sys.dm_os_wait_stats - Usage & Examples

The SQL Server scheduler operates on a cooperative multitasking model where threads voluntarily yield control when waiting for resources. When a thread cannot proceed (waiting for disk I/O, lock release, memory allocation, or network response), it enters a wait state tracked by the OS layer's wait statistics collector.

Each wait type represents a specific resource or condition. The scheduler moves threads between runnable, running, and suspended queues. When suspended, threads register wait statistics that accumulate in sys.dm_os_wait_stats until instance restart or manual reset via DBCC SQLPERF.

SQL Server 2016 introduced query store integration and improved wait classification. SQL Server 2017 added Linux support with modified wait types for cross-platform compatibility. SQL Server 2019 enhanced wait stats with intelligent query processing waits. SQL Server 2022 introduced parameter sensitive plan optimization waits and improved memory grant feedback tracking.

The buffer pool manager, lock manager, and transaction log writer generate the majority of production wait statistics. Resource semaphores control memory grant waits (RESOURCE_SEMAPHORE), while the buffer pool generates PAGEIOLATCH waits during physical I/O operations. Lock manager waits (LCK_M_*) indicate blocking and deadlock scenarios.

Internal waits like BROKER_* (Service Broker), SQLTRACE_*, and HADR_* (Always On) often dominate wait statistics but represent background operations rather than user workload bottlenecks. These benign waits frequently mislead inexperienced DBAs focusing on total wait time rather than user-impacting waits.

-- Identify top user-impacting waits excluding benign background waits
SELECT TOP 20 
    wait_type,
    wait_time_ms / 1000.0 AS wait_time_seconds,
    waiting_tasks_count,
    wait_time_ms / waiting_tasks_count AS avg_wait_time_ms,
    (wait_time_ms - signal_wait_time_ms) AS resource_wait_ms,
    signal_wait_time_ms,
    wait_time_ms / SUM(wait_time_ms) OVER() * 100 AS percentage
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'BROKER_EVENTHANDLER','BROKER_RECEIVE_WAITFOR','BROKER_TASK_STOP',
    'BROKER_TO_FLUSH','BROKER_TRANSMITTER','CHECKPOINT_QUEUE',
    'CHKPT','CLR_AUTO_EVENT','CLR_MANUAL_EVENT','CLR_SEMAPHORE',
    'DISPATCHER_QUEUE_SEMAPHORE','EXECSYNC','HADR_CLUSAPI_CALL',
    'HADR_FILESTREAM_IOMGR_IOCOMPLETION','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_XE_GETTARGETSTATE',
    'PWAIT_ALL_COMPONENTS_INITIALIZED','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',
    'WAIT_XTP_HOST_WAIT','WAIT_XTP_OFFLINE_CKPT_NEW_LOG','WAIT_XTP_CKPT_CLOSE',
    'XE_DISPATCHER_JOIN','XE_DISPATCHER_WAIT','XE_LIVE_TARGET_TVF','XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

-- Analyze wait patterns over time periods using percentage changes
WITH wait_stats AS (
    SELECT 
        wait_type,
        wait_time_ms,
        waiting_tasks_count,
        ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) as rn
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT LIKE 'SLEEP_%' 
        AND wait_type NOT LIKE 'BROKER_%'
        AND wait_type NOT LIKE 'SQLTRACE_%'
)
SELECT 
    wait_type,
    wait_time_ms / 1000.0 AS wait_seconds,
    waiting_tasks_count,
    wait_time_ms / waiting_tasks_count AS avg_wait_ms,
    CASE 
        WHEN wait_type LIKE 'PAGEIOLATCH%' THEN 'I/O Bottleneck'
        WHEN wait_type LIKE 'LCK_M%' THEN 'Blocking/Locking'
        WHEN wait_type LIKE 'RESOURCE_SEMAPHORE%' THEN 'Memory Pressure'
        WHEN wait_type IN ('SOS_SCHEDULER_YIELD','THREADPOOL') THEN 'CPU Pressure'
        WHEN wait_type LIKE 'WRITELOG%' THEN 'Log I/O Bottleneck'
        ELSE 'Other'
    END AS bottleneck_category
FROM wait_stats
WHERE rn <= 15;

-- Identify specific I/O wait patterns for storage subsystem analysis
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_ms,
    CASE wait_type
        WHEN 'PAGEIOLATCH_SH' THEN 'Shared page read from disk'
        WHEN 'PAGEIOLATCH_EX' THEN 'Exclusive page modification'
        WHEN 'WRITELOG' THEN 'Transaction log write'
        WHEN 'LOGMGR_FLUSH' THEN 'Log manager flush'
        WHEN 'ASYNC_IO_COMPLETION' THEN 'Asynchronous I/O completion'
        WHEN 'IO_COMPLETION' THEN 'Synchronous I/O completion'
        ELSE 'Other I/O operation'
    END AS io_description
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
    'PAGEIOLATCH_SH','PAGEIOLATCH_EX','PAGEIOLATCH_UP',
    'WRITELOG','LOGMGR_FLUSH','ASYNC_IO_COMPLETION','IO_COMPLETION'
)
    AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

-- Calculate wait statistics delta since specific timestamp for trending
SELECT 
    wait_type,
    waiting_tasks_count AS current_waits,
    wait_time_ms / 1000.0 AS current_wait_seconds,
    GETDATE() AS snapshot_time,
    DATEDIFF(minute, sqlserver_start_time, GETDATE()) AS uptime_minutes
FROM sys.dm_os_wait_stats 
CROSS JOIN sys.dm_os_sys_info
WHERE wait_time_ms > 1000
    AND wait_type NOT LIKE 'BROKER_%'
    AND wait_type NOT LIKE 'SLEEP_%'
