DBA Scripts: Get Wait Statistics

Every time a SQL Server session has to wait for something, a lock, a page from disk, a CPU slice, a memory grant, the engine records what it waited on and for how long. Those counters are the instance’s own diary of where time goes, and reading them is the quickest way to find out what kind of pressure a server is actually under.

The problem is that sys.dm_os_wait_stats mixes the signal in with hundreds of idle and background waits that mean nothing for troubleshooting. This script filters the noise out and returns the top 20 waits that actually matter, with percentages and signal-versus-resource splits so the story is readable at a glance.

This post covers the script itself. For what each individual wait type means, the full reference lives in the SQL Server Wait Statistics library, which documents hundreds of wait types with causes and fixes.

Why Wait Statistics Matter

Wait statistics are the first stop in almost every SQL Server performance investigation, because they tell you which subsystem to look at before you start guessing. High PAGEIOLATCH_* waits point at storage, LCK_M_* at blocking, SOS_SCHEDULER_YIELD and high signal waits at CPU, RESOURCE_SEMAPHORE at memory grants. Each one names the boundary where time is lost, not the component at fault: PAGEIOLATCH_SH can be slow storage, a working set that stopped fitting in memory, or a plan that started scanning, and WRITELOG is as often many tiny commits as a slow log disk.

Starting anywhere else means picking a subsystem on instinct. Starting with waits means the instance tells you where it hurts, and everything after that is confirmation rather than exploration.

When to Run This Script

• Routine SQL Server health checks
• At the start of any performance investigation, before deeper query-level analysis
• After a workload change or migration, to compare the wait profile against the old baseline
• When the server feels slow but no single query stands out

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-WaitStatistics
Category    : performance-troubleshooting
Purpose     : Top wait types since last SQL Server restart, filtered to actionable waits only.
Author      : Peter Whyte (https://sqldba.blog/sql-server-wait-statistics/)
Requires    : VIEW SERVER STATE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

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 (
          -- Idle / background scheduler waits, not indicative of workload pressure
          'SLEEP_TASK',                     'SLEEP_SYSTEMTASK',
          'SLEEP_TEMPDBSTARTUP',            'SLEEP_DBSTARTUP',
          'SLEEP_DCOMSTARTUP',              'SLEEP_MASTERDBREADY',
          'SLEEP_MASTERMDREADY',            'SLEEP_MASTERUPGRADED',
          'SLEEP_MSDBSTARTUP',              'SLEEP_PHYSMASTERDBREADY',
          'SLEEP_SAFEMODE',                 'SLEEP_SETUP',
          'SLEEP_RBPEXSHRINKTASK',          'SLEEP_RETRY_VIRTUALALLOC',
          'SLEEP_BPOOL_FLUSH',              'SLEEP_WORKSPACE_ALLOCATEPAGE',
          -- SLEEP_BPOOL_STEAL, SLEEP_BUFFERPOOL_HELPLW and SLEEP_MEMORYPOOL_ALLOCATEPAGES
          -- are deliberately NOT here: free-list stalls, near zero on a healthy
          -- instance, and a memory-pressure signal when they rank.
          'DISPATCHER_QUEUE_SEMAPHORE',     'CHECKPOINT_QUEUE',
          'DBMIRROR_EVENTS_QUEUE',          'DBMIRROR_WORKER_QUEUE',
          'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','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_TIMER_EVENT',
          'HADR_WORK_QUEUE',                'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
          'HADR_CLUSAPI_CALL',              'HADR_NOTIFICATION_DEQUEUE',
          'FT_IFTS_SCHEDULER_IDLE_WAIT',    'FT_IFTSHC_MUTEX',
          'CLR_AUTO_EVENT',                 'CLR_MANUAL_EVENT',
          'WAIT_XTP_COMPILE_WAIT',
          -- Service Broker threads parked waiting for a message. MS Docs on
          -- BROKER_TRANSMITTER: high waiting_tasks_count "aren't indications of any
          -- performance problem". The transmission queue/table waits are NOT listed
          -- here: those move real messages and can stall for real.
          'BROKER_TO_FLUSH',                'BROKER_TASK_STOP',
          'BROKER_EVENTHANDLER',            'BROKER_RECEIVE_WAITFOR',
          'BROKER_TRANSMITTER',             'BROKER_INIT',
          'BROKER_START',                   'BROKER_SHUTDOWN',
          'BROKER_MASTERSTART',             'BROKER_DISPATCHER',
          'BROKER_SERVICE',                 'BROKER_FORWARDER',
          'BROKER_REGISTERALLENDPOINTS',    'BROKER_TASK_SUBMIT',
          'BROKER_TASK_SHUTDOWN',           'BROKER_PRIORITIZED_TASK_STOP',
          -- Extended Events plumbing: dispatcher, session and buffer bookkeeping.
          -- These rank by call count, not by stall. Measured on an idle instance:
          -- PREEMPTIVE_XE_CALLBACKEXECUTE 132,833,917 waits for 3,828,650 ms, an
          -- average of 0.00003 ms each. XE_FILE_TARGET_TVF meters reads of event files
          -- through sys.fn_xe_file_target_read_file: the reader's own cost, not a stall.
          'XE_DISPATCHER_WAIT',             'XE_DISPATCHER_JOIN',
          'XE_FILE_TARGET_TVF',
          'XE_LIVE_TARGET_TVF',             'XE_CALLBACK_LIST',
          'XE_MODULEMGR_SYNC',              'XE_SESSION_CREATE_SYNC',
          'XE_SESSION_FLUSH',               'XE_SESSION_SYNC',
          'XE_BUFFERMGR_ALLPROCESSED_EVENT','XE_BUFFERMGR_FREEBUF_EVENT',
          'XE_SQL_TEXT_HEAP_ALLOC',         'XE_SQL_TEXT_HEAP_FREE',
          'PREEMPTIVE_XE_DISPATCHER',       'PREEMPTIVE_XE_GETTARGETSTATE',
          'PREEMPTIVE_XE_CALLBACKEXECUTE',  'PREEMPTIVE_XE_ENGINEINIT',
          'PREEMPTIVE_XE_SESSIONCOMMIT',    'PREEMPTIVE_XE_TARGETINIT',
          'PREEMPTIVE_XE_TARGETFINALIZE',   'PREEMPTIVE_XE_TIMERRUN',
          -- Idle / background waits introduced SQL Server 2016 to 2022
          'SOS_WORK_DISPATCHER',            'DIRTY_PAGE_POLL',
          'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','QDS_ASYNC_QUEUE',
          'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
          'QDS_SHUTDOWN_QUEUE',             'QDS_TASK_START',
          'QDS_ASYNC_PERSIST_TASK_START',   'PWAIT_EXTENSIBILITY_CLEANUP_TASK',
          'PARALLEL_REDO_WORKER_WAIT_WORK', 'HADR_TIMER_TASK',
          -- AG plumbing that idles by design: log capture caught up, redo waiting for
          -- work, and the parallel redo coordination waits MS Docs lists as internal.
          'HADR_LOGCAPTURE_WAIT',           'REDO_THREAD_PENDING_WORK',
          'PARALLEL_REDO_DRAIN_WORKER',     'PARALLEL_REDO_LOG_CACHE',
          'PARALLEL_REDO_TRAN_LIST',        'PARALLEL_REDO_WORKER_SYNC',
          -- Memory allocation bookkeeping: millions of microsecond waits on every instance.
          'MEMORY_ALLOCATION_EXT',
          'PVS_PREALLOCATE',                'WAIT_XTP_HOST_WAIT',
          -- Older builds only. Absent from sys.dm_os_wait_stats on 2025 (17.0.4075.5),
          -- so they exclude nothing there; kept because they are still returned on the
          -- builds that have them. Excluding a name that does not exist costs nothing.
          'REPL_WORK_QUEUE',                'SNI_HTTP_ACCEPT',
          'SQLTRACE_BUFFER_FLUSH'
      )
)
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;

