Get Top SQL Server Waits

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