RMJCS Logo
 

sys.dm_os_wait_stats

 
 

BOL: Returns information about the waits encountered by threads that are in execution. You can use this view to diagnose performance issues with SQL Server and also with specific queries and batches.

This view tracks the number and duration of waits since the SQL Server service started. There are in the region of 230 different wait types, BOL provides a brief description for each of them. Oddly, BOL states that the count is incremented at the start of a wait, but the duration of a wait can't possibly be known until the wait has completed, so going by BOL the count includes current waits but the times don't. The counters can be reset using DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR).

One particular wait type that I've seen too many of before is CXPACKET which occurs when the query processor brings parallel streams of execution back together. This can indicate that the query optimiser has been suggesting parallel plans in inappropriate places. Check server and query MAXDOP settings.

A few other wait types can legitimately have high counts. SQLTRACE_BUFFER_FLUSH is usually waiting for something to do and if you only have the default trace enabled then the overhead is very minor (see this microsoft.public.sqlserver.server post). CLR_AUTO_EVENT is fine - see the MSDN blog post 'High waits on CLR_MANUAL_EVENT and CLR_AUTO_EVENT'.

There's a very nice article on Simple-Talk 'SQL Server Wait Events: Taking the Guesswork out of Performance Profiling'.

 
     
 

Formatted query for sys.dm_os_wait_stats

 
 
SELECT
   ws.wait_type,
   ws.waiting_tasks_count,
   CASE WHEN ws.waiting_tasks_count = 0 THEN 0 ELSE ws.wait_time_ms / ws.waiting_tasks_count END as average_wait_time_ms,
   ws.wait_time_ms as total_wait_time_ms,
   CONVERT(DECIMAL(12,2), ws.wait_time_ms * 100.0 / SUM(ws.wait_time_ms) OVER()) as wait_time_proportion,
   ws.wait_time_ms - signal_wait_time_ms as total_wait_ex_signal_time_ms,
   ws.max_wait_time_ms,
   ws.signal_wait_time_ms as total_signal_wait_time_ms
FROM
   sys.dm_os_wait_stats ws
WHERE
   -- Restrict results to requests that have actually occured.
   ws.waiting_tasks_count > 0
ORDER BY
   ws.wait_type
 
     
Copyright 2007-2015 RMJCS Ltd