Performance

SQL Server Wait Statistics Troubleshooting Guide

Download a production-oriented wait-stat script, collect a useful sample, interpret the output, and move from a wait category to the next piece of evidence.

2026-09-27

/*
===============================================================================
SQL Server Wait Statistics Analysis
Author  : Production Ready
Purpose : Identify top server waits with basic recommendations
===============================================================================
*/

SET NOCOUNT ON;

;WITH Waits AS
(
    SELECT
        wait_type,
        waiting_tasks_count,
        wait_time_ms,
        signal_wait_time_ms,
        resource_wait_ms =
            wait_time_ms - signal_wait_time_ms,
        avg_wait_ms =
            CASE
                WHEN waiting_tasks_count = 0 THEN 0
                ELSE wait_time_ms * 1.0 / waiting_tasks_count
            END
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN
    (
        'BROKER_EVENTHANDLER',
        'BROKER_RECEIVE_WAITFOR',
        'SOS_WORK_DISPATCHER',
        'BROKER_TASK_STOP',
        'BROKER_TO_FLUSH',
        'BROKER_TRANSMITTER',
        'CHECKPOINT_QUEUE',
        'CHKPT',
        'CLR_AUTO_EVENT',
        'CLR_MANUAL_EVENT',
        'CLR_SEMAPHORE',
        'DBMIRROR_DBM_EVENT',
        'DBMIRROR_EVENTS_QUEUE',
        'DBMIRROR_WORKER_QUEUE',
        'DBMIRRORING_CMD',
        'DIRTY_PAGE_POLL',
        'DISPATCHER_QUEUE_SEMAPHORE',
        'EXECSYNC',
        'FSAGENT',
        'FT_IFTS_SCHEDULER_IDLE_WAIT',
        'FT_IFTSHC_MUTEX',
        'HADR_CLUSAPI_CALL',
        'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        'HADR_LOGCAPTURE_WAIT',
        'HADR_NOTIFICATION_DEQUEUE',
        'HADR_TIMER_TASK',
        'HADR_WORK_QUEUE',
        'KSOURCE_WAKEUP',
        'LAZYWRITER_SLEEP',
        'LOGMGR_QUEUE',
        'MEMORY_ALLOCATION_EXT',
        'ONDEMAND_TASK_QUEUE',
        'PARALLEL_REDO_DRAIN_WORKER',
        'PARALLEL_REDO_LOG_CACHE',
        'PARALLEL_REDO_TRAN_LIST',
        'PARALLEL_REDO_WORKER_SYNC',
        'PARALLEL_REDO_WORKER_WAIT_WORK',
        'PREEMPTIVE_OS_FLUSHFILEBUFFERS',
        'PREEMPTIVE_XE_GETTARGETSTATE',
        'PWAIT_ALL_COMPONENTS_INITIALIZED',
        'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
        'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        'QDS_ASYNC_QUEUE',
        'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        'QDS_SHUTDOWN_QUEUE',
        'REDO_THREAD_PENDING_WORK',
        'REQUEST_FOR_DEADLOCK_SEARCH',
        'RESOURCE_QUEUE',
        'SERVER_IDLE_CHECK',
        'SLEEP_BPOOL_FLUSH',
        'SLEEP_DBSTARTUP',
        'SLEEP_DCOMSTARTUP',
        'SLEEP_MASTERDBREADY',
        'SLEEP_MASTERMDREADY',
        'SLEEP_MASTERUPGRADED',
        'SLEEP_MSDBSTARTUP',
        'SLEEP_SYSTEMTASK',
        'SLEEP_TASK',
        'SLEEP_TEMPDBSTARTUP',
        'SNI_HTTP_ACCEPT',
        'SP_SERVER_DIAGNOSTICS_SLEEP',
        'SQLTRACE_BUFFER_FLUSH',
        'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        'SQLTRACE_WAIT_ENTRIES',
        'WAIT_FOR_RESULTS',
        'WAITFOR',
        'WAITFOR_TASKSHUTDOWN',
        'WAIT_XTP_RECOVERY',
        'XE_DISPATCHER_JOIN',
        'XE_DISPATCHER_WAIT',
        'XE_TIMER_EVENT'
    )
),
Totals AS
(
    SELECT SUM(wait_time_ms) AS total_wait_ms
    FROM Waits
)
SELECT TOP (25)
    w.wait_type,
    WaitingTasks = w.waiting_tasks_count,
    TotalWaitSeconds = CAST(w.wait_time_ms/1000.0 AS DECIMAL(18,2)),
    ResourceWaitSeconds = CAST(w.resource_wait_ms/1000.0 AS DECIMAL(18,2)),
    SignalWaitSeconds = CAST(w.signal_wait_time_ms/1000.0 AS DECIMAL(18,2)),
    AvgWaitMs = CAST(w.avg_wait_ms AS DECIMAL(18,2)),
    PercentOfTotal =
        CAST((100.0 * w.wait_time_ms) / t.total_wait_ms AS DECIMAL(6,2)),
    Recommendation =
        CASE
            WHEN w.wait_type LIKE 'PAGEIOLATCH%' THEN
                'Storage latency. Check disk latency, memory pressure, missing indexes.'
            WHEN w.wait_type LIKE 'PAGELATCH%' THEN
                'TempDB or allocation contention.'
            WHEN w.wait_type = 'CXPACKET' THEN
                'Review parallelism (MAXDOP & Cost Threshold).'
            WHEN w.wait_type = 'CXCONSUMER' THEN
                'Usually benign unless accompanied by CXPACKET.'
            WHEN w.wait_type LIKE 'LCK_M_%' THEN
                'Blocking. Investigate long-running transactions.'
            WHEN w.wait_type = 'WRITELOG' THEN
                'Transaction log bottleneck.'
            WHEN w.wait_type LIKE 'ASYNC_NETWORK_IO' THEN
                'Client consuming results slowly.'
            WHEN w.wait_type = 'SOS_SCHEDULER_YIELD' THEN
                'CPU pressure or inefficient queries.'
            WHEN w.wait_type = 'RESOURCE_SEMAPHORE' THEN
                'Memory grant pressure.'
            WHEN w.wait_type = 'THREADPOOL' THEN
                'Worker thread exhaustion.'
            WHEN w.wait_type = 'PREEMPTIVE_XE_DISPATCHER' THEN
                'Extended Events writing to OS. Usually benign unless XE sessions or storage are slow.'
            WHEN w.wait_type = 'PREEMPTIVE_OS_CRYPTOPS' THEN
                'Windows Crypto API. Check TDE, TLS, backup encryption, Always Encrypted, EKM, certificate validation.'
            WHEN w.wait_type = 'SOS_WORK_DISPATCHER' THEN
                'Background worker dispatcher. Normally expected and can usually be ignored.'
            WHEN w.wait_type = 'XE_DISPATCHER_WAIT' THEN
                'Extended Events dispatcher idle wait. Normally benign.'
            WHEN w.wait_type = 'XE_TIMER_EVENT' THEN
                'Extended Events timer wait. Normally benign.'
            WHEN w.wait_type = 'HADR_SYNC_COMMIT' THEN
                'Always On synchronous replica latency.'
            WHEN w.wait_type = 'LOG_RATE_GOVERNOR' THEN
                'Transaction log throughput throttling (commonly Azure SQL Managed Instance or cloud environments).'
            WHEN w.wait_type = 'RESERVED_MEMORY_ALLOCATION_EXT' THEN
                'Memory allocation pressure. Investigate if sustained with RESOURCE_SEMAPHORE.'
            WHEN w.wait_type = 'SESSION_WAIT_STATS_CHILDREN' THEN
                'Internal wait for per-session wait statistics aggregation. Normally benign and can be ignored.'
            WHEN w.wait_type = 'CMEMTHREAD' THEN
                'Memory object contention caused by concurrent access to shared SQL Server memory structures. Common causes include excessive compilations, ad hoc plan cache bloat, high concurrency, and plan cache contention. Review compilation rate, Optimize for Ad Hoc Workloads, parameterization, and MAXDOP.'
            ELSE
                'Investigate based on workload.'
        END
