Chapter 34. What are you waiting for? An introduction to waits and queues
The purpose of this chapter is to help fellow DBAs, developers, and other database professionals by spreading the word about waits and queues. This performance-tuning methodology became significant with the release of SQL Server 2005, with all its dynamic management views and functions, collectively known as dynamic management objects (DMOs). With waits and queues, you can quickly identify the cause of a performance bottleneck on your SQL Server systems. Although this methodology was introduced without much fanfare, there wasn’t a substantial amount of early adoption by those who would benefit from it the most: database administrators.
This topic is detailed and broad, so I’ll focus only on the fundamental concepts as they apply to online transaction processing (OLTP) systems. My goal is to help you understand the topic so you can pursue your own independent investigation into more advanced aspects.
In SQL Server 2000 and earlier, you had to use multiple methods to cast a wide net and set up various counters and tools to capture the underlying cause of a performance bottleneck. Traditional tools such as profiler traces, Performance Monitor (PerfMon), network sniffers, and other third-party software were also used to assist in this effort. Using these tools, you’d eventually get to the root cause of the issue, but the process was time consuming.