What Are You Waiting For?

I know what I am waiting for. I know exactly what every SQL Server instance in my care is waiting for. Why? Because it’s my job to make SQL Server go faster.

Identifying what your SQL Server Instance is waiting on will show you exactly where to look in order to make your server go faster.

That’s quite a statement right there, not to mention a very cool feature. You see every time a SQL Server execution request has to wait for something, be it a resource or another process, the fact that a wait has occurred is recorded by SQL Server. The specific type of wait that occurred is stored, along with the amount of time that the thread spent waiting. This information is aggregated by the Wait Type and is easily accessible to me and you in order to study via those super handy SQL Server Dynamic Management Views (DMVs).

If you know what your SQL Server is waiting on then you know exactly where you need to spend your time, your most precious resource, in order to make your server go faster. If that sounds like a pretty good deal, a no brainer or a fast track to a quick win, well that’s because it is!

Sure, you could go ahead and write your own query to review the SQL Server Wait Types for your server but then why reinvent the wheel right. Fantastic T-SQL resources and scripts are already available out there courtesy of our fellow SQL community members, such as the script below. This query has been written by Glenn Berry (Blog|Twitter) and is available as part of his excellent resource pack of SQL Server 2008 Diagnostic Queries so be sure to get yourself a copy.

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',
'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER'))
SELECT W1.wait_type, 
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold

Where Can I Find Out More About SQL Server Wait Types?

I’m glad you asked. Once you have identified what your SQL Server is waiting on, you then need to understand more about your specific Wait Types in order to decide how best to proceed. There are some brilliant resources available to you to help you on your way. Here, in no particular order, are just a few of them to get you started:

• SQL Server Best Practice Article: Performance Tuning Waits Queues
• White Paper: Troubleshooting Performance Problems in SQL Server 2008
• Looking at Wait Types is just one piece to the performance tuning puzzle. An excellent methodical and top down performance tuning method is described in the book Inside Microsoft SQL Server 2008: T-SQL Querying.
• Pinal Dave (Blog|Twitter) recently shared a fantastic blog post series looking at a a different SQL Server Wait Type each day.
• Visit the community knowledge resource SQL Server Pedia – SQL Server Wait Types.

Reviewing SQL Server Wait Types is one of the best ways to begin proactively performance tuning your environment. Armed with the information provided from your high level findings, you can then look to dive deeper into specific areas, focussing your efforts precisely where they can provide the most benefit. What are you waiting for?