SQL Server Wait Statistics: sys.dm_os_wait_stats Guide (2026)

The SQL Server Wait Model

SQL Server uses a cooperative scheduling model built on top of the Windows thread pool. Each logical CPU gets a scheduler. Worker threads cycle through three states:

• Running — actively executing on a CPU core
• Runnable — ready to run but waiting for a CPU slot (signal wait)
• Suspended — waiting for a resource: disk I/O, lock, latch, memory grant (resource wait)

Total wait time = resource wait (suspended) + signal wait (runnable but not yet scheduled). When signal waits are a large fraction of total waits, you have CPU pressure — more work than CPUs. When resource waits dominate, you have an I/O, locking, or memory bottleneck.

Reading sys.dm_os_wait_stats

The DMV has one row per wait type with four key columns:

SELECT wait_type, waiting_tasks_count, wait_time_ms,
       max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'PAGEIOLATCH_SH';

• waiting_tasks_count — total number of waits recorded for this type
• wait_time_ms — total cumulative wait time in milliseconds
• max_wait_time_ms — single longest individual wait (useful for outlier detection)
• signal_wait_time_ms — portion of wait_time_ms that was signal (CPU queue) wait
• resource_wait_time_ms = wait_time_ms − signal_wait_time_ms (derived)

Average wait per task = wait_time_ms / waiting_tasks_count. This is the most actionable metric — a type with 10 billion ms total wait across 10 billion tasks (1 ms average) is irrelevant. A type with 50 ms average wait on a high-frequency OLTP procedure is critical.

Top Waits Query with Signal vs Resource Split

This query shows the top 20 waits as a percentage of total wait time, excluding benign waits:

WITH waits AS (
    SELECT
        wait_type,
        wait_time_ms / 1000.0                                   AS wait_s,
        (wait_time_ms - signal_wait_time_ms) / 1000.0          AS resource_s,
        signal_wait_time_ms / 1000.0                            AS signal_s,
        waiting_tasks_count,
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER()        AS pct
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        -- Benign / idle waits — exclude from analysis
        'SLEEP_TASK','SLEEP_SYSTEMTASK','SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP',
        'SLEEP_MASTERDBREADY','SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED',
        'SLEEP_MSDBSTARTUP','SLEEP_TEMPDBSTARTUP','SLEEP_USERTASK',
        'WAITFOR','DISPATCHER_QUEUE_SEMAPHORE','BROKER_TO_FLUSH',
        'BROKER_TASK_STOP','CLR_AUTO_EVENT','CLR_MANUAL_EVENT',
        'DBMIRROR_EVENTS_QUEUE','SQLTRACE_BUFFER_FLUSH',
        'XE_TIMER_EVENT','XE_DISPATCHER_WAIT','FT_IFTS_SCHEDULER_IDLE_WAIT',
        'WAIT_XTP_OFFLINE_CKPT_NEW_LOG','HADR_WORK_QUEUE',
        'HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_TIMER_TASK',
        'SNI_HTTP_ACCEPT','REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE',
        'SERVER_IDLE_CHECK','SQL_TRACE_RECONFIGURE','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        'WAITFOR_TASKSHUTDOWN','WAIT_XTP_HOST_WAIT','ONDEMAND_TASK_QUEUE',
        'CHECKPOINT_QUEUE','BROKER_EVENTHANDLER','DBMIRROR_WORKER_QUEUE',
        'SQLTRACE_WAIT_ENTRIES','WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
    )
)
SELECT TOP 20
    wait_type,
    CAST(wait_s AS DECIMAL(10,2))          AS total_wait_s,
    CAST(resource_s AS DECIMAL(10,2))      AS resource_s,
    CAST(signal_s AS DECIMAL(10,2))        AS signal_s,
    waiting_tasks_count,
    CAST(pct AS DECIMAL(5,2))              AS pct_total,
    CAST(wait_s / NULLIF(waiting_tasks_count,0) * 1000 AS DECIMAL(10,2)) AS avg_wait_ms
FROM waits
ORDER BY pct_total DESC;

Per-Session Waits: sys.dm_exec_session_wait_stats

