SQL Server Performance Tuning Using Wait Statistics

---

Wait statistics are the starting point for every SQL Server performance investigation. When a query cannot execute immediately because it is waiting for a lock, disk I/O, memory, or CPU time, SQL Server records that wait and accumulates the total time spent. Reading and interpreting those accumulated waits tells a DBA which problem category to investigate first, without guesswork.

This article explains what wait statistics are, how the accumulation works, how to read them correctly, and what to do when each major wait type appears at the top of the list.

1 What Wait Statistics Are and How They Accumulate Beginner

SQL Server does not execute every query the moment it arrives. A query may need to wait for a lock held by another transaction, for a data page to be read from disk into memory, for a CPU scheduler slot, or for a memory grant. Each time a query waits, SQL Server records the wait type and the time spent waiting.

These records accumulate in an internal structure that is exposed through the DMV sys.dm_os_wait_stats. The accumulation is cumulative: numbers grow continuously from the last time SQL Server was restarted or the stats were manually cleared. A wait type showing 500,000 milliseconds of total wait time on a server that has been running for three months tells a different story than the same number on a server that restarted two hours ago.

This is why the correct way to use wait statistics during an incident is to capture a snapshot, wait one to five minutes while the problem is reproducing, capture a second snapshot, and compare the delta. The delta isolates what is happening right now rather than everything that has ever happened since the last restart. The triage playbook covers the delta snapshot approach in detail.

2 The Right DMV: sys.dm_os_wait_stats Beginner

Wait statistics come from sys.dm_os_wait_stats, not from sys.dm_exec_requests or sys.dm_exec_sessions. That distinction matters because sys.dm_exec_requests shows only actively running requests at the moment the query executes, while sys.dm_os_wait_stats accumulates all waits across the entire server lifetime since the last restart.

-- Correct: read accumulated wait statistics from sys.dm_os_wait_stats
-- Excludes benign background waits that add noise without diagnostic value
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms          AS resource_wait_ms,
    ROUND(wait_time_ms * 100.0
        / SUM(wait_time_ms) OVER(), 2)          AS pct_of_total
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK', 'WAITFOR', 'LAZYWRITER_SLEEP', 'SLEEP_DBSTARTUP',
    'SLEEP_DBTASK', 'SLEEP_TEMPDBSTARTUP', 'SLEEP_SYSTEMTASK',
    'DISPATCHER_QUEUE_SEMAPHORE', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP',
    'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'DBMIRROR_EVENTS_QUEUE',
    'SQLTRACE_BUFFER_FLUSH', 'REQUEST_FOR_DEADLOCK_SEARCH',
    'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'ONDEMAND_TASK_QUEUE',
    'CHECKPOINT_QUEUE', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'RESOURCE_QUEUE',
    'SERVER_IDLE_CHECK', 'HADR_WORK_QUEUE', 'SLEEP_TASK'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

3 How to Read the Numbers Correctly Beginner

The raw numbers from sys.dm_os_wait_stats are cumulative totals since the last restart. Three rules prevent misinterpretation.

Rule 1: Look at percentage of total, not absolute numbers. A wait type with 10,000,000 milliseconds of total wait time on a server that has been running for 90 days may represent a tiny fraction of overall activity. A wait type with 500,000 milliseconds on a server that restarted two hours ago may be dominating the workload. The pct_of_total column in the query above normalizes the comparison.

Rule 2: Focus on the top three to five waits. Every server has some level of every wait type. The wait types that matter are the ones dominating the top of the list by total time. If PAGEIOLATCH_SH is 45 percent of total waits and the next type is 12 percent, investigate PAGEIOLATCH_SH first.

Rule 3: Use deltas during active incidents. For a server under investigation right now, a five-minute delta snapshot is more actionable than the lifetime cumulative total. The cumulative total shows historical patterns. The delta shows what is happening in the current window.

4 Wait Type Reference: What Each Category Means Beginner

Category	Example Wait Types	What It Means	Investigation Path
I/O	PAGEIOLATCH_SH PAGEIOLATCH_EX WRITELOG	Queries waiting for data pages to be read from disk, or for log writes to complete	Check file I/O latency, look for missing indexes causing large scans, check log file autogrowth
Locking	LCK_M_X LCK_M_S LCK_M_U	Queries waiting for locks held by other transactions	Find the head blocker, investigate long-running transactions, consider RCSI
Parallelism	CXPACKET CXCONSUMER CXSYNC_PORT	Queries running in parallel with threads waiting on each other	Review MAXDOP setting, check cost threshold for parallelism, find skewed parallel plans
CPU scheduling	SOS_SCHEDULER_YIELD	A thread yielded the CPU voluntarily because it has been running too long	Find CPU-intensive queries, check for missing indexes, review parallelism settings
Memory	RESOURCE_SEMAPHORE RESOURCE_SEMAPHORE_QUERY_COMPILE	Queries waiting for a memory grant to execute sort or hash operations	Find queries with large memory grants, check for stale statistics causing overestimates
Latch	PAGELATCH_UP PAGELATCH_EX	Contention on SQL Server internal structures, often TempDB allocation pages	Check TempDB file count, look for hotspot tables, investigate identity column contention
Network	ASYNC_NETWORK_IO	SQL Server is ready to send results but the client is not consuming them fast enough	Usually an application-side issue: the client is processing rows slowly or the network is saturated

5 Common Wait Types and What to Do Intermediate

PAGEIOLATCH_SH / PAGEIOLATCH_EX

SQL Server is waiting for data pages to be loaded from disk into the buffer pool. This is the signature of I/O pressure. The two most common causes are storage that is too slow for the workload, and queries performing large table scans because indexes are missing or not being used. Check file I/O latency using sys.dm_io_virtual_file_stats to confirm whether the storage itself is slow. Then look at the top logical-read queries to find the scans driving the I/O.

