Author: Pinal Dave & Rick Morelan
Publisher: Joes 2 Pros International LLC
Pages: 112
ISBN: 978-0985226862
Audience: Performance tuning DBAs and developers
Rating: 3
Reviewer: Ian Stirk

This short book aims to give you an understanding of wait stats, what they are, and how they can be used to improve SQL performance. Does it succeed?

When you submit a query to SQL Server, it runs on the CPU, however, if it can’t run for any reason (e.g. waiting to get data from disk), SQL Server records why the query couldn’t run, internally as a wait state (commonly known as wait stats). Examining these wait stats can reveal why a given SQL Server instance is performing sub-optimally.

Using wait stats together with performance monitor (PerfMon) counters to investigate problems on SQL Server is a well known performance tuning technique. This book aims to provide a guide to the more common wait stats, the techniques involved in using them, and offers advice on how to reduce them to improve SQL performance.

I expected the book to start with an overview of what Dynamic Management Views (DMVs) are, so as to put the wait stats DMV into context, but it doesn’t, instead it starts with an analogy about inventions. Most chapters have a similar format, firstly explaining some common related wait stats, their causes, and methods used to reduce them. Each chapter ends with a review of the chapter’s content and a short quiz - both of these are short and enjoyable. The wait stats and the methods used to reduce them are explained adequately.

The wait stats examined include: 

• CXPACKET

• SOS_SCHEDULER_YIELD

• PAGEIOLATCH_xx

• IO_COMPLETION

• ASYNCH_IO_COMPLETION

• BACKUPIO

• BACKUPBUFFER

• LCK_M_xx

• WRITELOG

• MSSQL_XP

• OLEDB

The book contains lots of analogies e.g. comparing a taxi rank to the SQL scheduler. While these may be useful when used sporadically, here I feel they are overused, so it takes too long to make a point – time is a valuable resource! By way of contrast, the book’s main author, Pinal Dave, blogs extensively, with very short and to-the-point articles. 

The book is purported to be for people with an intermediary-level of SQL Server knowledge, however, in places, it reads like a book for beginners. It is only 112 pages, but has a large font. If the font was standard size, the book would be reduced to around 45 pages. If the analogies and repetition were also removed, the book would be around 25 pages in length.

Some concepts/topics are introduced without proper context. For example on page 27 the DMV sys.dm_exec_requests is not explained in context of wait stats before being introduced. The book’s editor should have ensured there was a smooth transition between paragraphs/sections.

I get a sense of deference in several sections of the book (e.g. listen to me I’m a SQL expert). This might grate certain audiences, anyway surely the correct approach is to test things yourself where possible.

References to some PerfMon counters are given, however the use of PerfMon itself is not explained (and perhaps it should be, since this is an introductory book), also the correlation between the wait stats and PerfMon counters is not explained in sufficient detail.

To get the wait stats associated with the running of a stored procedure, the book recommends first clearing the wait stats using the DBCC SQLPERF command. However, since this would have problems on production systems, I’m surprised another technique (DMV snapshots) wasn’t used. In this technique, a snapshot of the relevant wait stats DMV counters is taken, the stored procedure is run, another snapshot of the same wait stats DMV counters is taken, and then finally the delta between the two snapshots is calculated. This delta contains the wait stats associated with the stored procedure.

Several questions I had before I’d read the book, how to get wait stats for a given session using Extended Events, does it introduce the DMV snapshot technique, were not answered by it. There are some very good references given at the end of the book, these are well worth following up.

In conclusion, as an introductory/mid-level book, it explains adequately, the more common wait stats, some of the techniques used to understand wait stats, together with suggestions on how to reduce them to improve SQL performance. However, I found the analogies laboured and the book too wordy in general. If the book was condensed to 25 pages I could perhaps recommend it.

You will get a more detailed and extensive understanding of wait stats from Tom Davidson’s seminal Waits and Queues paper which, although written for SQL Server 2005, it is still very applicable today.