SQLSaturday#59 – Waits & Queues Presentation

At SQLSaturday#59 in NYC, my session was about Waits & Queues. I presented a topic I thought was sure to be known by the audience, and that the material would just be an introduction and reinforcement of some of the concepts of this performance methodology, that has been around for 5-6 years since the release of SQL Server 2005.

When I asked the audience first about some of the traditional performance and troubleshooting tools that they’ve used, the hands were up, and the attendees mentioning as expected, Performance Monitor, SQL Profiler, Trace, etc.  Then I asked how many folks have heard and used the Waits & Queues method?  To my amazement and surprise, you could hear the crickets, not a hand went up, except one gentleman who actually was a contributing participant throughout.   My follow-up question should have been, “OK, so how many people are going to use this once the session is over?”  I’m sure that many will indeed start exploring the Waits & Queues performance tuning methodology (as many folks lined up at the podium after the presentation to ask some questions and copy down some reference materials and links.)

Even though no one really knew of Waits & Queues, because perhaps the terminology was new, almost everyone had used the “Queue” side of the equation (ie: Perfmon Counters)

Waits are described as anytime SQL Server requests a resource for a user query or session to be completed, and when it is not immediately available, the session goes into a wait state or suspended queue until the resource becomes available.  SQL Server now tracks these waits, the time it spends waiting for the resource, and how long it’s actually waiting, and represents this by SQL Server wait statistics.

These wait statistics are exposed in the two key SQL Server DMV’s sys.dm_os_wait_stats and sys.dm_os_waiting_tasks.  The wait_stats DMV is historical aggregated data across all session ids since SQL Server last started (or cleared), and shows the time for waits that have completed. 

The waiting_tasks dmv, shows the current waits at the moment in time it is queried, and shows which resources it is waiting on (the suspended queue) for all active sessions.  It is what is happening on the server right now.

When an end-user complains of slowness or performance issues on a server, by looking directly at the wait statistics, one will instantly know what the server bottleneck is, and therefore easier to troubleshoot, and know which other methods to use in helping to resolve the issue.

Queues are, as mentioned earlier, represented by performance counters and objects.  So what should you do first, when a performance issue arises?  Setup 20 or so performance counters, profiler and trace, literally casting a wide net to find out, over some time, the bottleneck lies?   Or, go directly to the source of the slowness, and see exactly what your server queries are waiting on, what the resource is that is not available, and pinpoint the exact bottleneck?  I think put in this perspective, the answer will be the latter.

This is the “waits” in waits & queues, also, more specifically, know as wait-time analysis.

With sys.dm_os_wait_stats, it is easy to query, and reporting is not very complicated.   While there are many ways to sort the data, and also exclude some of the system and background waits, a simple query like this will expose the aggregated waits on the server, ordered by the longest wait time in milliseconds:

            SELECT *

FROM  sys.dm_os_wait_stats

ORDER BY wait_time_ms DESC

A really good and informative query to have in your toolset, in addition to the traditional sp_who2, is to query the sys.dm_os_waiting_tasks  and cross apply it with some other DMV’s to get the details on any blocking, resources involved, resources used, and the actual SQL text associated with a block or waiting spid:

SELECT w.session_id,

       w.wait_duration_ms,

       w.wait_type,

       w.blocking_session_id,

       w.resource_description,

       s.program_name,

       t.text,

       t.dbid,

       s.cpu_time,

       s.memory_usage

FROM sys.dm_os_waiting_tasks w

INNER JOIN sys.dm_exec_sessions s ON

            w.session_id=s.session_id

INNER JOIN sys.dm_exec_requests r ON

            s.session_id=r.session_id

OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t

WHERE s.is_user_process=1

So, my new mission as SQL Server Evangelist will be to spread the word on Waits & Queues wherever and whenever I get a chance.  I was glad I could talk about the topic in front of a large audience and give them tips and information on what Microsoft calls the “biggest payoff for your tuning efforts”   And, although it’s been around now for a while, clearly more needs to be done to spread the word!

Some time back, way before SQLSaturday#59, I had a client with some performance issues, and I published a blog on the same topic that can be accessed here:

http://www.sqlservercentral.com/blogs/pearlknows/archive/2010/01/15/sql-server-waits-and-queues.aspx

There’s much, much more on this topic, and I will continue to bring you articles, blogs and featured presentations.  Next, I will bring you details about Resource and Signal Waits....