Portrait of Mihaly KerteszMihaly KerteszSQL Server consulting

sql server hub / waits guide

SQL Server waits guide

SQL Server wait stats analysis is useful when it narrows the next check. It is weak when a top wait becomes a one-line explanation for a workload nobody has actually read.

Use this guide when SQL Server performance issues need a cleaner path from cumulative wait stats to active waits, session waits, blocking context, query text, plans, job timing, and resource pressure.

sys.dm_os_wait_stats

sys.dm_os_waiting_tasks

session waits

Guide

Operational guide~11 min readUpdated 4 Jul 2026

Share

LinkedInXEmail

Use this when

The top waits do not explain the slowdown yet

1

Use waits to narrow the diagnosis, not to replace it.

2

Compare wait movement against the time users felt the slowdown.

3

Read active waits before changing settings.

4

Tie wait stats back to blocking, query text, plans, jobs, or resource pressure.

1 / Wait basics

What SQL Server waits are

A wait means a task could not continue until something else happened. That might be a lock, a read from storage, a log flush, a worker, memory, parallel worker coordination, or normal background engine work.

Waits are not a verdict. They are a pointer toward the next check. The useful question is whether the waits line up with the time window, workload, session, query text, plan shape, blocking chain, job timing, or resource pressure that users actually felt.

LayerWhat it answers
Instance wait statsWhat has accumulated since startup or the last wait-stat reset.
Wait deltasWhat changed during a specific time window.
Active waitsWhat requests are waiting on right now.
Session waitsWhich sessions collected wait time during their work.

2 / Instance totals

SQL Server wait stats analysis basics

Instance-level waits are a good first map, but they are cumulative. They can include hours, days, months, background waits, old incidents, maintenance windows, and quiet periods.

SQL Server wait stats analysis should separate wait duration from wait count. A few long waits can mean something different from many short waits, and a large signal wait can point toward scheduler pressure rather than storage, locking, or memory.

Start here to see the shape of the system. Do not stop here. The next checks should narrow the wait pattern by time, active sessions, query text, plans, blockers, jobs, and workload.

Instance-level wait stats

Reads the largest accumulated waits from sys.dm_os_wait_stats while excluding common background waits.

SELECT TOP (25)
    ws.wait_type,
    ws.waiting_tasks_count,
    ws.wait_time_ms,
    ws.wait_time_ms - ws.signal_wait_time_ms AS resource_wait_time_ms,
    ws.signal_wait_time_ms,
    CONVERT(decimal(10,2),
        100.0 * ws.wait_time_ms / NULLIF(SUM(ws.wait_time_ms) OVER (), 0)
    ) AS pct_of_total_wait_time
FROM sys.dm_os_wait_stats AS ws
WHERE ws.wait_time_ms > 0
  AND ws.wait_type NOT IN (
      N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
      N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
      N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
      N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT',
      N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE',
      N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
      N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
      N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY',
      N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
      N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
      N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
      N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
      N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT',
      N'XE_TIMER_EVENT'
  )
ORDER BY ws.wait_time_ms DESC;

wait_time_ms shows cumulative wait time since startup or the last reset. resource_wait_time_ms separates resource wait from scheduler signal wait. waiting_tasks_count helps distinguish a few long waits from many short waits. If one wait dominates, compare it over a time window before naming a cause.

3 / Time window

SQL Server wait stats analysis starts with a time window

A wait delta is often more useful than the all-time list. It shows what moved during the period you care about. Use a short interval during the slowdown, then compare it with a quiet period if you have one.

Cumulative waits answer what built up since startup or reset. Wait deltas answer what changed during the symptom window. Active waits answer what sessions are waiting on now. Good analysis uses all three instead of treating the biggest all-time wait as the root cause.

The sample uses five minutes. Adjust the interval to match the problem window. Do not reset wait stats just to create a clean report unless that is a deliberate operational decision.

Wait-stat delta over a short interval

Captures wait stats, waits for an adjustable interval, then reports what changed.

IF OBJECT_ID('tempdb..#waits_before') IS NOT NULL
    DROP TABLE #waits_before;

SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms
INTO #waits_before
FROM sys.dm_os_wait_stats;

-- Adjust this interval for the symptom window you are checking.
WAITFOR DELAY '00:05:00';

