DMV Queries - Get Top SQL Server Waits

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown/ChillyDBA
Date:        7 May 2012/2 Jul 2012

Description

Returns the SQL Server waits that comprise the top 95% of waits.  DBMS operational wait types are excluded (e.g. LAZYWRITER_SLEEP) as these are mostly in a wait state by design and would skew the results.

The scope of the results is server level.

Code

Function:


DROP FUNCTION dbo.udf_GetTopWaits
GO

CREATE FUNCTION dbo.udf_GetTopWaits ()
RETURNS @TopWaits TABLE
  
(
      
WaitType            VARCHAR(100) NULL,
      
WaitTime_s          INT NULL,
      
WaitPercent         DECIMAL(18,2) NULL,
      
RunningWaitPercent  DECIMAL(18,2) NULL
   )
AS



/***************************************************************
Purpose:   To retrieve details of the SQL Server waits, by type,
           that comprise the top 95%
           NOTE:  A list of system wait types is excluded as these
           are processes that spend most of their tiem in a waiting state
           and would therefore skew the results
          
           Scope of the results is for the server
          
           The following DMVs are used:
               sys.dm_os_wait_stats    - contains a record of all witing processes and the wait types
          
          
Author:        Unknown
History:   7 May 2012 - Initial Issue  
           2 Jul 2012 - ChillyDBA - Converted to a table valued function

****************************************************************/

BEGIN



   WITH
Waits
  
AS
      
(
          
SELECT  
              
wait_type                                           AS WaitType,
              
wait_time_ms / 1000.                              AS WaitTime_s ,
              
100. * wait_time_ms / SUM(wait_time_ms) OVER ( )   AS WaitPercent ,
              
ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC )   AS RowNumber
          
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'
                                  
)
        )
        
  
INSERT @TopWaits
      
(
          
WaitType,
          
WaitTime_s,
          
WaitPercent,
          
RunningWaitPercent
      
)
  
SELECT  
          
W1.WaitType                                 AS WaitType,
          
CAST(W1.WaitTime_s AS DECIMAL(12, 2))      AS WaitTime_s,
          
CAST(W1.WaitPercent AS DECIMAL(12, 2))     AS WaitPercent ,
          
CAST(SUM(W2.WaitPercent) AS DECIMAL(12, 2)) AS RunningWaitPercent
  
FROM    Waits AS W1
  
INNER JOIN Waits AS W2
      
ON W2.RowNumber <= W1.RowNumber
  
GROUP BY
          
W1.RowNumber ,
          
W1.WaitType ,
          
W1.WaitTime_s ,
          
W1.WaitPercent
  
HAVING  SUM(W2.WaitPercent) - W1.WaitPercent < 95 ; -- percentage threshold
    
  
RETURN
END



Test Code:

 

SELECT * FROM dbo.udf_GetTopWaits()

ċ
udf_GetTopWaits.sql
(2k)
Andy Hughes,
Jul 2, 2012, 9:11 AM
Comments