ORDER BY wait_time_ms DESC;

Reset wait statistics for baseline measurement

-- Clear existing wait statistics to establish new baseline
-- WARNING: This removes historical wait data, run during maintenance window
-- Document current timestamp and wait stats before executing
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Verify reset completed successfully
SELECT COUNT(*) as remaining_wait_types,
       MAX(wait_time_ms) as max_wait_time,
       GETDATE() as reset_timestamp
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0;

Create wait stats monitoring table for historical tracking

-- Create permanent table to track wait statistics over time
-- Enables trend analysis and change detection
CREATE TABLE dbo.WaitStatsHistory (
    CaptureDate datetime2(3) DEFAULT SYSDATETIME(),
    wait_type nvarchar(60) NOT NULL,
    waiting_tasks_count bigint,
    wait_time_ms bigint,
    max_wait_time_ms bigint,
    signal_wait_time_ms bigint
);

-- Create clustered index for efficient querying
CREATE CLUSTERED INDEX IX_WaitStatsHistory_Date 
ON dbo.WaitStatsHistory (CaptureDate, wait_type);

-- Insert current baseline snapshot
INSERT INTO dbo.WaitStatsHistory (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms)
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_time_ms > 0;

Automated wait stats collection procedure

-- Create procedure for scheduled wait statistics collection
-- Run every 15-30 minutes via SQL Agent for trending analysis
CREATE PROCEDURE dbo.sp_CollectWaitStats
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Only collect significant waits to reduce storage overhead
    INSERT INTO dbo.WaitStatsHistory (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms)
    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_time_ms > 1000  -- Filter low-impact waits
        AND wait_type NOT IN (
            SELECT wait_type FROM dbo.WaitStatsHistory 
            WHERE CaptureDate = (SELECT MAX(CaptureDate) FROM dbo.WaitStatsHistory)
                AND wait_time_ms = sys.dm_os_wait_stats.wait_time_ms
        );
        
    -- Cleanup old data (retain 90 days)
    DELETE FROM dbo.WaitStatsHistory 
    WHERE CaptureDate < DATEADD(day, -90, SYSDATETIME());
END;

Wait statistics analysis with actionable recommendations

-- Generate performance recommendations based on wait patterns
-- Provides specific guidance for addressing identified bottlenecks
WITH TopWaits AS (
    SELECT TOP 10
        wait_type,
        wait_time_ms,
        waiting_tasks_count,
        wait_time_ms / waiting_tasks_count AS avg_wait_ms,
        wait_time_ms / SUM(wait_time_ms) OVER() * 100 AS pct_total
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT LIKE 'BROKER_%' 
        AND wait_type NOT LIKE 'SLEEP_%'
        AND waiting_tasks_count > 0
    ORDER BY wait_time_ms DESC
)
SELECT 
    wait_type,
    CAST(wait_time_ms / 1000.0 AS DECIMAL(12,2)) AS wait_seconds,
    waiting_tasks_count,
    CAST(avg_wait_ms AS DECIMAL(8,2)) AS avg_wait_ms,
    CAST(pct_total AS DECIMAL(5,2)) AS pct_total,
    CASE wait_type
        WHEN 'PAGEIOLATCH_SH' THEN 'Add memory or optimize storage I/O subsystem'
        WHEN 'PAGEIOLATCH_EX' THEN 'Check for I/O bottleneck, consider faster storage'
        WHEN 'LCK_M_S' THEN 'Investigate blocking queries and indexing strategy'
        WHEN 'LCK_M_X' THEN 'Optimize transactions, check for long-running queries'
        WHEN 'RESOURCE_SEMAPHORE' THEN 'Add memory or optimize memory-intensive queries'
        WHEN 'SOS_SCHEDULER_YIELD' THEN 'CPU pressure, check for missing indexes or optimize queries'
        WHEN 'WRITELOG' THEN 'Optimize transaction log I/O, consider log file placement'
        ELSE 'Research specific wait type for targeted optimization'
    END AS recommendation
FROM TopWaits
ORDER BY wait_time_ms DESC;

Implement continuous wait statistics monitoring through SQL Agent jobs collecting snapshots every 15 minutes. Store historical data for trend analysis and establish baseline thresholds for automated alerting when wait times exceed normal patterns by 200% or more.

Configure proper max degree of parallelism (MAXDOP) based on NUMA topology and cost threshold for parallelism settings to prevent excessive SOS_SCHEDULER_YIELD waits. Set MAXDOP to number of cores per NUMA node, typically 4-8 for modern servers. Adjust cost threshold from default 5 to 25-50 for OLTP workloads.

Optimize storage configuration to minimize PAGEIOLATCH waits through proper RAID levels, sufficient memory allocation, and separate log file placement on dedicated fast storage. Enable instant file initialization and configure appropriate autogrowth settings to prevent PREEMPTIVE_OS_WRITEFILEGATHER waits.

Establish query performance baseline using Query Store and identify queries contributing to lock waits through extended events or Query Store runtime statistics. Implement proper indexing strategies and consider read committed snapshot isolation for high-concurrency OLTP environments to reduce blocking waits.

Monitor memory pressure indicators like RESOURCE_SEMAPHORE waits and configure appropriate max server memory settings leaving 4-6GB for OS operations. Enable lock pages in memory privilege for dedicated SQL Server instances to prevent memory paging and associated wait types.

Set up automated wait statistics analysis comparing current patterns against historical baselines. Create alerts for sudden increases in critical wait types like THREADPOOL (indicating CPU starvation) or WRITELOG (transaction log bottlenecks) that require immediate attention.