SELECT TOP (25)
    after_waits.wait_type,
    after_waits.waiting_tasks_count - before_waits.waiting_tasks_count AS waiting_tasks_delta,
    after_waits.wait_time_ms - before_waits.wait_time_ms AS wait_time_delta_ms,
    after_waits.signal_wait_time_ms - before_waits.signal_wait_time_ms AS signal_wait_delta_ms,
    (after_waits.wait_time_ms - before_waits.wait_time_ms)
      - (after_waits.signal_wait_time_ms - before_waits.signal_wait_time_ms) AS resource_wait_delta_ms
FROM sys.dm_os_wait_stats AS after_waits
JOIN #waits_before AS before_waits
    ON before_waits.wait_type = after_waits.wait_type
WHERE after_waits.wait_time_ms > before_waits.wait_time_ms
ORDER BY wait_time_delta_ms DESC;

wait_time_delta_ms is the main signal for this window. signal_wait_delta_ms can point toward scheduler pressure if it is a large share. A quiet interval should look different from the problem interval. If the same waits rise during the symptom window, check active waits and related sessions next.

4 / Active waits

Check active waits and waiting tasks

Active waits show what is waiting now. They are especially useful during a live slowdown because they can point to blocking, resource waits, parallel workers, or one program creating a queue.

Currently waiting tasks

Reads active waiting tasks with session, wait, blocker, resource, host, program, and login details.

SELECT
    wt.session_id,
    wt.exec_context_id,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.blocking_session_id,
    wt.resource_description,
    s.host_name,
    s.program_name,
    s.login_name
FROM sys.dm_os_waiting_tasks AS wt
LEFT JOIN sys.dm_exec_sessions AS s
    ON s.session_id = wt.session_id
WHERE wt.session_id IS NOT NULL
  AND wt.session_id <> @@SPID
ORDER BY wt.wait_duration_ms DESC;

wait_duration_ms shows how long the current wait has lasted. blocking_session_id tells you when the wait is directly blocked by another session. resource_description gives extra context for locks, latches, and other resources. If the same program or host repeats, check the active request and query text next.

Active requests with wait context

Shows running requests, current wait context, blocking session, wait resource, and running statement text.

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    s.host_name,
    s.program_name,
    s.login_name,
    SUBSTRING(
        sql_text.text,
        (r.statement_start_offset / 2) + 1,
        CASE
            WHEN r.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(max), sql_text.text))
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
        END
    ) AS running_statement
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS sql_text
WHERE r.session_id <> @@SPID
ORDER BY r.wait_time DESC;

wait_type and wait_time show the current request-level wait. last_wait_type helps when the request recently moved between waits. running_statement is the SQL text to connect with plans or application code. If blocking_session_id is nonzero, use the blocking guide checks.

5 / Session waits

Read session-level waits

Session-level waits help when instance totals are too broad. They show wait time collected by each session, which is useful for noisy application connections, reports, jobs, and batches.

This view resets for a session when the session opens or is reset. That makes it useful for session diagnosis, but it is not a permanent history table.

Session-level wait stats

Reads session wait stats with host, application, login, and session status.

SELECT TOP (50)
    sws.session_id,
    sws.wait_type,
    sws.waiting_tasks_count,
    sws.wait_time_ms,
    sws.wait_time_ms - sws.signal_wait_time_ms AS resource_wait_time_ms,
    sws.signal_wait_time_ms,
    s.status,
    s.host_name,
    s.program_name,
    s.login_name
FROM sys.dm_exec_session_wait_stats AS sws
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = sws.session_id
WHERE sws.wait_time_ms > 0
  AND sws.session_id <> @@SPID
ORDER BY sws.wait_time_ms DESC;

session_id ties the wait pattern to a live or recently active connection. program_name and host_name help connect waits to applications or job runners. High session waits deserve query text, plan, and timing checks. Connection pooling can reset session context, so use this as a live diagnostic input.

6 / Separation

Separate blocking from resource pressure

Lock waits need a different response from storage, CPU, memory, or log pressure. If the wait story is really a blocking chain, the fix usually lives in transactions, access order, indexing, or job timing.

If the waits point toward I/O, logging, memory grants, or CPU, do not jump straight to infrastructure. Check query shape, reads, transaction size, spills, plan changes, and workload timing first.

First split

1

LCK waits and blockers move into the blocking guide.

2

PAGEIOLATCH waits need query-shape and read-volume checks before storage blame.

3

WRITELOG needs transaction size, batching, and log latency checks.

4

Memory-related waits need grant, spill, and plan-quality review.