FROM Waits w
CROSS JOIN Totals t
WHERE w.wait_time_ms > 0
ORDER BY PercentOfTotal DESC;

Review the full script and confirm the connected SQL Server instance before execution. The run instructions and output guide appear later in this article.

Diagram for SQL Server Wait Statistics Troubleshooting Guide — SQL Server Wait Statistics Troubleshooting Guide

SQL Server wait statistics show where workers spend time when they cannot continue immediately. They are one of the fastest ways to narrow a broad complaint such as “the database is slow,” but they do not identify the root cause by themselves. A wait type is a direction for the next investigation.

This guide uses the top_server_waits.sql diagnostic from the SQL Optima DBA script library. The companion Analysis.sql file in the source library is a written troubleshooting reference stored with a .sql extension; it is not an executable SQL script. The downloadable file below is the executable diagnostic.

Download the executable diagnostic

Download top_server_waits.sql

What the script returns

The script reads sys.dm_os_wait_stats, filters a list of common idle and background waits, and returns the 25 largest remaining wait types. It does not clear wait statistics and does not modify database objects.

Output column	Meaning
`wait_type`	The resource or condition SQL Server workers waited for
`WaitingTasks`	Number of completed waits accumulated for that type
`TotalWaitSeconds`	Total elapsed wait time, including resource and signal time
`ResourceWaitSeconds`	Time waiting for the resource to become available
`SignalWaitSeconds`	Time runnable workers waited to receive CPU scheduler time
`AvgWaitMs`	Average wait duration per completed wait
`PercentOfTotal`	Share of the filtered wait time represented by the row
`Recommendation`	A first investigation direction encoded in the script

The numbers are cumulative from the last SQL Server service start or the last explicit wait-stat reset. That makes the first result an instance history, not necessarily a picture of the incident happening now.

Permissions and safety

Use a login with VIEW SERVER STATE on SQL Server 2019 and earlier. On versions that support more granular server-performance permissions, an administrator may choose the equivalent documented permission. Do not grant sysadmin merely to run a diagnostic.

Before execution:

