SQL SERVER – Wait Types

When I was working with couple of frequent blocking issue in our production servers, I have noticed different types of wait resources which are very common and can be very useful but people often overlooked these while troubleshooting. I also feel that "wait stats" is one of the most under-utilized performance troubleshooting methodologies in the SQL Server.

Any time a user connection is waiting, SQL Server keep track of wait time. For example, the application requests resources such as I/O, locks, or memory and can wait for the resource to be available.

There are few system wait types which are always present and there are a few wait types which can really cause a heavy bottleneck for your system.

Types of Waits

As per BOL, there are three types of wait types, namely:

Resource waits

Resource waits occur when a worker requests access to a resource that is not available because the resource is being used by some other worker or is not yet available. Examples of resource waits are locks, latches, network and disk I/O waits. Lock and latch waits are waits on synchronization objects

Queue waits

Queue waits occur when a worker is idle, waiting for work to be assigned. Queue waits are most typically seen with system background tasks such as the deadlock monitor and deleted record cleanup tasks. These tasks will wait for work requests to be placed into a work queue. Queue waits may also periodically become active even if no new packets have been put on the queue.

External waits

External waits occur when a SQL Server worker is waiting for an external event, such as an extended stored procedure call or a linked server query, to finish. When you diagnose blocking issues, remember that external waits do not always imply that the worker is idle, because the worker may actively be running some external code.

To check the wait types for any server, just run the following simple T-SQL:

SELECT * FROM sys.dm_os_wait_stats

Along with above thoughts , it woold be good to extract useful information from below T-SQL. This will show the waits grouped together as a percentage of all waits on the system, in decreasing order. The waits to be concerned about (potentially) are those at the top of the list as this represents the majority of where SQL Server is spending it’s time waiting.

This code based on a query written by MVP Glenn Berry and modified by Paul S. Randal so credit goes to them.          

                                                                                                            ----------------------------------------------------------------

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',  N'SP_SERVER_DIAGNOSTICS_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],

    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

GO

Output: Will be something like as mentioned below:

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)