Blocking-related waits

Shows active requests with lock waits or blocking session IDs, including batch text.

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.open_transaction_count,
    DB_NAME(r.database_id) AS database_name,
    s.host_name,
    s.program_name,
    s.login_name,
    sql_text.text AS batch_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS sql_text
WHERE r.session_id <> @@SPID
  AND (
      r.blocking_session_id <> 0
      OR r.wait_type LIKE N'LCK%'
  )
ORDER BY r.wait_time DESC;

blocking_session_id shows direct blocking when SQL Server can identify it. LCK wait types mean the request is waiting on locks. open_transaction_count helps identify sessions holding locks longer than expected. If rows appear here during the incident, continue with the blocking guide.

7 / Wait families

Interpret common wait families

Wait familyWhat to check next
LCK waitsBlocking chains, transaction scope, isolation, access order, and indexing.
PAGEIOLATCHRead volume, plan shape, missing indexes, storage latency, and buffer cache pressure.
WRITELOGTransaction size, commit frequency, log disk latency, synchronous commit, and write bursts.
CXPACKET / CXCONSUMERPlan shape, parallel branches, skew, MAXDOP, cost threshold, and query tuning.
RESOURCE_SEMAPHOREMemory grants, spills, concurrency, cardinality estimates, and workload bursts.
ASYNC_NETWORK_IOClient fetch speed, application processing, row volume, and network path.
SOS_SCHEDULER_YIELDCPU pressure, long CPU-bound queries, plan regressions, and scheduler load.

SQL Agent job timing

Checks recent SQL Agent job step timing when wait spikes may align with scheduled work.

SELECT TOP (50)
    j.name AS job_name,
    h.step_id,
    h.step_name,
    h.run_status,
    DATETIMEFROMPARTS(
        h.run_date / 10000,
        (h.run_date % 10000) / 100,
        h.run_date % 100,
        h.run_time / 10000,
        (h.run_time % 10000) / 100,
        h.run_time % 100,
        0
    ) AS run_start_time,
    ((h.run_duration / 10000) * 3600)
      + (((h.run_duration % 10000) / 100) * 60)
      + (h.run_duration % 100) AS run_duration_seconds,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.step_id > 0
ORDER BY run_start_time DESC;

run_start_time lets you compare job timing with wait spikes. run_duration_seconds helps find jobs that overlap the slow period. Failed or long-running steps may explain sudden wait movement. If a job overlaps the incident, check its query text, plans, locks, and resource use.

8 / Misreads

Misleading wait patterns

Wait lists lie by omission. They do not show whether the wait happened during the incident, whether it came from user traffic, whether it is normal for this server, or whether a query shape created the pressure.

Totals also hide workload changes. A server can show the same top wait for months while the real problem yesterday came from a new job, release, report, blocker, or plan change.

Common mistakes

1

Reading cumulative totals without checking the incident window.

2

Treating normal background waits as urgent.

3

Ignoring scheduled work that explains the wait spike.

4

Skipping query text, plans, and blocking checks because a wait name looked familiar.

9 / Do not guess

What not to change blindly

Wait-stat fixes should be tied to active symptoms, time windows, sessions, query text, plans, and workload behavior. The wrong setting change can hide the issue or make another workload worse.

1

Do not treat top cumulative waits as root cause by default.

2

Do not reset wait stats casually in production.

3

Do not disable parallelism because CXPACKET or CXCONSUMER appears.

4

Do not blame storage from PAGEIOLATCH before checking query shape and reads.

5

Do not blame the log from WRITELOG before checking transaction size, batching, and disk latency.

6

Do not change memory, MAXDOP, cost threshold, or indexes from one wait snapshot alone.

10 / Performance review

When to request a performance review

A performance review makes sense when waits are visible but the next check is still unclear, or when the fix could touch blocking, indexing, jobs, storage, memory grants, CPU pressure, and plan quality at the same time.

Send wait stats, active waits, session waits, timing, query text, plans, job history, monitoring data, and what changed recently. That is enough to start separating signal from background noise.

Request performance reviewRead the blocking guide

Next step

If wait stats are visible but the next fix is still unclear, use the SQL Server performance review page or request the review above.

Next useful reads: the SQL Server slow performance guide for broad slowness, the SQL Server blocking guide for lock chains, the SQL Server deadlocks guide for victim errors, and the SQL Server monitoring guide for better recurring capture.