LLI Wiki — My Digital Knowledge Base

Top wait types with running percentage

List the wait types that make up 95% of SQL Server waiting time, from sys.dm_os_wait_stats.

The query below lists the wait types that account for most of the waiting time recorded by SQL Server since the last restart (or since the wait statistics were cleared), with the percentage of each and a running total. Common background waits are excluded. The list stops at the wait type where the running total reaches 95%.

WITH Waits AS
(
    SELECT
        wait_type,
        wait_time_ms / 1000. AS wait_time_s,
        100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
    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',
         'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
)
SELECT
    W1.wait_type,
    CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
    CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
    CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
    INNER JOIN Waits AS W2
        ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95;

Notes

Mastodon LLIMastodon SocialFosstodon