SQL Server Wait Statistics Report: dm_os_wait_stats

calendar Feb 27, 2026 / · 6 min read · sql server wait statistics performance dm os wait stats wait types bottleneck analysis dba scripts performance tuning  ·
Share on: linkedin copy

Wait statistics are one of the most reliable ways to identify performance bottlenecks in SQL Server. This T-SQL script queries sys.dm_os_wait_stats, filters out background and idle wait types that do not indicate real problems, and ranks the remaining waits by their percentage of total wait time.

Purpose and Overview

Every time SQL Server cannot immediately process a request — because it is waiting for CPU, disk, memory, locks, or network — it records that wait in sys.dm_os_wait_stats. Over time this DMV accumulates a comprehensive picture of where SQL Server spends time waiting.

The challenge is that many wait types are benign background activity (checkpoint writes, service broker, log writer sleep, and others) that are normal and expected. Leaving them in the results obscures the waits that actually matter. This script uses a CTE to define and exclude the known benign waits, then calculates each remaining wait type as a percentage of the total, so you can focus immediately on the top contributors to real performance problems.

Use this report as the starting point for any performance investigation. The top wait type tells you where to look next.

Code Breakdown

 1WITH
 2    benign_waits AS (
 3        SELECT wait_type
 4        FROM (VALUES
 5            ('SLEEP_TASK'),
 6            ('SLEEP_SYSTEMTASK'),
 7            ('SLEEP_TEMPDBSTARTUP'),
 8            ('SLEEP_DBSTARTUP'),
 9            ('SLEEP_DCOMSTARTUP'),
10            ('SLEEP_MASTERDBREADY'),
11            ('SLEEP_MASTERMDREADY'),
12            ('SLEEP_MASTERUPGRADED'),
13            ('SLEEP_MSDBSTARTUP'),
14            ('SLEEP_TEMPDBSTARTUP'),
15            ('SLEEP_WAITTASK'),
16            ('SLEEP_WORKER_POOL_INITIALIZATION'),
17            ('WAITFOR'),
18            ('LAZYWRITER_SLEEP'),
19            ('SQLTRACE_BUFFER_FLUSH'),
20            ('SQLTRACE_INCREMENTAL_FLUSH_SLEEP'),
21            ('SQLTRACE_WAIT_ENTRIES'),
22            ('LOGMGR_QUEUE'),
23            ('CHECKPOINT_QUEUE'),
24            ('REQUEST_FOR_DEADLOCK_SEARCH'),
25            ('XE_TIMER_EVENT'),
26            ('XE_DISPATCHER_WAIT'),
27            ('BROKER_TO_FLUSH'),
28            ('BROKER_TASK_STOP'),
29            ('BROKER_EVENTHANDLER'),
30            ('BROKER_TRANSMITTER'),
31            ('DISPATCHER_QUEUE_SEMAPHORE'),
32            ('FT_IFTS_SCHEDULER_IDLE_WAIT'),
33            ('XIO_IDLE'),
34            ('SNI_HTTP_ACCEPT'),
35            ('DBMIRROR_EVENTS_QUEUE'),
36            ('ONDEMAND_TASK_QUEUE'),
37            ('SERVER_IDLE_CHECK'),
38            ('HADR_WORK_QUEUE'),
39            ('HADR_FILESTREAM_IOMGR_IOCOMPLETION'),
40            ('SP_SERVER_DIAGNOSTICS_SLEEP'),
41            ('CLR_AUTO_EVENT'),
42            ('DIRTY_PAGE_POLL'),
43            ('PREEMPTIVE_OS_LIBRARYOPS'),
44            ('QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'),
45            ('QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'),
46            ('REDO_THREAD_PENDING_WORK'),
47            ('WAIT_XTP_OFFLINE_CKPT_NEW_LOG')
48        ) AS w(wait_type)
49    ),
50    filtered_waits AS (
51        SELECT
52            wait_type,
53            waiting_tasks_count,
54            wait_time_ms,
55            max_wait_time_ms,
56            signal_wait_time_ms
57        FROM sys.dm_os_wait_stats
58        WHERE wait_type NOT IN (SELECT wait_type FROM benign_waits)
59          AND wait_type NOT LIKE 'SLEEP_%'
60          AND wait_type NOT LIKE 'BROKER_%'
61          AND wait_type NOT LIKE 'XE_%'
62          AND wait_type NOT LIKE 'SQLTRACE_%'
63          AND wait_type NOT LIKE 'QDS_%'
64          AND wait_type NOT LIKE 'HADR_%'
65          AND waiting_tasks_count > 0
66    ),
67    totals AS (
68        SELECT SUM(wait_time_ms) AS total_wait_ms
69        FROM filtered_waits
70    )
71SELECT TOP 20
72    fw.wait_type,
73    fw.waiting_tasks_count,
74    fw.wait_time_ms,
75    fw.max_wait_time_ms,
76    fw.signal_wait_time_ms,
77    fw.wait_time_ms - fw.signal_wait_time_ms  AS resource_wait_time_ms,
78    CASE WHEN fw.waiting_tasks_count > 0
79        THEN ROUND(
80            CAST(fw.wait_time_ms AS FLOAT) / fw.waiting_tasks_count,
81            2)
82        ELSE 0
83    END                                        AS avg_wait_ms,
84    ROUND(
85        100.0 * fw.wait_time_ms / NULLIF(t.total_wait_ms, 0),
86        2
87    )                                          AS pct_of_total_waits
88FROM
89    filtered_waits AS fw
90    CROSS JOIN totals AS t
91ORDER BY
92    fw.wait_time_ms DESC;

