Tara Kizer Blog

Tara Kizer

PASS 2005: Session notes/comments Part 2

At the "General Session" today (Thursday), we saw a demo of the Upgrade Advisor tool which is used to prepare for the SQL Server 2005 upgrade.  It'll show you what you need to change prior to the upgrade and what you can change after the upgrade.  An example of something that you can change after the upgrade is the use of *= and =* for OUTER JOINs.  This syntax has been deprecated in the 90 compatibility mode.  Upgrade Advisor requires .NET Framework 2.0.

We also saw a demo of the reports available in Management Studio.  I really like the schema change history report that you get to by clicking on your SQL Server in the Object Explorer then selecting Report from the summary tab.  This will show you who has changed the schema and when it was done.  The report feature uses Reporting Services.

In Alex Cheng's "Using SQL Execution Plans for Performance Optimizations in SQL Server 2005" session, I was surprised to see that SELECT * does not use a bookmark lookup in SQL Server 2005.  I was also surprised to see in SQL Server 2005 that WHERE Column1 LIKE '%Value1%' uses an index seek instead of an index scan.  Alex stressed that the estimated cost in an execution plan should be less than 1 for most queries on an OLTP system.  I wish that Alex didn't spend so much time on the basics as he had a lot to cover and could have used that time to go into greater detail on the more advanced topics.

Ken Henderson covered three very useful tools that every DBA should consider using in his "SQL Server 2005 Diagnostic Collection and Analysis" session.  These tools are SQLDiag, Relog, and ReadTrace.  SQLDiag is a command line utility that is used to collect the data that you will need to diagnose a problem.  It captures SQL Profiler trace data, Performance Monitor counters, blocking information, server configuration, Event logs, SQL Server Error Logs, and much more.  It can be run as a console application or as a service.  Previous versions could only run as a console application.  You can customize what it collects via an ini or an XML (preferred) file.  If you are using it as a service, you can also use the registry.  It supports NTFS compression and has a built-in scheduling mechanism.  It can collect data from multiple servers simulatenously since it can use multiple threads.  It uses very little resources, except when it is collecting MS Info data (but you can turn this off).  Relog utility converts a Performance Monitor log into any other format that Performance Monitor supports, such as SQL Server tables.  If you convert to SQL, it will use an ODBC DSN for the connection.  ReadTrace utility analyzes trace files.  He had it analyze a 1GB trace file.  It didn't take very long at all.  It produces HTML reports with the analysis.  He covered much more, but that is all I could scribble down.

In Chris Miller's "Network Security and Database Administration", he demonstrated the netsh.exe command line utility.  It manipulates the Windows 2003 firewall settings.  You can schedule batch files that call this utility to change firewall settings such as to open a port for access for a short time window.  He also showed us how to use SQL Server 2000 with encryption.  It requires EXACTLY correct setup.  You can see KB276553 for how to set it up.


Legacy Comments

re: PASS 2005: Session notes/comments Part 2
this also uses an index scan in SS2000.
use northwind
select * from orders where shipaddress like '%rue%'

What am i missing here???

re: PASS 2005: Session notes/comments Part 2
Oops, another typo. It should read it uses an index seek in SQL Server 2005. In SQL Server 2000, yes it'll use an index scan. I'll fix the typo. Thanks Mladen.