SQL Server Wait Type Repository

Microsoft’s Bob Ward has a good post up on the CSS SQL Server Engineers blog about an upcoming web page that will completely document all of the wait types from the sys.dm_os_wait_stats DMV. There will be more detailed and practical information about the individual wait types compared to what is documented in Books Online. He is soliciting questions and suggestions on his blog for this project.

This is great news. I have long been a proponent of looking at wait types to get a better idea of the primary performance bottlenecks for a SQL Server instance. Below is a query that I use very frequently to monitor my top wait types.

-- Clear Wait Stats (as needed) 
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- 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('SLEEP_TASK', 'BROKER_TASK_STOP', 
  'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
  'LAZYWRITER_SLEEP')) -- filter out some irrelevant waits
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 < 95; -- percentage threshold for waits

It is pretty easy to figure out the more common wait types, but it will be great to have better information on the less common ones, along with information on corrective action (where it is needed).

Technorati Tags:

This entry was posted in SQL Server 2008. Bookmark the permalink.

One Response to SQL Server Wait Type Repository

  1. john langston says:

    I start Profiler and open Activity Monitor. I see EXEC #am_generate_waitstats being executed which I presume is for populating the Resource Waits section. When sorting by Cumulative Wait Time, I see a completely different highest wait type than when I run the above query in SSMS. Can you point to a resource to help me rectify what I see?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s