Understanding SQL Server Wait Statistics

Wait statistics are one of the most powerful tools for diagnosing SQL Server performance issues. When SQL Server cannot immediately fulfill a request, it records what it's waiting for. Understanding these waits is key to solving performance problems.

How Wait Statistics Work

Every time a SQL Server worker thread cannot continue processing, it enters a wait state. SQL Server tracks:

• Wait type - What resource is being waited for
• Wait time - How long the wait lasted
• Signal wait time - Time spent waiting for CPU after resource became available

The Wait Statistics Formula

Total wait time consists of two components:

Total Wait Time = Resource Wait Time + Signal Wait Time

• Resource Wait: Waiting for the actual resource (lock, I/O, memory)
• Signal Wait: Waiting for CPU time after the resource is available

High signal waits indicate CPU pressure, regardless of the wait type.

Common Wait Types Explained

PAGEIOLATCH Waits

These waits occur when SQL Server needs to read data from disk into memory.

-- Find tables causing the most I/O waits
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    OBJECT_NAME(object_id, database_id) AS ObjectName,
    index_id,
    page_io_latch_wait_count,
    page_io_latch_wait_in_ms,
    page_io_latch_wait_in_ms / NULLIF(page_io_latch_wait_count, 0) AS avg_wait_ms
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
WHERE page_io_latch_wait_count > 0
ORDER BY page_io_latch_wait_in_ms DESC;

Solutions:

• Add more memory to reduce disk reads
• Optimize indexes to reduce I/O
• Upgrade storage to faster disks or SSDs

CXPACKET and CXCONSUMER Waits

Parallelism waits that occur during parallel query execution.

Not always bad! Some CXPACKET waits are normal for parallel queries. Investigate if:

• Average wait time is high
• Combined with high CPU usage
• Queries are running slower than expected

LCK_M_* Waits

Lock waits indicate blocking between sessions.

-- Find current blocking chains
SELECT 
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocked.wait_type,
    blocked.wait_time / 1000.0 AS wait_seconds,
    blocking_sql.text AS blocking_query,
    blocked_sql.text AS blocked_query
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocking 
    ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_sql
CROSS APPLY sys.dm_exec_sql_text(blocking.context_info) blocking_sql
WHERE blocked.blocking_session_id > 0;

ASYNC_NETWORK_IO Waits

These occur when SQL Server sends data faster than the client can consume it.

Common causes:

• Application processing rows one at a time
• Network latency between SQL Server and client
• Client-side cursor processing

Analyzing Wait Statistics

Clearing Wait Statistics

Sometimes you need a fresh start:

-- Clear wait statistics (requires ALTER SERVER STATE permission)
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

Calculating Wait Percentages

WITH Waits AS (
    SELECT 
        wait_type,
        wait_time_ms / 1000.0 AS wait_time_s,
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
        'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH',
        'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE',
        'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
        'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT',
        'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
        'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'
    )
    AND wait_time_ms > 0
)
SELECT 
    wait_type,
    CAST(wait_time_s AS DECIMAL(12,2)) AS wait_time_s,
    CAST(pct AS DECIMAL(5,2)) AS pct,
    CAST(SUM(pct) OVER(ORDER BY rn) AS DECIMAL(5,2)) AS running_pct
FROM Waits
WHERE rn <= 20;

Best Practices

1. Establish baselines - Know your normal wait patterns
2. Focus on percentages - Not absolute numbers
3. Consider workload type - OLTP vs OLAP have different patterns
4. Look at trends - Single point-in-time data can be misleading
5. Use Query Store - Correlate waits with specific queries

Conclusion

Wait statistics provide invaluable insight into SQL Server performance. By understanding what your server is waiting for, you can make targeted improvements that have real impact.