Added in SQL Server 2016, this DMV shows cumulative waits broken down by active session — letting you pinpoint which specific SPID is responsible for the waits you see:

-- Find the top-waiting active sessions right now
SELECT
    s.session_id,
    s.login_name,
    s.status,
    ws.wait_type,
    ws.wait_time_ms,
    ws.waiting_tasks_count,
    r.blocking_session_id,
    r.command,
    SUBSTRING(t.text, r.statement_start_offset/2+1,
        (CASE WHEN r.statement_end_offset = -1 THEN LEN(t.text)*2
              ELSE r.statement_end_offset END - r.statement_start_offset)/2+1) AS current_sql
FROM sys.dm_exec_session_wait_stats ws
JOIN sys.dm_exec_sessions s ON ws.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1
ORDER BY ws.wait_time_ms DESC;

Benign Waits to Exclude

Several high-volume wait types are normal idle or background activity and should be excluded from tuning analysis:

CXPACKET and CXCONSUMER: Parallelism Waits

CXPACKET accumulates when parallel query threads wait for each other to exchange rows through a parallelism operator. It is almost always the top wait on any server running analytical queries — but it is often a symptom, not a root cause.

In SQL Server 2016 SP2+ and 2017+, CXPACKET was split:

• CXPACKET — producer threads waiting (the slow thread is the problem)
• CXCONSUMER — consumer threads waiting for the producer (usually benign)

Focus on CXPACKET specifically. High CXPACKET with skewed parallelism (one thread doing most work) is a data skew problem. Solutions:

-- Reduce MAXDOP at query or workload level
SELECT ... OPTION (MAXDOP 4);

-- Server-level MAXDOP (Resource Governor or server config)
EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

-- Raise the Cost Threshold for Parallelism (default 5 is very low)
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

PAGEIOLATCH_SH / _EX: I/O Waits

A PAGEIOLATCH wait occurs when a thread requests a data page and it is not in the buffer pool — SQL Server must read it from disk. The thread waits on the latch protecting that in-flight I/O:

• PAGEIOLATCH_SH — shared latch — reading a page (most common)
• PAGEIOLATCH_EX — exclusive latch — writing a page (new allocation, e.g. TempDB)
• PAGEIOLATCH_UP — update latch — TempDB PFS/GAM/SGAM page contention (see TempDB guide)

High PAGEIOLATCH_SH means your working set does not fit in the buffer pool or your storage is slow. Resolution paths:

• Add RAM (cheapest fix — more buffer pool = fewer physical reads)
• Move hot tables to SSD or NVMe storage
• Add or fix missing indexes to reduce I/O per query
• Check for index fragmentation causing excessive sequential reads

-- Find which databases / files are generating the most I/O waits
SELECT DB_NAME(vfs.database_id)  AS db_name,
       mf.physical_name,
       vfs.io_stall_read_ms,
       vfs.num_of_reads,
       vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads,0) AS avg_read_ms,
       vfs.io_stall_write_ms,
       vfs.num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL,NULL) vfs
JOIN sys.master_files mf ON vfs.database_id = mf.database_id
                         AND vfs.file_id = mf.file_id
ORDER BY avg_read_ms DESC;

WRITELOG: Transaction Log Waits

WRITELOG occurs when a session waits for its log records to be hardened to disk (log flush). Every COMMIT on a synchronous transaction must wait for this. High WRITELOG indicates:

• Slow log disk I/O (log should be on fast sequential-write storage — NVMe or dedicated SSD)
• Very high transaction rate generating more log than the disk can flush
• Log file sharing contention (multiple databases on the same log disk)

-- Check log flush stats
SELECT DB_NAME(vfs.database_id)  AS db_name,
       vfs.io_stall_write_ms,
       vfs.num_of_writes,
       vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes,0) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) vfs
JOIN sys.master_files mf ON vfs.database_id = mf.database_id
                         AND vfs.file_id = mf.file_id
WHERE mf.type = 1  -- log files only
ORDER BY avg_write_ms DESC;

LCK_M_* Lock Waits

LCK_M_* waits occur when a session cannot acquire a lock because another session holds a conflicting lock. Common variants:

To find what is blocked and what is blocking right now:

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000.0            AS wait_s,
    r.wait_resource,
    s.login_name,
    SUBSTRING(t.text, r.statement_start_offset/2+1,
        (CASE WHEN r.statement_end_offset=-1 THEN LEN(t.text)*2
              ELSE r.statement_end_offset END - r.statement_start_offset)/2+1) AS sql_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

For persistent lock waits, the long-term solution is usually Read Committed Snapshot Isolation (RCSI), which gives readers a consistent snapshot without taking shared locks:

ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;

SOS_SCHEDULER_YIELD: CPU Pressure

SOS_SCHEDULER_YIELD occurs when a thread voluntarily yields the CPU scheduler after using its full quantum (4 ms), then waits to be rescheduled. High values indicate CPU saturation — there are more runnable threads than CPU cores to service them.

This wait is always a signal wait (no resource component). Solutions:

• Add CPU cores
• Reduce query CPU cost — fix missing indexes, simplify large aggregations
• Reduce MAXDOP — fewer parallel threads per query means more capacity for concurrent sessions
• Check for runaway queries consuming entire CPU cores

-- Find top CPU-consuming queries right now
SELECT TOP 10
    qs.total_worker_time / qs.execution_count   AS avg_cpu_us,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset/2+1,
        (CASE WHEN qs.statement_end_offset=-1 THEN LEN(qt.text)*2
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2+1) AS sql_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_cpu_us DESC;

RESOURCE_SEMAPHORE: Memory Grant Waits

RESOURCE_SEMAPHORE occurs when a query requests a memory grant (for sort or hash operations) and SQL Server cannot fulfill it because the workspace memory pool is exhausted. The query queues until enough memory is freed.

High RESOURCE_SEMAPHORE usually means:

• Queries requesting very large memory grants (bad cardinality estimates → over-allocation)
• Many concurrent queries each holding modest grants, collectively exhausting workspace memory
• Low max server memory setting leaving insufficient workspace

-- Check current memory grant queue
SELECT
    r.session_id,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.required_memory_kb,
    mg.queue_id,
    mg.wait_order,
    mg.is_next_candidate
FROM sys.dm_exec_query_memory_grants mg
JOIN sys.dm_exec_requests r ON mg.session_id = r.session_id
ORDER BY mg.requested_memory_kb DESC;

ASYNC_NETWORK_IO: Network / Client Waits

ASYNC_NETWORK_IO occurs when SQL Server has results ready to send but the client is not consuming them fast enough. The server thread sits idle waiting for the client to drain the network buffer.

This is usually a client-side problem:

• Application processes rows slowly (e.g., writing to disk for each row)
• Result set too large — client cannot consume it as fast as SQL produces it
• Network bandwidth saturated between server and application tier

Fix: reduce result set size (pagination, early filtering), use SET NOCOUNT ON to eliminate row-count messages, or move processing closer to the database.

PAGELATCH_UP: TempDB Contention

PAGELATCH_UP on TempDB PFS, GAM, or SGAM pages indicates allocation contention — too many sessions simultaneously allocating objects in TempDB. Covered in detail in the TempDB Internals guide.

-- Confirm TempDB allocation contention
SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'PAGELATCH_UP';

If PAGELATCH_UP dominates, add TempDB data files (one per 4 logical CPUs, up to 8) to spread allocation across PFS pages.

Wait-Based Tuning Workflow

A systematic wait-based approach prevents the common mistake of tuning random queries rather than the actual bottleneck:

1. Capture baseline — run the top-waits query, record results, wait 5–10 minutes under representative load, run again, compute delta
2. Identify the dominant wait — the wait type with the highest percentage of total wait time
3. Map to root cause — use the reference table below
4. Fix the root cause
5. Re-baseline — confirm the dominant wait decreased; the next wait type is now your new target

Read Next

• Blocking and Deadlocks: Detection, Diagnosis and Resolution
• TempDB Internals: Version Store, Spills and Optimization
• Parameter Sniffing: Diagnosis and Fixes
• Statistics: How They Work and Maintenance
• All SQL Server Articles