SQL Server Wait Statistics: The Complete Guide

The time a query takes divides cleanly into two parts: time spent doing work, and time spent waiting to be allowed to do work. On a server that feels slow, the second part is almost always the larger one — and unlike the first, SQL Server records it in detail and hands it to you for free.

That is what makes wait-based analysis the sensible place to start. It does not ask you to guess which counter matters, or to reason backwards from CPU utilisation to a cause. It asks the engine a direct question — what were you waiting for, how long, and how often — and the answer narrows a whole server down to one or two things worth investigating, usually within a couple of minutes.

What it does not do is hand you a fix. A wait type is a direction. Everything after the first query on this page is about turning that direction into something specific enough to change.

Every task inside SQL Server is, at any instant, in exactly one of three states. Understanding the cycle between them is what makes the columns in the DMV mean something.

The cycle runs suspended → runnable → running, and a task goes round it constantly. Because a single wait usually passes through both waiting states, the recorded wait_time_ms contains both parts, and signal_wait_time_ms is the second one broken out. Subtract to get the resource half. That subtraction is the single most useful thing you can do with the raw numbers: it separates the resource is slow from the CPU is busy before you have looked at a single wait type.

Five DMVs matter, and they answer genuinely different questions. Most bad wait analysis comes from asking the first one a question only the last three can answer.

This ranks wait types by share of total wait time, filters out the background waits that would otherwise dominate, and — importantly — returns the average wait alongside the total, plus a running percentage so you can see where the list stops mattering.

WITH waits AS (
    SELECT
        wait_type,
        waiting_tasks_count                              AS wait_count,
        wait_time_ms / 1000.0                            AS wait_s,
        (wait_time_ms - signal_wait_time_ms) / 1000.0    AS resource_s,
        signal_wait_time_ms / 1000.0                     AS signal_s,
        max_wait_time_ms,
        100.0 * wait_time_ms
            / NULLIF(SUM(wait_time_ms) OVER (), 0)       AS pct
    FROM sys.dm_os_wait_stats
    WHERE waiting_tasks_count > 0
      AND wait_type NOT IN (
            -- Background and timer waits. These are the server doing nothing,
            -- on purpose, and on an instance with real uptime they will
            -- otherwise occupy the entire top of this list.
            N'BROKER_EVENTHANDLER',        N'BROKER_RECEIVE_WAITFOR',
            N'BROKER_TASK_STOP',           N'BROKER_TO_FLUSH',
            N'BROKER_TRANSMITTER',         N'CHECKPOINT_QUEUE',
            N'CHKPT',                      N'CLR_AUTO_EVENT',
            N'CLR_MANUAL_EVENT',           N'CLR_SEMAPHORE',
            N'DBMIRROR_DBM_EVENT',         N'DBMIRROR_EVENTS_QUEUE',
            N'DBMIRROR_WORKER_QUEUE',      N'DBMIRRORING_CMD',
            N'DIRTY_PAGE_POLL',            N'DISPATCHER_QUEUE_SEMAPHORE',
            N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
            N'HADR_CLUSAPI_CALL',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            N'HADR_LOGCAPTURE_WAIT',       N'HADR_NOTIFICATION_DEQUEUE',
            N'HADR_TIMER_TASK',            N'HADR_WORK_QUEUE',
            N'LAZYWRITER_SLEEP',           N'LOGMGR_QUEUE',
            N'ONDEMAND_TASK_QUEUE',        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
            N'QDS_ASYNC_QUEUE',            N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
            N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_SHUTDOWN_QUEUE',
            N'REQUEST_FOR_DEADLOCK_SEARCH', N'SLEEP_BPOOL_FLUSH',
            N'SLEEP_DBSTARTUP',            N'SLEEP_DCOMSTARTUP',
            N'SLEEP_SYSTEMTASK',           N'SLEEP_TASK',
            N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
            N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
            N'WAIT_XTP_HOST_WAIT',         N'WAITFOR',
            N'XE_DISPATCHER_WAIT',         N'XE_TIMER_EVENT',
            -- Real work, but not a problem indicator on its own.
            N'CXCONSUMER'
        )
)
SELECT
    wait_type,
    CAST(wait_s      AS decimal(16, 2))                  AS wait_s,
    CAST(resource_s  AS decimal(16, 2))                  AS resource_s,
    CAST(signal_s    AS decimal(16, 2))                  AS signal_s,
    wait_count,
    CAST(wait_s * 1000.0 / wait_count AS decimal(16, 2)) AS avg_wait_ms,
    max_wait_time_ms,
    CAST(pct AS decimal(5, 2))                           AS pct,
    CAST(SUM(pct) OVER (ORDER BY pct DESC
                        ROWS UNBOUNDED PRECEDING)
         AS decimal(5, 2))                               AS running_pct
FROM waits
ORDER BY pct DESC;

In practice the first four or five rows account for nearly all the time. When running_pct passes about ninety-five, stop reading — what is below that line is noise dressed up as data.

Run this second, to get the instance-wide split between scheduling delay and real resource waiting:

SELECT
    CAST(100.0 * SUM(signal_wait_time_ms)
         / NULLIF(SUM(wait_time_ms), 0) AS decimal(5, 2)) AS signal_pct,
    CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms)
         / NULLIF(SUM(wait_time_ms), 0) AS decimal(5, 2)) AS resource_pct
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
  AND wait_type NOT IN (N'SLEEP_TASK', N'LAZYWRITER_SLEEP', N'WAITFOR',
                        N'XE_TIMER_EVENT', N'REQUEST_FOR_DEADLOCK_SEARCH',
                        N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP',
                        N'HADR_WORK_QUEUE', N'QDS_ASYNC_QUEUE');