Benign Wait Filter CTE

The benign_waits CTE lists all wait types that represent normal background SQL Server activity — things like checkpoint queue processing, service broker threads, log manager sleep, and Extended Events dispatcher waits. These waits are always present and do not indicate a performance problem.

The filtered_waits CTE applies the exclusion list plus pattern-based filters (SLEEP_%, BROKER_%, XE_%, etc.) to catch entire families of background waits without listing each individually. The waiting_tasks_count > 0 filter removes wait types that have never occurred since the last restart.

Signal vs Resource Wait Time

sys.dm_os_wait_stats splits wait_time_ms into two components:

  • signal_wait_time_ms — time a task spent waiting to be scheduled on CPU after the resource became available. High signal waits indicate CPU pressure
  • resource_wait_time_ms (calculated as wait_time_ms - signal_wait_time_ms) — time spent waiting for the actual resource (disk, lock, memory). This is where most bottlenecks show up

If signal waits are a large fraction of total waits, the server is under CPU pressure. If resource waits dominate, the bottleneck is elsewhere (disk, locks, memory, network).

Average Wait Time per Task

avg_wait_ms divides total wait time by the number of waiting tasks to show the average duration per wait event. A wait type with a very high average (e.g., PAGEIOLATCH waits averaging 500 ms) is more alarming than one with a low average regardless of its total.

Percentage of Total Waits

The pct_of_total_waits column shows each wait type as a percentage of all non-benign wait time since the last restart. The top wait type is the most meaningful indicator of what SQL Server is waiting on most. Focus your investigation there first.

Key Benefits and Use Cases

  • Bottleneck identification — the top wait type immediately tells you whether the server is waiting on disk (PAGEIOLATCH), locks (LCK_M_*), CPU (SOS_SCHEDULER_YIELD), memory (RESOURCE_SEMAPHORE), or network (ASYNC_NETWORK_IO)
  • Filtered noise — benign waits are excluded so only actionable waits appear
  • Percentage ranking — easier to prioritize than raw millisecond totals
  • Signal vs resource split — distinguishes CPU pressure from resource contention
  • Baseline comparison — save results before and after changes to measure impact

Performance Considerations

This script reads only the in-memory sys.dm_os_wait_stats DMV and has negligible overhead — it is safe to run at any time including during active incidents.

  • Cumulative since restart — the data accumulates from the last SQL Server restart or the last time DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) was run. On long-running instances, older workloads may skew the results
  • Not a real-time snapshot — the DMV shows aggregate history, not current activity. For current waits, query sys.dm_os_waiting_tasks instead
  • Instance-level view — waits are aggregated across all databases on the instance. A specific database's waits are not isolated here; use Query Store or Extended Events for per-database or per-query wait data
  • CXPACKET waits — if CXPACKET (parallel query waits) is at the top, investigate MAXDOP and cost threshold for parallelism settings before concluding there is a parallelism problem

Practical Tips

  • Clear and re-baseline — run DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) at the start of a load test or peak period, then capture the results afterward for a focused snapshot
  • Save historical snapshots — insert the top 20 rows into a history table on a schedule (e.g., every hour) to track how the wait profile changes over time
  • Cross-reference with Paul Randal's wait types list — his published list maps each wait type to its meaning and recommended investigation steps (see References)
  • Pair with sys.dm_os_waiting_tasks — for real-time blocked session analysis, query sys.dm_os_waiting_tasks to see individual tasks currently waiting
  • Watch for WRITELOG — high WRITELOG waits indicate transaction log I/O bottleneck; investigate the LDF file location and consider moving it to a faster disk or enabling instant file initialization

Conclusion

Wait statistics are the most direct window into SQL Server performance bottlenecks. This script filters out the noise of background waits and presents the top wait types as a percentage of total activity, making it easy to identify where SQL Server is spending the most time. Use it as the first step in any performance investigation to determine where to dig deeper.

References

Posts in this series