Part II: Wait Types

The previous section was largely concerned with: explaining what Wait Stats are, how they can be identified in currently executing SQL, how to record baseline information, and how problems can be identified by comparing the currently executing SQL with baseline information.

This next section focuses on the various wait types associated with given processing areas (e.g. IO). Since there are over 700 wait types in SQL Server 2014, only the most common ones are discussed. In each case, the meaning of the wait type is discussed, and then methods of lowering it are given.

Chapter 4: CPU-Related Wait Types

This chapter opens with a short discussion on the history of processors, in essence more CPUs/cores mean more schedulers that can process more queries.

The chapter then discusses perhaps the most common wait, CXPACKET. This wait type indicates a query is executing in parallel. The optimizer splits the work to be done between different threads, the threads can finish at different times, those that finish early issue a CXPACKET wait type.

The amount of parallelism is influenced by the configuration settings “max degree of parallelism” and “cost threshold for parallelism”, and these are discussed. Stale statistics can reflect skewed data, which can lead to excessive CXPACKETs – updating the statistics can correct this. In summary, this wait type is often innocuous.

Next, the wait types SOS_SCHEDULER_YIELD and THREADPOOL are similarly discussed.

This chapter provides a good discussion of: what the CPU-related wait types mean, how they can be lowered, and if they are important. Example code is provided to generate the wait types (and suitable advice about NOT doing this on an important box is given).

Some useful tools are discussed (e.g. OStress), and you’ll certainly learn more about the internal processing within SQL Server from the examples.

Other Chapters (5 to 11)

In many ways, looking up the wait type information is mostly a reference exercise, so rather than give details of the content of each of the remaining chapters, below I list the processing areas discussed. All the chapters follow the same useful format as Chapter 4 discussed above. The remaining chapters discuss: 

• IO-Related Wait Types

• Backup-Related Wait Types

• Lock-Related Wait Types

• Latch-Related Wait Types

• High-Availability and Disaster-Recovery Wait Types

• Preemptive Wait Types

• Background and Miscellaneous Wait Types

• In-Memory OLTP–Related Wait Types

Conclusion

This book contains well-written discussions, useful examples, SQL code, diagrams, website links, and inter-chapter links. It contains plenty of detailed information to help you troubleshoot your problems, and offers useful suggestions for solutions.

The book describes what Wait Stats are, how they can be recorded as a baseline, and how this baseline can be used to identify problems. The larger part of the book can be used as a reference, for you to decode your wait types, understand what they are, and how the wait type metrics can be lowered. You’re sure to learn a lot about the internal working of SQL Server from this book.

Sometimes, things are not how they initially seem e.g. high IO values might not mean the IO subsystem is slow, it might be there isn’t enough memory on the server. So, I would have liked to have seen a more corroborative approach between various tuning techniques (e.g. Davidson links Wait Stats with Perfmon queue metrics). Also, I felt sometimes there was too much hyperbole (e.g. Wait Stats is a relatively little used technique, and worthless without a baseline).

Despite these reservations, I enjoyed this book immensely, it contains lots of useful detail. It is certainly the next thing to read after Davidson’s paper on SQL Server 2005 Waits and Queues (available for free at: https://technet.microsoft.com/en-us/library/cc966413.aspx). The book is highly recommended. 

For more recommended SQL Server books see the article on Programmer's Bookshelf   Pick Of The Shelf - SQL Server

To keep up with our coverage of books for programmers, follow @bookwatchiprog on Twitter or subscribe to I Programmer's Books RSS feed for each day's new addition to Book Watch and for new reviews.