Microsoft® SQL Server® 2008 Bible

Chapter 57. Wait States

IN THIS CHAPTER

• Querying for wait states

• Detecting CPU pressure

• Analyzing hardware performance

I wonder how much of the gross national product is wasted annually waiting at traffic lights. I've thought about using the chronometer feature on my Timex Ironman watch to time my monthly aggregate red light wait time. (It would be a good reason to actually use my watch for more than telling time, as there's little chance I'm going to do a triathlon anytime soon.) There's no doubt that our fast-food culture obsesses about avoiding waiting at any cost.

SQL Server has to wait too. Sometimes it's waiting for I/O, sometimes for the CLR, sometimes for CPU. These waits are system bottlenecks. Fortunately, you don't need a stopwatch to measure how SQL Server waits. The data is all there in a dynamic management view (DMV), just waiting for you.

Most of my optimization strategies involve reducing the aggregate workload of the database by improving the schema, queries, and indexes.

Wait states are about tuning the environment—the hardware and server operating system. By analyzing what SQL Server is waiting for, you can identify the bottlenecks in the system.

The SQL Server Operating System (SQLOS) uses one scheduler per logical CPU core. Each scheduler manages a set of sessions that rotate through three states. A session that's running eventually has to wait for something, so it becomes suspended while waiting. When the wait is over, the session is runnable and waiting for the CPU ...