SQL Server Wait Types Library

Wait statistics are the single best first step in any SQL Server performance investigation. Every time a query waits on a resource, whether that’s a lock, disk I/O, a transaction log flush, a memory grant, or something else, SQL Server records it. By the time a performance problem appears, the evidence is usually already sitting in sys.dm_os_wait_stats. If you arrived here holding a specific wait type, jump straight to the Wait Type Finder: type the name, get the verdict.

First move: take a snapshot, wait five to ten minutes, take another, and diff them. Everything in sys.dm_os_wait_stats is cumulative since the last restart, and a month of uptime buries this morning’s problem under weeks of history; the interval is what tells you about now. On SQL Server 2017 and later, sys.query_store_wait_stats keeps per-query wait history with timestamps, which answers what a specific query was waiting on last Tuesday in a way the cumulative view never can.

---

Start With What The Server Is Short Of

Most waits are informational: filter them and move on. Thirteen actually drive real incidents, and each has a full, tool-backed troubleshooting workflow rather than a short reference entry. They are grouped below by what the server is running out of, because that is the decision the number leads to.

---

Why It Matters

Without wait statistics, a slow server is a black box. Is it the disk? Locking? CPU queue? Parallelism overhead? Memory pressure? Each of those requires a completely different fix, and the wrong diagnosis wastes hours.

Wait stats give you a ranked list of where time is being lost. If PAGEIOLATCH_SH is 60% of your total wait time, sessions are spending most of their time waiting on data-file reads: that is a symptom at the I/O boundary, and the cause behind it can be genuinely slow storage, a working set that stopped fitting in memory, or a plan change that started scanning. If WRITELOG dominates, commits are waiting on log flushes, which points at the log path: sometimes a slow log disk, just as often many tiny commits. If RESOURCE_SEMAPHORE appears, queries are queuing for memory grants. A wait names the boundary where time is lost, not the component at fault; it tells you where to investigate next, and the deep-dive pages below tell you what to check there.

The catch: SQL Server also records a lot of completely harmless waits, SLEEP_TASK, LAZYWRITER_SLEEP, BROKER_TO_FLUSH and others that represent SQL Server’s own background processes doing nothing. If you don’t filter these out, they dominate the output and hide the real signals.

---

The Script

WITH filtered_waits AS (
    SELECT
        wait_type,
        waiting_tasks_count,
        wait_time_ms,
        max_wait_time_ms,
        signal_wait_time_ms,
        wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
    FROM sys.dm_os_wait_stats
    WHERE waiting_tasks_count > 0
      AND wait_type NOT IN (
          'SLEEP_TASK',                     'SLEEP_SYSTEMTASK',
          'SLEEP_TEMPDBSTARTUP',            'SLEEP_DBSTARTUP',
          'SLEEP_DCOMSTARTUP',              'SLEEP_MASTERDBREADY',
          'SLEEP_MASTERMDREADY',            'SLEEP_MASTERUPGRADED',
          'SLEEP_MSDBSTARTUP',              'SNI_HTTP_ACCEPT',
          'DISPATCHER_QUEUE_SEMAPHORE',     'BROKER_TO_FLUSH',
          'BROKER_TASK_STOP',               'BROKER_EVENTHANDLER',
          'BROKER_RECEIVE_WAITFOR',         'CHECKPOINT_QUEUE',
          'DBMIRROR_EVENTS_QUEUE',          'DBMIRROR_WORKER_QUEUE',
          'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','SQLTRACE_BUFFER_FLUSH',
          'SQLTRACE_WAIT_ENTRIES',          'WAITFOR',
          'LAZYWRITER_SLEEP',               'LOGMGR_QUEUE',
          'ONDEMAND_TASK_QUEUE',            'REQUEST_FOR_DEADLOCK_SEARCH',
          'RESOURCE_QUEUE',                 'SERVER_IDLE_CHECK',
          'SP_SERVER_DIAGNOSTICS_SLEEP',    'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
          'XE_DISPATCHER_WAIT',             'XE_TIMER_EVENT',
          'HADR_WORK_QUEUE',                'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
          'HADR_CLUSAPI_CALL',              'HADR_NOTIFICATION_DEQUEUE',
          'FT_IFTS_SCHEDULER_IDLE_WAIT',    'FT_IFTSHC_MUTEX',
          'REPL_WORK_QUEUE',                'CLR_AUTO_EVENT',
          'CLR_MANUAL_EVENT',               'WAIT_XTP_COMPILE_WAIT'
      )
)
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    CAST(100.0 * wait_time_ms / NULLIF(SUM(wait_time_ms) OVER (), 0) AS DECIMAL(5,2)) AS pct_total_wait,
    CAST(wait_time_ms / NULLIF(waiting_tasks_count, 0) AS DECIMAL(10,0))              AS avg_wait_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    resource_wait_time_ms
FROM filtered_waits
ORDER BY wait_time_ms DESC;

The script removes the common idle and background waits so that the output focuses on waits that are more likely to indicate a real performance bottleneck.

---

How To Run It

# Table output - quick triage view
.\run.ps1 Get-WaitStatistics

# Save to CSV for comparison over time
.\run.ps1 Get-WaitStatistics -OutputFormat Csv

# Against a named instance
.\run.ps1 Get-WaitStatistics -ServerInstance MYSERVER\INST01 -OutputFormat Csv

---

Reading The Output

Column	What It Means
wait_type	Name of the wait. The SQL Server documentation has a full list.
waiting_tasks_count	How many tasks have waited on this type since the last restart
wait_time_ms	Total cumulative wait time in milliseconds since last restart
pct_total_wait	This wait type’s share of all non-idle wait time. The most useful column for ranking.
avg_wait_ms	Average wait time per occurrence, helps distinguish frequent short waits from rare long ones
max_wait_time_ms	Longest single wait ever recorded for this type
signal_wait_time_ms	Time spent waiting for a CPU scheduler slot after the resource became available. High values here indicate CPU pressure.
resource_wait_time_ms	Time waiting for the actual resource (I/O, lock, memory, etc.)

The pct_total_wait column is the one to sort by mentally. The top two or three wait types that together account for 60–80% of total wait time are your investigation targets. Treat the 60–80% figure as the usual shape, not a rule; the signal is that a few wait types dominate, whatever the exact split.

---

Production Notes

---

Important: These Are Cumulative Since Last Restart

sys.dm_os_wait_stats accumulates from the moment SQL Server started. On a server that’s been up for 6 months, today’s problem might be buried under months of historical data. If you’re investigating a specific incident, compare two snapshots taken before and after the problem window, or use a baseline snapshot tool.

---

This library is also served to AI assistants through the sqldba MCP server. Its explain_wait tool returns the verdict and the write-up link for a wait type, and says plainly when a type is not covered rather than inventing an explanation for it.

Related Scripts

• Long-Running Queries, see which active queries are waiting right now, and on what
• Blocking Sessions, the next step when LCK_M_* waits are high
• Database IO Usage, which databases are driving the I/O when PAGEIOLATCH dominates
• RESOURCE_SEMAPHORE Waits, the full workflow when memory grants are the bottleneck

---

Get The Scripts

The full script is available in the dba-tools repo on GitHub:

• sql/performance/Get-WaitStatistics.sql
• powershell/reporting/Get-WaitStatistics.ps1

---

The Library, A to Z

Every documented SQL Server wait type, searchable. Paste the name you are seeing in your own output, or filter by whether it is worth your time. Family pages cover related waits together; the thirteen that drive real incidents have full troubleshooting guides.

---