CXPACKET / CXCONSUMER

These are parallelism coordination waits. Some CXPACKET on a server running analytical queries is normal. CXPACKET dominating the top of the wait list on an OLTP server, or a specific query that sometimes runs serially and sometimes in parallel, points to a cost threshold for parallelism setting that is too low or a query with skewed cardinality estimates. Review the MAXDOP setting and cost threshold. Use Query Store to identify queries that flip between serial and parallel execution.

LCK_M_X / LCK_M_S

Locking waits indicate that transactions are holding locks longer than necessary. The investigation starts by identifying the head blocker: the session holding locks that no other session is waiting on. From there, look at what transaction the blocker has open and why it has not committed. Common causes are long-running explicit transactions, missing indexes on write paths causing lock escalation, and applications that open transactions and then pause for user input before committing. Read Committed Snapshot Isolation (RCSI) eliminates reader-writer blocking on databases where it is appropriate.

SOS_SCHEDULER_YIELD

A worker thread ran long enough on a CPU core that it voluntarily yielded to allow other threads to run. High SOS_SCHEDULER_YIELD indicates CPU pressure: there are more runnable threads than available CPU cores. Find the CPU-intensive queries using sys.dm_exec_query_stats ordered by total_worker_time. These are the queries burning the most CPU, and they are typically the best candidates for index tuning or query rewriting.

WRITELOG

Every committed transaction must wait for its log records to be hardened to the transaction log before the commit returns. WRITELOG waits indicate that log write I/O is the bottleneck. Check log file latency using sys.dm_io_virtual_file_stats for the log file. Common causes are a log file on slow storage, frequent autogrowth events on the log file, and very high transaction rates. Pre-sizing the log file and placing it on fast dedicated storage are the standard fixes.

ASYNC_NETWORK_IO

SQL Server has finished producing results but the client application is not reading them fast enough. This is almost always an application-layer issue rather than a SQL Server issue. The application may be processing each row before requesting the next one, or the network between SQL Server and the application is saturated. Check the client application’s row consumption pattern and network throughput between the application and database server.

6 Performance Tuning Strategies by Wait Category Intermediate

Each dominant wait category points to a specific tuning area. The table below maps wait categories to the right first action.

Dominant Wait Category	First Action	Then
I/O (PAGEIOLATCH)	Check file I/O latency with sys.dm_io_virtual_file_stats	Find top logical read queries, add covering indexes to reduce scans
Locking (LCK_M_*)	Find the head blocker with sys.dm_exec_requests	Investigate long-running transactions, consider RCSI
Parallelism (CXPACKET)	Review MAXDOP and cost threshold for parallelism settings	Find queries flipping between serial and parallel in Query Store
CPU (SOS_SCHEDULER_YIELD)	Find top CPU queries with sys.dm_exec_query_stats	Add indexes to reduce scan CPU cost, rewrite inefficient queries
Memory (RESOURCE_SEMAPHORE)	Find queries with large memory grants	Update statistics, fix cardinality estimate errors driving overestimates
Latch (PAGELATCH on TempDB)	Check TempDB data file count vs CPU count	Add TempDB files in increments of 4 up to CPU count
Log I/O (WRITELOG)	Check log file latency and autogrowth events	Move log to faster dedicated storage, pre-size the log file

7 Making Wait Statistics Part of Regular Monitoring Beginner

Wait statistics are most valuable when they are reviewed regularly rather than only during incidents. A server where PAGEIOLATCH waits have been quietly growing for three weeks while staying below alert thresholds is showing a trend that will eventually become an incident. Capturing wait statistics on a schedule and comparing snapshots over time reveals these trends before they become problems.

-- Scheduled baseline capture: save top waits to a history table
-- Run this as a SQL Agent job daily or at the cadence that suits your environment
INSERT INTO YourDBADatabase.dbadmin.WaitStatsHistory
    (WaitType, WaitingTasksCount, TotalWaitMs, ResourceWaitMs, SignalWaitMs)
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    wait_time_ms - signal_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK', 'WAITFOR', 'LAZYWRITER_SLEEP', 'SLEEP_DBSTARTUP',
    'SLEEP_DBTASK', 'SLEEP_TEMPDBSTARTUP', 'SLEEP_SYSTEMTASK',
    'DISPATCHER_QUEUE_SEMAPHORE', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP',
    'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'DBMIRROR_EVENTS_QUEUE',
    'SQLTRACE_BUFFER_FLUSH', 'REQUEST_FOR_DEADLOCK_SEARCH',
    'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'ONDEMAND_TASK_QUEUE',
    'CHECKPOINT_QUEUE', 'SP_SERVER_DIAGNOSTICS_SLEEP'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

-- Review trend: is any wait type growing over the last 30 days?
SELECT
    WaitType,
    CAST(SnapshotTime AS DATE)              AS SnapshotDate,
    AVG(TotalWaitMs)                        AS AvgTotalWaitMs
FROM YourDBADatabase.dbadmin.WaitStatsHistory
WHERE SnapshotTime >= DATEADD(DAY, -30, GETDATE())
GROUP BY WaitType, CAST(SnapshotTime AS DATE)
ORDER BY WaitType, SnapshotDate;

References

• Microsoft Docs: sys.dm_os_wait_stats
• Microsoft Docs: sys.dm_exec_requests
• Paul Randal (SQLskills): Wait Statistics, or Please Tell Me Where It Hurts
• SQLYARD: When SQL Server Is Slow: Triage Playbook
• SQLYARD: SQL Server DMV Reference Cheat Sheet
• SQLYARD: SQL Server Performance Tuning Complete Guide
• SQLYARD: SQL Server MAXDOP Guide