Home / Databases / SQL Server

DatabasesHow-toIntermediate

Optimization: tuning SQL Server from wait statistics - find the real bottleneck before adding hardware

Wait statistics show what SQL Server spends time waiting on, which points you to storage, CPU, memory, locking or log throughput. Snapshot waits over a busy period, filter out benign waits, map the top waits to causes and fixes, and re-measure after each change.

Deepak Bhardwaj25 Oct 20262 min read

Applies toSQL Server 2016 and later, Azure SQL Managed Instance, Azure SQL Database (sys.dm_db_wait_stats)

Snapshot waits over a busy period

Cumulative waits since restart hide recent behaviour. Take two snapshots and diff them:

IF OBJECT_ID('tempdb..#w1') IS NOT NULL DROP TABLE #w1;
SELECT wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count
INTO #w1 FROM sys.dm_os_wait_stats;

WAITFOR DELAY '00:15:00';   -- busy period

SELECT w2.wait_type,
       w2.wait_time_ms - w1.wait_time_ms AS wait_ms,
       w2.signal_wait_time_ms - w1.signal_wait_time_ms AS signal_ms,
       w2.waiting_tasks_count - w1.waiting_tasks_count AS tasks
FROM sys.dm_os_wait_stats w2
JOIN #w1 w1 ON w1.wait_type = w2.wait_type
WHERE w2.wait_time_ms - w1.wait_time_ms > 0
  AND w2.wait_type NOT IN ('SLEEP_TASK','LAZYWRITER_SLEEP','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
      'XE_TIMER_EVENT','XE_DISPATCHER_WAIT','REQUEST_FOR_DEADLOCK_SEARCH','LOGMGR_QUEUE',
      'CHECKPOINT_QUEUE','BROKER_TO_FLUSH','BROKER_TASK_STOP','DIRTY_PAGE_POLL',
      'HADR_FILESTREAM_IOMGR_IOCOMPLETION','SP_SERVER_DIAGNOSTICS_SLEEP','WAITFOR',
      'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','QDS_ASYNC_QUEUE','SOS_WORK_DISPATCHER')
ORDER BY wait_ms DESC;

Community scripts (e.g. Paul Randal's wait stats script) keep a longer benign-wait list.

Map top waits to causes

Wait	Usually means	Look at
`PAGEIOLATCH_*`	Reading data pages from disk	Missing indexes, scans, memory size, storage latency
`WRITELOG`	Log flush latency	Log disk latency, many tiny commits, AG sync commit
`LCK_M_*`	Blocking	Long transactions, isolation level, indexes on FK/filters (see the blocking post)
`CXPACKET`/`CXCONSUMER`	Parallelism	MAXDOP/cost threshold, skewed plans (see the MAXDOP post)
`SOS_SCHEDULER_YIELD` + high signal wait	CPU pressure	Top CPU queries in Query Store
`RESOURCE_SEMAPHORE`	Memory grant waits	Large sorts/hashes, bad estimates, memory size
`HADR_SYNC_COMMIT`	Synchronous AG replica latency	Network, secondary log disk
`PAGELATCH_*` on tempdb	tempdb contention	Data files, memory-optimized tempdb metadata (see tempdb post)
`ASYNC_NETWORK_IO`	Client not consuming results fast	Application fetching row by row, large result sets

Confirm with other evidence

-- File-level latency
SELECT DB_NAME(vfs.database_id) AS db, mf.physical_name,
       vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS avg_read_ms,
       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 mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY avg_read_ms DESC;

Combine with Query Store top queries by the resource the wait points to.

Fix and re-measure

Fix the top wait's cause (query, index, configuration, storage).
Snapshot again over a comparable period.
Expect the profile to shift; the next bottleneck becomes visible.

Tip
Save wait snapshots to a table every 15 minutes with an Agent job. A baseline turns "the server is slow today" into "WRITELOG doubled since 10:00".

Checklist

Waits snapshotted over a busy period
Benign waits filtered
Top waits mapped to causes
Confirmed with file latency and Query Store
Fix applied and re-measured
Baseline collection scheduled