Three SQL Server Tuning Tips

There is a lot of SQL Server tuning advice floating around the web these days.  Of all of the different resources available, one of my favorites is the webcast.  Webcasts pack in more information into a one hour session than you can get by reading a 100-page volume.  I greatly approve of Microsoft’s new practice of making their internal webcasts and training sessions available as streaming media. 

One example of a very good training webcast is the Support Webcast: Microsoft SQL Server 2000 – Reading Execution Plans in SQL Server 2000 at http://support.microsoft.com/default.aspx?scid=kb;en-us;815337. This is a good place to start your training in performance tuning because it’s essential that you have a good understanding of how to read a SQL Server execution plan and to learn to look for less than optimal behaviors.

Microsoft has also increased the number and variety of technical white papers for SQL Server.  One such tuning white paper, written by SQL Server dev team luminary Lubor Kollar, is called Statistics Used by the Query Optimizer in Microsoft SQL Server 2000 and can be found at http://msdn.microsoft.com/library/default.asp?url=/library/techart/statquery.htm.  This is paper covers another tuning foundation concept – how statistics affect query processing and how to create, update, and delete statistics in a SQL Server database.

Finally, you can find another intriguing bit of tuning arcane by looking up Knowledge Base (KB) article #826433.  This article, entitled PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O Problems, gives you a lot of insight into the interplay between hardware and SQL Server.  Obviously, hardware can have a huge impact on how well SQL Server performs (duh!).  But this article also points out a nifty little tool called SQLIOStress that can help you test your hardware.  If you want to skip the information in KB #826433, you can go directly to http://support.microsoft.com/kb/231619 for more information about using SQLIOStress.