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.