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

 

In this chapter, we’ll cover

  • SQLOS
  • Wait analysis
  • Common performance problems
  • Correlating waits and queues

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.

17.1. SQLOS schedulers

17.2. Wait analysis

17.3. Common performance problems

17.4. Waits, queues, and DMV cross-reference

17.5. Best practice considerations: performance tuning