Uncategorized

Wait Statistics Overview

9. 3. 2018
WITH [WaitStats] AS
(
	SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 [WaitTimeSec],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 [ResourcesWaitTimeSec],
        [signal_wait_time_ms] / 1000.0 [SignalWaitTimeSec],
        [waiting_tasks_count] [WaitingTasksCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() [WaitPercentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) [Rows],
		CASE
			WHEN [wait_type] 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', N'SP_SERVER_DIAGNOSTICS_SLEEP' ) THEN 1
			ELSE 0
		END IsSystemWaitType
    FROM [sys].[dm_os_wait_stats]
    )
SELECT
    [ws1].[wait_type] AS [WaitType],
    CAST([ws1].[WaitTimeSec] AS DECIMAL(12, 2)) [WaitTimeSec],
    CAST([ws1].[ResourcesWaitTimeSec] AS DECIMAL(12, 2)) [ResourcesWaitTimeSec],
    CAST([ws1].[SignalWaitTimeSec] AS DECIMAL(12, 2)) [SignalWaitTimeSec],
    [ws1].[WaitingTasksCount],
    CAST([ws1].[WaitPercentage] AS DECIMAL(12, 2)) [WaitPercentage],
    CASE
		WHEN [ws1].[WaitingTasksCount] > 0 THEN CAST(([ws1].[WaitTimeSec] / [ws1].[WaitingTasksCount]) AS DECIMAL(12, 2))
		ELSE 0
	END [AvgWaitTimeSec],
    CASE
		WHEN [ws1].[WaitingTasksCount] > 0 THEN CAST(([ws1].[ResourcesWaitTimeSec] / [ws1].[WaitingTasksCount]) AS DECIMAL(12, 2))
		ELSE 0
	END [AvgResourcesWaitTimeSec],
    CASE
		WHEN [ws1].[WaitingTasksCount] > 0 THEN CAST(([ws1].[SignalWaitTimeSec] / [ws1].[WaitingTasksCount]) AS DECIMAL(12, 2))
		ELSE 0
	END [AvgSignalWaitTimeSec]
FROM [WaitStats] AS [ws1]
WHERE [ws1].[IsSystemWaitType] = 0
GROUP BY [ws1].[IsSystemWaitType], [ws1].[Rows], [ws1].[wait_type], [ws1].[WaitTimeSec], [ws1].[ResourcesWaitTimeSec], [ws1].[SignalWaitTimeSec], [ws1].[WaitingTasksCount], [ws1].[WaitPercentage]
HAVING [ws1].[WaitTimeSec] > 0
ORDER BY [ws1].[WaitTimeSec] DESC
GO

 

93
Share: