SQL Server Wait Statistics: A Practical Diagnostic Workflow

Wait statistics show what SQL Server sessions spend their time waiting for instead of running. Take two snapshots of sys.dm_os_wait_stats a few minutes apart, subtract them, remove idle waits, and read the biggest wait type: LCK_M_* points to blocking, PAGEIOLATCH_* to reads from storage, WRITELOG to log flushes, SOS_SCHEDULER_YIELD to CPU. Then confirm with the queries behind it.

A wait type is a symptom, not a verdict. This guide gives you a repeatable workflow: measure, narrow down, find the queries, change one thing, measure again. It includes a symptom-to-evidence-to-fix matrix, a decision tree and read-only T-SQL you can adapt for your own instance.

Last verified: 19 October 2026. Checked against Microsoft Learn (wait statistics, current requests and waiting tasks, Query Store wait statistics). T-SQL tested on SQL Server 2025 (build 17.0.4055.5).

What Wait Statistics Tell You (and What They Don’t)

sys.dm_os_wait_stats returns information about all the waits encountered by threads that executed, aggregated across the instance. It shows the time for waits that have completed, not current waits. The numbers are cumulative since the last restart or counter reset and are not persisted across restarts.

Two columns matter most. wait_time_ms includes signal_wait_time_ms, the time between a thread being signaled and actually getting back on the CPU. So wait_time_ms - signal_wait_time_ms is the time spent waiting for the resource itself, and a large signal share points to CPU scheduling pressure rather than the named resource.

Waits also do not tell you which query is responsible, and a cumulative total mixes nightly jobs with business-hours load. That is why the workflow below uses a short delta sample, then per-request and per-query views.

The Five-Step Diagnostic Workflow

All scripts below only read from system views, except that script 2 creates a session-scoped temporary table in tempdb. They need VIEW SERVER STATE on SQL Server; for Azure SQL Database Premium tiers the documented requirement for the wait-stats view is VIEW DATABASE STATE.

Step 1: Get a first picture since the last restart

The idle-wait exclusion list is a pragmatic starting point, not an official Microsoft list. Microsoft’s Query Store wait categories classify several of these names, such as SLEEP_%, LAZYWRITER_SLEEP, XE_DISPATCHER_WAIT and REQUEST_FOR_DEADLOCK_SEARCH, as Idle. Review the list for your version before you trust it.

-- Read-only. Cumulative waits since the last restart or counter reset.
-- The exclusion list is a starting point, not an official list.
SELECT TOP (15)
       ws.wait_type,
       ws.waiting_tasks_count,
       ws.wait_time_ms,
       ws.wait_time_ms - ws.signal_wait_time_ms AS resource_wait_ms,
       ws.signal_wait_time_ms,
       CAST(1.0 * ws.wait_time_ms / ws.waiting_tasks_count AS decimal(18, 2)) AS avg_wait_ms,
       CAST(100.0 * ws.wait_time_ms
            / NULLIF(SUM(ws.wait_time_ms) OVER (), 0) AS decimal(5, 2)) AS pct_of_listed_wait_time
FROM sys.dm_os_wait_stats AS ws
WHERE ws.waiting_tasks_count > 0
  AND ws.wait_type NOT LIKE N'SLEEP[_]%'
  AND ws.wait_type NOT IN (
        N'LAZYWRITER_SLEEP', N'SLEEP_TASK', N'SQLTRACE_BUFFER_FLUSH',
        N'XE_TIMER_EVENT', N'XE_DISPATCHER_WAIT', N'XE_DISPATCHER_JOIN',
        N'XE_LIVE_TARGET_TVF', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'CHECKPOINT_QUEUE', N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',
        N'BROKER_RECEIVE_WAITFOR', N'DIRTY_PAGE_POLL', N'LOGMGR_QUEUE',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'ONDEMAND_TASK_QUEUE',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'WAITFOR')
ORDER BY ws.wait_time_ms DESC;

Step 2: Measure a delta during the problem window

Run this while the slowness is happening. It captures the counters, waits one minute, and shows only the waits that accumulated in between. If the counters are cleared or the instance restarts during the sample, rows with a negative difference are filtered out and you should repeat the sample.