Open the file and review the complete query.
Confirm the SSMS or Azure Data Studio connection points to the intended instance.
Record the instance name, SQL Server start time, sample time, and incident window.
Prefer a read-only diagnostic login when your operating model supports one.
Do not clear server wait statistics during an incident unless the team explicitly approves losing the cumulative baseline.

The query reads a server DMV and returns one result set. Its cost is small, but every production script still deserves review and controlled execution.

Run the first sample

Open top_server_waits.sql in SQL Server Management Studio, connect to the affected instance, and select Execute. Save the grid as CSV or copy it into the incident record.

The initial result answers three useful questions:

Which non-idle waits dominate the instance history?
Are waits primarily resource time or signal time?
Does a wait occur frequently in small intervals or less often with a high average duration?

Do not stop at the recommendation column. It is a routing hint. The same wait can have several causes, and the correct cause depends on the workload and time window.

Prefer a delta during the problem window

For an active or repeatable issue, collect two samples around a defined interval and analyze the difference. A five-minute sample during the slowdown is often more useful than totals accumulated across several months.

A safe workflow is:

Run the script immediately before the problem window and save the result.
Wait through a representative period without resetting the DMV.
Run it again and save the second result.
Subtract the first counters from the second for matching wait types.
Rank the delta by wait time and compare it with application latency, throughput, and active queries from the same interval.

If you automate deltas, retain the raw counters and timestamps. Counter resets, restarts, failovers, or instance changes can otherwise produce misleading negative or discontinuous values.

Read resource and signal time separately

TotalWaitSeconds combines resource and signal time. Resource time means the worker could not continue because it waited for something such as a page read, log flush, lock, memory grant, or synchronization event. Signal time means the worker became runnable but waited for CPU scheduling.

A high signal share can support a CPU pressure investigation, especially when runnable tasks, host CPU, scheduler pressure, and workload latency agree. It does not prove that more CPU is the answer. Excessive compilation, inefficient queries, spin activity, virtualization contention, or too much concurrency can all increase pressure.

A high resource share points toward the category named by the leading waits. Continue with the corresponding query, storage, locking, memory, or HA evidence.

Common wait categories and the next check

Wait pattern	First interpretation	Evidence to collect next
`PAGEIOLATCH_*`	Waiting for data pages to arrive from storage	File latency, physical reads, memory pressure, scan-heavy plans, missing or ineffective indexes
`WRITELOG`	Waiting for transaction log flush	Log-file latency, transaction size, commit frequency, log throughput, synchronous replication overhead
`LCK_M_*`	Lock wait	Head blocker, open transaction, statements, lock resource, isolation level, access order
`RESOURCE_SEMAPHORE`	Waiting for query execution memory	Pending grants, large estimates, sort/hash operators, concurrency, memory configuration
`SOS_SCHEDULER_YIELD`	Worker yielded while consuming CPU	High-CPU queries, plan quality, scheduler load, compilation, host CPU
`CXPACKET` or `CXCONSUMER`	Parallel query coordination	Expensive parallel plans, skew between threads, row estimates, MAXDOP and cost threshold in context
`PAGELATCH_*`	In-memory latch contention	TempDB allocation, hot pages, sequential-key inserts, allocation design
`ASYNC_NETWORK_IO`	SQL Server waits for the client to consume rows	Large result sets, slow application processing, network behavior, row-by-row consumption
`HADR_*`	Availability Group transport or synchronization activity	Replica state, send and redo queues, network, secondary storage, commit mode

Wait names are not remediation commands. For example, PAGEIOLATCH can reflect slow storage, but it can also reflect a query reading far more pages than necessary. Replacing storage without examining the query may hide rather than solve the problem.

A worked interpretation pattern

Suppose PAGEIOLATCH_SH represents 42 percent of the delta, the average wait is elevated, and application latency increased during the same five-minute interval.

The next investigation should connect three layers:

Storage: review per-file read latency and operating-system storage telemetry.
Workload: identify queries with the most physical and logical reads in the interval.
Plan: inspect whether scans, poor estimates, spills, or missing access paths caused unnecessary I/O.

If storage latency is normal but one query suddenly reads millions of pages, tune the workload. If query volume is normal but file latency rose sharply, investigate the storage path. If both changed, preserve the timeline and determine which event happened first.

The same pattern applies to locking. LCK_M_* sends you to the blocking chain, transaction scope, and access order. The fix belongs to the cause, not to the label.

What not to do

Do not tune from cumulative lifetime totals without checking server start time.
Do not treat benign idle waits as workload problems.
Do not clear wait statistics merely to make the list easier to read.
Do not change MAXDOP, memory, indexes, or storage from one wait row.
Do not compare two servers without accounting for workload volume and observation duration.
Do not add percentages from samples collected with different filters.

Completion criteria

A wait-stat investigation is complete when the incident window is clear, the leading wait category correlates with another independent signal, the responsible workload or resource has been identified, and a controlled change improves the measured outcome.

After remediation, repeat the same sample method. Compare user latency, throughput, wait deltas, query metrics, and resource telemetry. A successful change reduces the observed problem without moving unacceptable pressure to another part of the system.