Chapter 17. Waits and queues:a performance-tuning methodology

Performance tuning, particularly performance troubleshooting, can be tough work. With entire books (and careers) dedicated to this topic, what can we possibly hope to achieve in a single chapter? Rather than attempt to provide a root cause analysis for a wide variety of potential performance problems, the goal of this chapter is to introduce you to a performance-tuning methodology called waits and queues.

Tom Davidson's excellent whitepaper SQL Server 2005 Waits and Queues is a must read for anyone interested in SQL Server performance tuning. Equally applicable to SQL Server 2008, it argues the case for a targeted approach to performance tuning by determining the resources that processes are spending the most time waiting on. Knowing this enables the analysis of a smaller subset of queues, therefore eliminating inconsequential data from the analysis-and-troubleshooting process.

In this chapter, we'll begin with an overview of the SQL Operating System (SQLOS) with a particular focus on its scheduling mechanism, part of which is the use of a waiter list for processes waiting on resources to become available. We'll then focus on the analysis of this waiter list as part of a targeted performance-tuning process using the waits and queues methodology. We'll spend the rest of the chapter on common performance problems and how waits and queues metrics can assist in the diagnosis of such problems.