-- Creates a session-scoped temp table only; reads system views.
IF OBJECT_ID(N'tempdb..#w1') IS NOT NULL DROP TABLE #w1;

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

WAITFOR DELAY '00:01:00';   -- sample length: lengthen for intermittent problems

SELECT TOP (15)
       w2.wait_type,
       w2.waiting_tasks_count - w1.waiting_tasks_count AS waits_in_sample,
       w2.wait_time_ms - w1.wait_time_ms AS wait_ms_in_sample,
       w2.signal_wait_time_ms - w1.signal_wait_time_ms AS signal_ms_in_sample
FROM sys.dm_os_wait_stats AS w2
JOIN #w1 AS w1 ON w1.wait_type = w2.wait_type
WHERE w2.wait_time_ms - w1.wait_time_ms > 0
  AND w2.wait_type NOT LIKE N'SLEEP[_]%'
  AND w2.wait_type NOT IN (
        N'LAZYWRITER_SLEEP', N'SLEEP_TASK', N'SQLTRACE_BUFFER_FLUSH',
        N'XE_TIMER_EVENT', N'XE_DISPATCHER_WAIT', N'XE_DISPATCHER_JOIN',
        N'XE_LIVE_TARGET_TVF', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'CHECKPOINT_QUEUE', N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',
        N'BROKER_RECEIVE_WAITFOR', N'DIRTY_PAGE_POLL', N'LOGMGR_QUEUE',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'ONDEMAND_TASK_QUEUE',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'WAITFOR')
ORDER BY wait_ms_in_sample DESC;

DROP TABLE #w1;

Step 3: See who is waiting right now

sys.dm_exec_requests shows currently executing requests; wait_type and wait_time are filled only while the request is waiting, and last_wait_type shows its previous wait. Without VIEW SERVER STATE you see only your own session. sys.dm_os_waiting_tasks adds one row per waiting task, including parallel worker threads. The script also uses sys.dm_exec_sessions to skip system sessions and sys.dm_exec_sql_text to read the statement text.

-- Read-only. Requests that are running or waiting right now.
SELECT r.session_id,
       r.status,
       r.command,
       DB_NAME(r.database_id) AS database_name,
       r.wait_type,
       r.wait_time AS wait_ms,
       r.last_wait_type,
       r.wait_resource,
       r.blocking_session_id,
       r.cpu_time AS cpu_ms,
       r.total_elapsed_time AS elapsed_ms,
       r.logical_reads,
       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 s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

-- Read-only. One row per waiting task (parallel queries show several rows).
SELECT wt.session_id,
       wt.exec_context_id,
       wt.wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.resource_description
FROM sys.dm_os_waiting_tasks AS wt
JOIN sys.dm_exec_sessions AS s ON s.session_id = wt.session_id
WHERE s.is_user_process = 1
ORDER BY wt.wait_duration_ms DESC;

Step 4: Find the queries behind the wait (history)

If Query Store is on (SQL Server 2017 and later, and Azure SQL Database), sys.query_store_wait_stats aggregates waits per plan, interval and wait category rather than per raw wait type. Microsoft’s mapping puts for example LCK_M_% into Lock, PAGEIOLATCH_% into Buffer IO and WRITELOG into Tran Log IO. The Query Store reports include a Query Wait Statistics view for the same data. Our Query Store guide covers enabling and sizing it.

-- Read-only. Top non-idle wait categories per query over the last 24 hours.
-- Requires Query Store to be enabled; total_query_wait_time_ms is in milliseconds.
SELECT TOP (20)
       ws.wait_category_desc,
       q.query_id,
       p.plan_id,
       SUM(ws.total_query_wait_time_ms) AS total_wait_ms,
       LEFT(qt.query_sql_text, 200) AS query_text_start
FROM sys.query_store_wait_stats AS ws
JOIN sys.query_store_runtime_stats_interval AS i
     ON i.runtime_stats_interval_id = ws.runtime_stats_interval_id
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
WHERE i.start_time >= DATEADD(HOUR, -24, SYSDATETIMEOFFSET())
  AND ws.wait_category_desc <> N'Idle'
GROUP BY ws.wait_category_desc, q.query_id, p.plan_id, LEFT(qt.query_sql_text, 200)
ORDER BY total_wait_ms DESC;

