SQL Server: Find Highest Wait on Server

This content 7 years old. Please, read this page keeping its age in mind along with the fact technology changes fast and the information on this page me be outdated, not best practice, or plain wrong.

From Paul Randal’s survey of highest wait on server TSQL.

 

WITH Waits AS
     (SELECT
         wait_type,
         wait_time_ms / 1000.0 AS WaitS,
         (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
         signal_wait_time_ms / 1000.0 AS SignalS,
         waiting_tasks_count AS WaitCount,
         100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
         ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
     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', 'BROKER_EVENTHANDLER',
         'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
      )
 SELECT
      W1.wait_type AS WaitType,
      CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
      CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
      CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
      W1.WaitCount AS WaitCount,
      CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
 FROM Waits AS W1
 INNER JOIN Waits AS W2
      ON W2.RowNum <= W1.RowNum
 GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
 HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
 GO

Source

Speak Your Mind

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.