-- Use the same ignore list as the query above. A signal ratio computed over
-- sleeping background tasks describes the background tasks, not your workload.

A signal share above roughly a fifth to a quarter is worth treating as CPU pressure in its own right. When it is that high, the specific wait types below it are largely downstream of the scheduling problem, and chasing them individually will not get you anywhere.

Here is a shortened result from a test instance. The ranking and the averages disagree, which is the whole point of the example.

CXPACKET is top of the list and is the least interesting row on it: four thousand seconds accumulated in fractions of a millisecond at a time, which is what parallelism looks like when it is working. PAGEIOLATCH_SH is second by total but first by consequence — nearly 22 ms per read is storage that cannot keep up, or a workload asking it for far too much.

LCK_M_X is fourth at ten percent, and it is the row that would make users complain: an average of 486 ms means individual statements are stalling for half a second behind someone else's transaction. A ranking by total alone would have you working on the parallelism first and the blocking last, in exactly the wrong order.

Everything above still describes the whole life of the instance. To isolate a window — an incident, a batch run, the fifteen minutes before and after a change — capture the counters twice and subtract.

-- Step 1. Capture the starting point.
DROP TABLE IF EXISTS #wait_before;

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #wait_before
FROM sys.dm_os_wait_stats;

Then let the window pass. Do not use WAITFOR DELAY in the same session to wait it out: that blocks the session you need, and the session's own wait shows up in the results. Just come back to the same query window when the time is up.

-- Step 2. Let the window you care about pass, then subtract.
-- Run this in the SAME session, so that #wait_before is still there.
SELECT TOP (20)
    a.wait_type,
    a.waiting_tasks_count - ISNULL(b.waiting_tasks_count, 0)  AS wait_count,
    CAST((a.wait_time_ms - ISNULL(b.wait_time_ms, 0)) / 1000.0
         AS decimal(16, 2))                                   AS wait_s,
    CAST(((a.wait_time_ms - ISNULL(b.wait_time_ms, 0))
        - (a.signal_wait_time_ms - ISNULL(b.signal_wait_time_ms, 0))) / 1000.0
         AS decimal(16, 2))                                   AS resource_s,
    CAST(1.0 * (a.wait_time_ms - ISNULL(b.wait_time_ms, 0))
         / NULLIF(a.waiting_tasks_count - ISNULL(b.waiting_tasks_count, 0), 0)
         AS decimal(16, 2))                                   AS avg_wait_ms
FROM sys.dm_os_wait_stats AS a
LEFT JOIN #wait_before    AS b ON b.wait_type = a.wait_type
WHERE a.wait_time_ms - ISNULL(b.wait_time_ms, 0) > 0
ORDER BY wait_s DESC;

-- If any row comes back negative, the instance restarted or someone cleared the
-- counters between the two captures. Throw the result away and start again.

For anything longer-lived than a single investigation, write the same capture into a permanent table on a schedule instead of a temp table. Two weeks of fifteen-minute snapshots is small, cheap, and turns every future question about “is this normal?” into a query rather than an argument.

Cumulative counters cannot tell you who. For that you need the live view, which returns the waiting sessions, what they are waiting on, and — for lock waits — the session blocking them.

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time                       AS wait_ms,
    r.last_wait_type,
    r.wait_resource,
    DB_NAME(r.database_id)            AS database_name,
    r.status,
    r.command,
    s.login_name,
    s.host_name,
    s.program_name,
    SUBSTRING(t.text,
        (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset
              WHEN -1 THEN DATALENGTH(t.text)
              ELSE r.statement_end_offset
          END - r.statement_start_offset) / 2) + 1)  AS running_statement
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
     ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
  AND s.is_user_process = 1
ORDER BY r.wait_time DESC;

Two columns repay attention. blocking_session_id turns an abstract LCK_M_X total into a specific session you can go and look at, and where several rows point at the same id you have found the head of a blocking chain. wait_resource names the thing being waited on — for tempdb allocation contention it reads as 2:1:1 or 2:1:3, which identifies the problem outright.

A server-level wait tells you the instance spent time on something. It does not tell you which query did it, and that is the step where wait analysis either becomes actionable or stalls. Query Store closes the gap and, unlike the live DMVs, its history survives a restart.

SELECT TOP (20)
    ws.wait_category_desc,
    q.query_id,
    p.plan_id,
    CAST(SUM(ws.total_query_wait_time_ms) / 1000.0
         AS decimal(16, 2))              AS wait_s,
    MIN(qt.query_sql_text)               AS query_text
FROM sys.query_store_wait_stats AS ws
JOIN sys.query_store_plan       AS p  ON p.plan_id      = ws.plan_id
JOIN sys.query_store_query      AS q  ON q.query_id     = p.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_runtime_stats_interval AS i
     ON i.runtime_stats_interval_id = ws.runtime_stats_interval_id
WHERE i.start_time >= DATEADD(hour, -24, SYSUTCDATETIME())
GROUP BY ws.wait_category_desc, q.query_id, p.plan_id
ORDER BY wait_s DESC;

-- Requires Query Store to be ON for the database, and SQL Server 2017 or later.
-- Waits arrive pre-grouped into categories such as Lock, Buffer IO and CPU, so
-- you get the query but not the exact wait type. That is usually the trade you
-- want: the query is the part you can change.

For a session you are watching right now, sys.dm_exec_session_wait_stats gives the same shape as the instance DMV scoped to one session_id. It is the cleanest way to profile a single batch: read it before the batch starts, read it again when it finishes, and subtract. The rows vanish when the session disconnects, so capture before you close the window.

Wait statistics have been stable for a long time, but several changes affect how you read them and which fixes are available to you.