The interval view used for the time filter has a start_time column of type datetimeoffset. Without Query Store, a CPU-focused history is still available from sys.dm_exec_query_stats; see our guide to finding slow queries with DMVs.

Step 5: Change one thing, then measure again

Fix the cause the evidence points to, using the matrix below, and then repeat the step 2 sample over a comparable window. If the top wait just moves down the list, that is normal progress, not a failed fix. Write the before and after numbers down so the next person can see what changed.

Symptom → Evidence → Fix Matrix

Microsoft’s own descriptions of each wait type are the basis for the second column. The fix direction is where judgment starts, so treat it as the next thing to investigate rather than an instruction.

Decision Tree: From Top Wait to Next Action

Work through the questions in order and stop at the first branch that matches.

1. Is the slowness happening right now? Yes: run step 3 and read wait_type, blocking_session_id and the statement. No or intermittent: use the step 2 delta during the next occurrence, or Query Store history in step 4.
2. Is a blocking_session_id filled in? Yes: follow the chain to the head blocker and fix that session’s transaction or query. This is a locking problem even if the top cumulative wait is something else.
3. Is the top non-idle wait mostly resource wait or signal wait? Compare resource_wait_ms with signal_ms_in_sample or signal_wait_time_ms.
   • Signal share is high: CPU scheduling pressure. Start with the top CPU queries.
   • LCK_M_*: blocking. PAGEIOLATCH_*: read I/O and scans. WRITELOG: commit rate and log storage. RESOURCE_SEMAPHORE: memory requests. ASYNC_NETWORK_IO: slow client.
   • CXPACKET/CXCONSUMER only: read the parallelism row in the matrix before touching MAXDOP.
4. Can you name the queries that produce the wait? No: use step 4 (Query Store) or the DMV-based slow-query search. Yes: tune those queries, then repeat step 2.
5. Is the same query slow only for some parameter values? That is a plan problem, not a resource problem. See our parameter sniffing guide.

Official Behavior, Aryasoft Field Notes and Our Recommendation

Common Mistakes

• Tuning to the top cumulative wait. It may belong to last night’s job.
• Raising MAXDOP or max worker threads because of CXPACKET or THREADPOOL. Microsoft’s wording points to tuning the query first for CXPACKET and names long-running batches as one cause of THREADPOOL.
• Ignoring signal waits. A high signal share means threads were ready but waiting to get back on the CPU.
• Changing several things at once. You will not know what helped.
• Treating ASYNC_NETWORK_IO as a network team problem. Microsoft says to verify that the client is processing data from the server.
• More patterns like these are in our SQL Server performance tuning mistakes article.

FAQ

How Aryasoft Works on Wait-Based Troubleshooting

In a performance assessment we start from the symptom the business reports, collect a wait delta and per-query evidence during that window, and change one thing at a time with a documented before and after.

From the field: at a large manufacturer, a month-end report blocked the whole system. The blocking chain led to that report as the head blocker; the team ended the session and the system was back to normal within 12 minutes. The report itself was then tuned, and its runtime dropped from 4 hours to 19 minutes. Our SQL Server consulting page describes the wider scope.

Sources

Technical claims in this guide were checked against the Microsoft documentation below on 19 October 2026. The date shown is the “ms.date” value of the documentation source file, not a guarantee that the page is unchanged. Re-check version-specific details before a production change.

1. sys.dm_os_wait_stats (Transact-SQL) (ms.date: 05/16/2019)
2. sys.dm_os_waiting_tasks (Transact-SQL) (ms.date: 03/13/2017)
3. sys.dm_exec_requests (Transact-SQL) (ms.date: 06/03/2019)
4. sys.dm_exec_session_wait_stats (Transact-SQL) (ms.date: 04/24/2018)
5. sys.query_store_wait_stats (Transact-SQL) (ms.date: 04/21/2017)
6. Best practices for monitoring workloads with Query Store (ms.date: 01/26/2026)
7. sys.dm_exec_sessions (Transact-SQL) (ms.date: 06/03/2019)
8. sys.dm_exec_sql_text (Transact-SQL) (ms.date: 10/20/2017)
9. sys.query_store_runtime_stats_interval (Transact-SQL) (ms.date: 01/23/2019)
10. sys.dm_exec_query_stats (Transact-SQL) (ms.date: 05/30/2019)