Top 5 Waiting Tasks by Percent

Use below script to determine the Top 5 waiting tasks and sorted by percent of use in the top 5

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 (

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’CLR_SEMAPHORE’,Â Â Â N’LAZYWRITER_SLEEP’,

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’RESOURCE_QUEUE’,Â Â N’SQLTRACE_BUFFER_FLUSH’,

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’SLEEP_TASK’,Â Â Â Â Â Â N’SLEEP_SYSTEMTASK’,

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’WAITFOR’,Â Â Â Â Â Â Â Â Â N’HADR_FILESTREAM_IOMGR_IOCOMPLETION’,

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’CHECKPOINT_QUEUE’, N’REQUEST_FOR_DEADLOCK_SEARCH’,

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’XE_TIMER_EVENT’,Â Â N’XE_DISPATCHER_JOIN’,

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’LOGMGR_QUEUE’,Â Â Â Â N’FT_IFTS_SCHEDULER_IDLE_WAIT’,

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’BROKER_TASK_STOP’, N’CLR_MANUAL_EVENT’,

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’CLR_AUTO_EVENT’,Â Â N’DISPATCHER_QUEUE_SEMAPHORE’,

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’TRACEWRITE’,Â Â Â Â Â Â N’XE_DISPATCHER_WAIT’,

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’BROKER_TO_FLUSH’,Â N’BROKER_EVENTHANDLER’,

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’FT_IFTSHC_MUTEX’,Â N’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’,

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â N’DIRTY_PAGE_POLL’)

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â )

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],

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]

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

Article ID: 153
Created: September 4, 2014
Last Updated: January 10, 2015
Author: Natural Networks NOC [support@naturalnetworks.com]

Online URL: https://kb.naturalnetworks.com/article.php?id=153