It reads sys.dm_os_wait_stats, excludes the idle and background wait types, and returns the top 20 remaining waits with each one’s share of total wait time and its signal-versus-resource split.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check top wait types since last restart:
.\run.ps1 Get-WaitStatistics

# Save to CSV, to compare two runs taken minutes apart:
.\run.ps1 Get-WaitStatistics -OutputFormat Csv

# To run against a remote sql server:
.\run.ps1 Get-WaitStatistics -ServerInstance SQLSERVER01

This script lives in the repo at:

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

Example Output

The result set shows the top wait types ordered by total wait time, with each type’s percentage of overall waits and the split between signal time and resource time.

This is a test instance, so your own profile will look different. Read the top few percentages first, see what the server is spending most of its time waiting on, then follow that into the matching troubleshooting guide. Take it as a hint rather than a diagnosis, sometimes a strong one, sometimes a small clue.

Understanding the Results

8 columns, in the order the script returns them. pct_total_wait is the one to read first, and the signal split at the end is the one most people skip. Every value is cumulative since the counters were last reset, so for an incident read the columns as the difference between two runs, not as one run on its own.

For what any specific wait type means and how to fix it, look it up in the wait statistics library, which has a dedicated page per wait with causes, thresholds and fixes.

Best Practices

• Measure deltas, not lifetime totals. The counters accumulate since the last restart, so an instance up for months carries months of history. For an incident, capture the output twice, ten minutes apart, and compare; the difference is what is happening now. Cumulative Wait Stats Lie: Measuring Waits Over an Interval has a script that takes both snapshots and does the subtraction.
• Look back with Query Store. On SQL Server 2017 and later, with Query Store on, sys.query_store_wait_stats keeps per-query wait history by wait category and time interval, which answers what one query was waiting on last Tuesday in a way the cumulative view cannot.
• Baseline when things are fine. The wait profile on a good day is the only fair comparison for a bad day.
• Reset deliberately when needed. DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR); zeroes the counters, which is useful after a config change, but note it clears the history for everyone.
• Do not chase a wait just because it is top. Every instance always has a top wait. It only matters if it holds real time in the interval you measured, the average is high, or it changed against your baseline.

Microsoft’s reference covers sys.dm_os_wait_stats and DBCC SQLPERF in full.

Frequently Asked Questions

Summary

Wait statistics are the instance telling you where its time went, and this script turns that raw diary into a readable top-20 with the noise already removed. Read the percentage column for the headline, the averages for the pain level, and the signal split for hidden CPU pressure.

It runs as part of the dba-tools health check for a reason: a wait profile you glance at weekly is what makes the odd spike unmissable. And when a specific wait needs explaining, the wait statistics library carries the per-wait detail so this post does not have to. If the whole server feels slow and you do not yet know why, start with SQL Server Is Slow: What to Check First, in Order, which puts this script at step 3.

Related