Browse cheat sheets · SQL Server
SQL Server Wait Types
Use SQL Server wait statistics to identify where work is spending time, distinguish current waits from cumulative waits, and choose the next performance investigation.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Show top cumulative waits
- 2. Exclude common idle/background waits for troubleshooting
- 3. Show what is waiting right now
- 4. Join current waits to requests and SQL text
- 5. Show wait statistics for one session
- 6. Check SQL Server start time
- 7. Capture a baseline without clearing waits
- 8. Reset wait statistics only when you have a deliberate reason
- What the result means
- What to look for
- High signal wait time
- LCK_M_*
- PAGEIOLATCH_*
- PAGELATCH_*
- SOS_SCHEDULER_YIELD
- CXPACKET / CXCONSUMER
- WRITELOG
- ASYNC_NETWORK_IO
- RESOURCE_SEMAPHORE
- THREADPOOL
- HADR_*
- Production notes
- Wait stats show symptoms, not automatic root causes
- sys.dm_os_wait_stats shows completed waits
- Wait stats reset on restart or explicit clear
- Permissions vary by version
- Background waits vary by environment
- Wait totals need a time window
- Common mistakes
- 1. Treating the top wait as the root cause
- 2. Clearing wait stats before capturing them
- 3. Using a giant universal ignore list
- 4. Confusing PAGEIOLATCH_* and PAGELATCH_*
- 5. Assuming parallelism waits mean “set MAXDOP to 1”
- 6. Ignoring signal waits
- 7. Looking only at instance-wide waits
- 8. Comparing environments without normalizing workload and time
- Next step
- Related deep dives
Problem
SQL Server is slow, but CPU, reads, blocking, and query duration do not immediately explain why.
Wait statistics help answer a different question:
What is SQL Server spending time waiting for?
The goal is not to find one “bad” wait type and fix it blindly.
The goal is to:
- identify the dominant wait categories
- distinguish current waits from historical cumulative waits
- separate resource waits from queue/background waits
- understand whether the bottleneck is likely CPU, locking, I/O, memory, parallelism, logging, network, or something else
- choose the next diagnostic step
Quick answer
Use sys.dm_os_wait_stats for cumulative instance-level wait history.
SELECT TOP (30)
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms,
CAST(
(wait_time_ms - signal_wait_time_ms) / 1000.0
AS decimal(18,2)
) AS resource_wait_seconds,
CAST(
signal_wait_time_ms / 1000.0
AS decimal(18,2)
) AS signal_wait_seconds,
CAST(
100.0 * wait_time_ms
/ NULLIF(SUM(wait_time_ms) OVER (), 0)
AS decimal(6,2)
) AS pct_of_total_wait_time
FROM sys.dm_os_wait_stats
WHERE
wait_time_ms > 0
ORDER BY
wait_time_ms DESC;
Use this to find the dominant waits since the statistics were last reset or the SQL Server engine started.
Then use sys.dm_os_waiting_tasks when you need to know what is waiting right now.
Code / commands
1. Show top cumulative waits
SELECT TOP (30)
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
CAST(
wait_time_ms * 1.0
/ NULLIF(waiting_tasks_count, 0)
AS decimal(18,2)
) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE
wait_time_ms > 0
ORDER BY
wait_time_ms DESC;
Use this as a broad instance-level starting point.
Remember:
wait_time_msincludes signal wait timesignal_wait_time_msis the time after a worker is signaled but before it actually gets CPU- resource wait time is approximately:
wait_time_ms - signal_wait_time_ms
2. Exclude common idle/background waits for troubleshooting
Some waits are expected and can dominate totals without indicating a user-facing bottleneck.
Use a filtered view for practical troubleshooting.
SELECT TOP (30)
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE
wait_type NOT IN
(
N'BROKER_EVENTHANDLER',
N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP',
N'BROKER_TO_FLUSH',
N'CHECKPOINT_QUEUE',
N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT',
N'DBMIRROR_DBM_EVENT',
N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE',
N'DIRTY_PAGE_POLL',
N'DISPATCHER_QUEUE_SEMAPHORE',
N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'HADR_CLUSAPI_CALL',
N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT',
N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK',
N'HADR_WORK_QUEUE',
N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE',
N'ONDEMAND_TASK_QUEUE',
N'QDS_ASYNC_QUEUE',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH',
N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK',
N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP',
N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY',
N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED',
N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK',
N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP',
N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP',
N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'WAITFOR',
N'WAITFOR_TASKSHUTDOWN',
N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT',
N'XE_TIMER_EVENT'
)
AND wait_time_ms > 0
ORDER BY
wait_time_ms DESC;
Do not treat any “ignore list” as universal.
The right exclusions depend on:
- SQL Server version
- enabled features
- Availability Groups
- Service Broker
- Query Store
- replication
- workload type
Use filtering to reduce noise, not to hide unfamiliar waits.
3. Show what is waiting right now
sys.dm_os_wait_stats is cumulative history.
For current waits, use:
SELECT
wt.session_id,
wt.exec_context_id,
wt.wait_duration_ms,
wt.wait_type,
wt.blocking_session_id,
wt.resource_description
FROM sys.dm_os_waiting_tasks AS wt
ORDER BY
wt.wait_duration_ms DESC;
This is the right query when the performance issue is happening right now.
4. Join current waits to requests and SQL text
SELECT
wt.session_id,
wt.wait_duration_ms,
wt.wait_type,
wt.blocking_session_id,
wt.resource_description,
r.status,
r.command,
DB_NAME(r.database_id) AS database_name,
r.cpu_time,
r.logical_reads,
txt.text AS batch_text
FROM sys.dm_os_waiting_tasks AS wt
LEFT JOIN sys.dm_exec_requests AS r
ON r.session_id = wt.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
ORDER BY
wt.wait_duration_ms DESC;
This turns a wait type into something actionable by showing:
- which session is waiting
- what SQL it is running
- whether another session is blocking it
- which database is involved
5. Show wait statistics for one session
For session-scoped history:
DECLARE @session_id int = 57;
SELECT
session_id,
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_exec_session_wait_stats
WHERE
session_id = @session_id
ORDER BY
wait_time_ms DESC;
This is useful when you are investigating one known session and want to avoid instance-wide noise.
Session wait statistics reset when the session is created or reset, including connection-pool reset behavior.
6. Check SQL Server start time
Wait statistics are cumulative only since the last reset/start.
SELECT
sqlserver_start_time
FROM sys.dm_os_sys_info;
Always pair wait statistics with the observation window.
A 10-hour wait total over 30 days means something very different from 10 hours over 20 minutes.
7. Capture a baseline without clearing waits
The safest production pattern is to snapshot wait stats and compare deltas.
Example first snapshot:
SELECT
SYSDATETIMEOFFSET() AS captured_at,
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats;
Persist that output to a monitoring table or external telemetry store.
Then compare two snapshots:
delta_wait_time_ms
= current.wait_time_ms - previous.wait_time_ms
delta_wait_count
= current.waiting_tasks_count - previous.waiting_tasks_count
Delta analysis is much more useful than relying on lifetime cumulative totals.
8. Reset wait statistics only when you have a deliberate reason
SQL Server supports:
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
Do not use this casually on production systems.
Clearing wait stats removes the existing cumulative baseline for everyone troubleshooting the instance.
Prefer snapshot-and-delta monitoring.
What the result means
| Field | Why it matters |
|---|---|
wait_type |
Name of the resource/event SQL Server workers waited on. |
waiting_tasks_count |
Number of waits recorded for that wait type. |
wait_time_ms |
Total wait time, including signal wait time. |
signal_wait_time_ms |
Time spent runnable after being signaled but before getting CPU. |
resource_wait_time_ms |
Approximation of non-signal wait time: total minus signal. |
avg_wait_ms |
Rough average wait duration. Useful for context, not as a standalone verdict. |
blocking_session_id |
Session blocking the waiting task, where applicable. |
resource_description |
Additional detail about the resource being waited on. |
What to look for
High signal wait time
High signal waits can indicate CPU scheduler pressure.
Possible next checks:
- server CPU utilization
- runnable task counts
sys.dm_os_schedulers- expensive active queries
- concurrency
- parallel workloads
Do not diagnose CPU pressure from signal waits alone. Correlate with actual CPU and scheduler evidence.
LCK_M_*
These are lock waits.
Next step:
- identify blocked sessions
- identify the head blocker
- inspect open transactions
- inspect transaction scope and access order
Do not treat a lock wait as an indexing problem by default.
PAGEIOLATCH_*
These waits indicate pages are waiting to be read from storage into memory.
Possible areas to investigate:
- storage latency
- buffer cache pressure
- large scans
- poor access paths
- insufficient useful indexing
- workload growth
Do not conclude “disk is slow” without correlating with file-level I/O latency and query behavior.
PAGELATCH_*
These are in-memory latch waits, not normal data-file I/O waits.
They can point to contention on internal memory structures/pages.
For example, tempdb allocation contention can show certain page-latch patterns.
Do not confuse PAGELATCH_* with PAGEIOLATCH_*.
SOS_SCHEDULER_YIELD
This indicates workers yielded the scheduler voluntarily after consuming CPU quantum.
It can be normal in CPU-active systems.
If it dominates during poor performance, investigate CPU-intensive queries and scheduler pressure.
CXPACKET / CXCONSUMER
These are parallelism-related waits.
They do not automatically mean MAXDOP is wrong.
Investigate:
- skewed parallel plans
- query shape
- cardinality estimates
- CPU pressure
- exchange operators
- workload concurrency
Modern SQL Server versions separate more benign consumer-side waits into CXCONSUMER, which can make CXPACKET more actionable than in older versions.
WRITELOG
This means sessions are waiting for transaction log flushes.
Investigate:
- log storage latency
- transaction size
- commit frequency
- write-heavy workloads
- synchronous replication effects
- log throughput
ASYNC_NETWORK_IO
SQL Server is waiting for the client to consume result rows.
Possible causes:
- slow client processing
- large result sets
- network delay
- row-by-row application consumption
Do not immediately blame the network.
RESOURCE_SEMAPHORE
Queries are waiting for execution memory grants.
Investigate:
- large memory grants
- concurrent analytical queries
- bad cardinality estimates
- query-plan shape
- workspace memory pressure
THREADPOOL
SQL Server is waiting for worker threads.
This is a high-priority concurrency signal.
Possible contributors:
- severe blocking
- too many concurrent requests
- long-running queries
- exhausted worker pool
Do not “fix” threadpool waits by changing max worker threads without understanding workload pressure.
HADR_*
Availability Group waits require context.
Many HADR waits are expected background waits.
If availability/replication performance is the concern, analyze the specific HADR wait plus AG queue/redo/send metrics.
Production notes
Wait stats show symptoms, not automatic root causes
A wait type tells you where time is being spent.
It does not always tell you why.
Example:
PAGEIOLATCH_SH can be caused by:
- slow storage
- a massive scan
- memory pressure
- missing/inefficient index
- query regression
The correct fix depends on the surrounding evidence.
sys.dm_os_wait_stats shows completed waits
It is cumulative and does not represent only currently waiting work.
For current waiting tasks, use:
sys.dm_os_waiting_tasks
Wait stats reset on restart or explicit clear
sys.dm_os_wait_stats is not durable historical telemetry.
Track:
sys.dm_os_sys_info.sqlserver_start_time
and preferably snapshot wait stats over time.
Permissions vary by version
For SQL Server 2019 and earlier, broad wait-DMV access generally requires:
VIEW SERVER STATE
For SQL Server 2022 and later:
VIEW SERVER PERFORMANCE STATE
Azure SQL Database and other SQL platforms have different scope and permission rules.
Background waits vary by environment
An idle wait list from one server may be misleading on another.
Features such as:
- Availability Groups
- Service Broker
- Query Store
- replication
- external scripts
introduce waits that may be expected.
Wait totals need a time window
Never report:
“We have 3 hours of PAGEIOLATCH waits”
without also saying:
“over what period?”
Rate and delta matter more than lifetime totals.
Common mistakes
1. Treating the top wait as the root cause
The highest cumulative wait may simply reflect the longest-running characteristic of the instance.
Use:
- time-window deltas
- current waits
- query evidence
- server metrics
before deciding.
2. Clearing wait stats before capturing them
Once cleared, the baseline is gone.
Capture first.
3. Using a giant universal ignore list
Wait behavior depends on version and features.
Filter obvious background noise, but understand what you remove.
4. Confusing PAGEIOLATCH_* and PAGELATCH_*
They represent different classes of problems.
PAGEIOLATCH_*→ waiting for page I/OPAGELATCH_*→ in-memory latch contention
5. Assuming parallelism waits mean “set MAXDOP to 1”
Parallelism can be beneficial.
Investigate the actual parallel plans, CPU pressure, and skew before changing server-level configuration.
6. Ignoring signal waits
Signal wait time can help reveal CPU scheduling pressure.
But it must be correlated with scheduler and CPU data.
7. Looking only at instance-wide waits
One noisy workload can dominate instance totals.
Use:
- session waits
- current waiting tasks
- Query Store
- request-level diagnostics
to narrow scope.
8. Comparing environments without normalizing workload and time
Wait totals from production and test are not directly comparable when:
- workload volume differs
- uptime differs
- hardware differs
- features differ
Compare rates and context, not raw totals.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.