SQL Server Best Practices ArticlePublished: November 2006
Writers: Tom Davidson
Updated by: Danny Tambs
Technical Reviewers: Sanjay Mishra
This paper approaches the complex area of Microsoft® SQL Server™ performance tuning using a methodology called Waits and Queues. By using this methodology one can identify the best opportunities to improve performance, the so called “biggest bang for the buck”. These performance improvements are likely to have a significant return on the performance tuning time investment. The methodology helps identify the areas of slow performance by looking at the problem from two directions. You, or another troubleshooter, can use this to pinpoint problem areas by correlating data from two sources: Waits and Queues. An analysis of Waits indicates where SQL Server is spending lots of time waiting. In addition, the biggest waits point out the most important or relevant Queues (that is, Performance Monitor counters and other data) for this workload. The cross validation of the waits analysis enables us to eliminate all except the most significant performance counters, and provides a strong indication of pressure on specific resources.
In sum, Performance Tuning using the Waits and Queues methodology is an effective way to quickly identify and resolve application performance problems because it lets the user discover new and potentially unexpected problem areas, within applications or solutions without the typical guesswork that can accompany such work.
For more information, please refer to the SQL Server 2005 Waits and Queues whitepaper.
