SQL Server Customer Advisory Team - SQL Server Best Practices

Enabling SQL Server customers to navigate the most challenging frontiers of large scale data management.

Whitepapers

SQL Server 2005 Waits and Queues

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.

 

Comments

 

Joe Sack's SQL Server WebLog said:

Today I witnessed a performance load test against a 16 proc 64-bit SQL Server instance. During the first

March 17, 2009 9:06 PM
 

Jimmy May, Aspiring Geek: SQL Server Performance, Best Practices, Productivity, etc.
said:

Introduction: The Classic Protocol, Amended Introduction My customers are often surprised to learn about

April 26, 2009 3:37 PM
 

Jimmy May, Aspiring Geek: SQL Server Performance, Best Practices, Productivity, etc.
said:

Preamble For those of you who are already facile with SQL Server wait stats, there’s nothing new here.

April 27, 2009 11:10 AM
 

SQL Server Premier Field Engineer Blog said:

There are several dynamic management objects (DMOs) that I use on an ongoing basis in performance tuning

June 11, 2009 6:58 AM
sql, server, best practices, whitepapers, analysis services, data mining, olap, datawarehouse, datawarehousing, availability, clustering, capacity, collation, data types, data warehouse, database, design, index, mirroring, optimization, partitions, performance, precision, processing, querying, scalability, security, reporting services, integration services
Copyright 2008 Microsoft Corporation. All Rights Reserved.