SQL Optima guide

Reading wait stats without drowning in noise

A practical filter for SQL Server wait stats during incident triage.

2026-07-28

/*
===============================================================================
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 Reading wait stats without drowning in noise — Reading wait stats without drowning in noise

Start with waits that correlate to application timeouts, not every counter that moved.

Sort by cumulative wait time, exclude benign noise (like SLEEP_TASK), and pair the top waiters with a slow-query sample from the same window.

SQLOptima Monitoring surfaces this pairing so DBAs can move from symptom to statement faster.

Why wait-stat analysis deserves a method

The central principle is that waits describe where sessions spend time, but interpretation requires a time window and workload context. A single metric, recommendation, or successful command is not enough. The decision has to connect an observed symptom to a plausible cause, a controlled change, and a result that can be verified under the workload that matters.

Evidence to collect

Use evidence from the same time window whenever possible:

delta waits over the incident period
signal wait versus resource wait
throughput and concurrency
queries and resources active in the same window

A practical workflow

Capture a baseline and a focused delta.
Group waits by resource category.
Correlate the leading category with other evidence.
Investigate the responsible workload before tuning.

Common mistakes

Avoid these shortcuts:

tuning from lifetime totals
treating benign idle waits as incidents
mapping one wait type directly to one fix

Verification and completion

Define completion before making the change. Confirm correctness first, then compare latency, throughput, reads, CPU, waits, storage behavior, or recovery time as appropriate for Reading wait stats without drowning in noise. Test negative and boundary cases, not only the path expected to succeed. Watch the system long enough to cover the relevant workload pattern.

Download and run the diagnostic

Top server waits

Download top_server_waits.sql

Before you run it

Run the script first on a non-production or read-only connection when possible.
Review the complete SQL file before execution and confirm that the connected instance is the intended target.
Required access: VIEW SERVER STATE.
Open SQL Server Management Studio or Azure Data Studio, connect to the instance, open the downloaded file, and select Execute. Do not run diagnostic scripts through an application login.
Save the result grid with the server name, collection time, and incident window. DMV output changes over time and often resets after a SQL Server restart.

What the script does

Filters common idle waits and returns the top 25 wait types with task count, total, resource and signal wait seconds, average wait, percentage, and a first-line recommendation. The download is intended for diagnosis and does not apply an automatic remediation. Read every statement anyway, especially when adapting the script for automation or a different SQL Server version.

How to read the output

Use PercentOfTotal to find dominant categories, AvgWaitMs to understand per-occurrence severity, and compare SignalWaitSeconds with ResourceWaitSeconds. Because the DMV is cumulative since restart or reset, run delta samples during the problem window for stronger evidence.