SQL Server Wait Types Cheat Sheet for Performance Tuning

Monitoring wait stats is a key part of SQL Server performance tuning. This cheat sheet summarizes common wait types, their causes, and recommended actions.

1️⃣ CPU and Parallelism Waits

Wait Type	Description	Common Causes	Tuning Tips
SOS_SCHEDULER_YIELD	Query yielded CPU to allow others to run	High CPU usage, poor query plans	Optimize queries, indexes, and consider scaling CPU
CXPACKET	Waiting for parallel threads to finish	Parallel query imbalance	Adjust MAXDOP, update statistics, tune parallel plans
CXCONSUMER	Worker threads consuming parallel streams	Normal after SQL 2017 for CXPACKET	Investigate only if excessive

2️⃣ I/O Waits (Disk Bottlenecks)

Wait Type	Description	Common Causes	Tuning Tips
PAGEIOLATCH_*	Waiting for a page to be read from disk	Slow I/O, missing indexes	Add proper indexes, tune queries, move to faster storage (SSD)
WRITELOG	Waiting for log to flush to disk	Large transactions, slow log disk	Use fast storage for log, commit in smaller batches
ASYNC_IO_COMPLETION	Waiting for async I/O completion	Backup/restore, I/O saturation	Monitor backup jobs, consider faster disks

3️⃣ Memory and Resource Waits

Wait Type	Description	Common Causes	Tuning Tips
RESOURCE_SEMAPHORE	Waiting for query memory grant	Memory pressure, large sorts/joins	Add RAM, reduce query memory needs, optimize joins
MEMORY_ALLOCATION_EXT	Waiting for memory allocation	Fragmented memory or low memory	Monitor memory usage, optimize workloads
PAGE_LATCH_*	Waiting for access to in-memory page latch	Contention on heavily updated pages	Use partitioning, optimize inserts/updates

4️⃣ Locking and Blocking Waits

Wait Type	Description	Common Causes	Tuning Tips
LCK_M_S / LCK_M_X	Waiting for shared/exclusive lock	Blocking transactions	Reduce transaction scope, use RCSI (Read Committed Snapshot Isolation)
LATCH_*	Waiting for internal memory structure latch	Contention on tempdb or system pages	Use multiple tempdb files, reduce contention
ASYNC_NETWORK_IO	Waiting for client to read results	Network slowness, client-side processing	Optimize network, fetch only required columns

5️⃣ Other Common Waits

Wait Type	Description	Common Causes	Tuning Tips
NETWORK_IO	Waiting for network writes	Slow network or large result sets	Reduce dataset size, optimize queries
BACKUPIO / BACKUPBUFFER	Waiting during backup operations	Backup throttling or disk I/O	Use striped backups, faster disk or backup to Azure
PREEMPTIVE_OS_WAIT	Waiting for OS-level operations	External resources or file I/O	Check antivirus, OS I/O, or external calls

🛠 How to Monitor Wait Stats

Check Cumulative Waits

SELECT TOP 20 wait_type, waiting_tasks_count, wait_time_ms/1000 AS WaitSec
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK','SLEEP_SYSTEMTASK','BROKER_EVENTHANDLER')
ORDER BY wait_time_ms DESC;

Check Active Requests

SELECT session_id, wait_type, blocking_session_id, wait_time, last_wait_type
FROM sys.dm_exec_requests
WHERE status = 'suspended';

✅ Best Practices for Reducing Waits

1. Tune queries and indexes to reduce I/O and CPU waits
2. Monitor blocking and deadlocks using sys.dm_exec_requests and Extended Events
3. Check tempdb usage and configure multiple data files for concurrency
4. Use Query Store to analyze regressions and high-wait queries
5. Baseline wait stats to identify abnormal spikes

This cheat sheet is ideal for DBAs and developers to quickly diagnose SQL Server performance issues.