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).