Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: PerformanceDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Show top cumulative waits
  5. 2. Exclude common idle/background waits for troubleshooting
  6. 3. Show what is waiting right now
  7. 4. Join current waits to requests and SQL text
  8. 5. Show wait statistics for one session
  9. 6. Check SQL Server start time
  10. 7. Capture a baseline without clearing waits
  11. 8. Reset wait statistics only when you have a deliberate reason
  12. What the result means
  13. What to look for
  14. High signal wait time
  15. LCK_M_*
  16. PAGEIOLATCH_*
  17. PAGELATCH_*
  18. SOS_SCHEDULER_YIELD
  19. CXPACKET / CXCONSUMER
  20. WRITELOG
  21. ASYNC_NETWORK_IO
  22. RESOURCE_SEMAPHORE
  23. THREADPOOL
  24. HADR_*
  25. Production notes
  26. Wait stats show symptoms, not automatic root causes
  27. sys.dm_os_wait_stats shows completed waits
  28. Wait stats reset on restart or explicit clear
  29. Permissions vary by version
  30. Background waits vary by environment
  31. Wait totals need a time window
  32. Common mistakes
  33. 1. Treating the top wait as the root cause
  34. 2. Clearing wait stats before capturing them
  35. 3. Using a giant universal ignore list
  36. 4. Confusing PAGEIOLATCH_* and PAGELATCH_*
  37. 5. Assuming parallelism waits mean “set MAXDOP to 1”
  38. 6. Ignoring signal waits
  39. 7. Looking only at instance-wide waits
  40. 8. Comparing environments without normalizing workload and time
  41. Next step
  42. 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_ms includes signal wait time
  • signal_wait_time_ms is 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/O
  • PAGELATCH_* → 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.

Next step

Use the dominant wait pattern to choose the next diagnostic.

  • LCK_M_* → inspect blocking, head blockers, and open transactions.
  • High signal wait time / SOS_SCHEDULER_YIELD → inspect CPU and scheduler pressure.
  • PAGEIOLATCH_* → inspect file I/O latency, memory pressure, scans, and query access paths.
  • PAGELATCH_* → inspect in-memory latch contention, including tempdb patterns where relevant.
  • WRITELOG → inspect transaction log latency, transaction size, and commit patterns.
  • ASYNC_NETWORK_IO → inspect result-set size, client consumption, and network behavior.
  • RESOURCE_SEMAPHORE → inspect memory grants and query cardinality.
  • THREADPOOL → inspect concurrency, blocking, and worker exhaustion urgently.
  • Parallelism waits → inspect plan skew, CPU pressure, and workload before changing MAXDOP.
  • Nothing clearly abnormal → compare wait deltas over a known time window and continue with Query Store and active-request analysis.

← All cheat sheets