Sqlwait Review

SQL Server Wait Statistics Review Skill

Purpose

Analyze SQL Server wait statistics and identify the dominant bottleneck using the Waits and Queues methodology. Applies 44 checks (V1–V44): V1–V18 classify each significant wait type into its root cause and produce a prioritized remediation plan; V19–V26 perform multi-snapshot trend analysis when 3+ time windows are provided — detecting worsening trends, spikes, peak periods, and emerging bottlenecks; V27–V29 cover specialized scenarios (PAGELATCH on user databases, backup I/O, cumulative skew from outlier events); V30–V36 cover modern feature wait types (In-Memory OLTP, Columnstore, Query Store, Transaction/DTC, Service Broker, Full Text Search, Parallel Redo); V37–V40 add DMV-level memory and I/O detail — forced memory grants, grant timeouts, stolen memory, and file-level I/O latency (requires optional capture queries); V41–V44 cover SQL 2019/2022 IQP/PSP/ADR feature-specific wait types and TempDB memory-optimized metadata contention (SQL 2019+).

The Waits and Queues methodology is based on how SQL Server's thread scheduler works: threads are always in one of three states — RUNNING (on CPU), RUNNABLE (queued for CPU), or SUSPENDED (waiting for a resource). Every time a thread suspends, SQL Server records the wait type and duration. Analyzing the top accumulated waits reveals the dominant bottleneck — not by guessing, but by measuring exactly what the server spent its time waiting for.

Wait analysis answers the question execution plans cannot: why is the server slow when no individual query has a bad plan? The answer is almost always in the wait types — I/O, locks, CPU, memory, or network.

Input

Accept any of:

• Output from the sys.dm_os_wait_stats capture query below (paste the result grid)
• Output from sys.dm_exec_requests for current active session waits
• A .txt or .csv file containing either of the above
• A natural language description of the top wait types ("PAGEIOLATCH_SH is 78% of waits, CXPACKET is 12%")

Recommended capture query

Run on the SQL Server instance and paste the results:

-- Wait statistics since last SQL Server restart or DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)
-- Benign exclusion list based on community wait statistics methodology
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    CAST(100.0 * wait_time_ms
         / NULLIF(SUM(wait_time_ms) OVER (), 0) AS DECIMAL(5,2)) AS pct_total
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    -- Broker / Service Broker
    'BROKER_EVENTHANDLER','BROKER_RECEIVE_WAITFOR','BROKER_TASK_STOP',
    'BROKER_TO_FLUSH','BROKER_TRANSMITTER',
    -- Checkpoint / CLR
    'CHECKPOINT_QUEUE','CHKPT','CLR_AUTO_EVENT','CLR_MANUAL_EVENT','CLR_SEMAPHORE',
    -- Mirroring / HADR background (idle components only — not HADR_SYNC_COMMIT)
    'DBMIRROR_DBM_EVENT','DBMIRROR_DBM_MUTEX','DBMIRROR_EVENTS_QUEUE',
    'DBMIRROR_WORKER_QUEUE','DBMIRRORING_CMD',
    'HADR_CLUSAPI_CALL','HADR_FABRIC_CALLBACK','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    'HADR_LOGCAPTURE_WAIT','HADR_NOTIFICATION_DEQUEUE','HADR_TIMER_TASK',
    'HADR_WORK_QUEUE',
    -- Background / dispatcher
    'DIRTY_PAGE_POLL','DISPATCHER_QUEUE_SEMAPHORE',
    'EXECSYNC','FSAGENT',
    'FT_IFTS_SCHEDULER_IDLE_WAIT','FT_IFTSHC_MUTEX',
    'KSOURCE_WAKEUP','LAZYWRITER_SLEEP','LOGMGR_QUEUE',
    'MEMORY_ALLOCATION_EXT',
    'ONDEMAND_TASK_QUEUE',
    'PARALLEL_REDO_DRAIN_WORKER','PARALLEL_REDO_LOG_CACHE',
    'PARALLEL_REDO_TRAN_LIST','PARALLEL_REDO_WORKER_SYNC',
    'PARALLEL_REDO_WORKER_WAIT_WORK','POPULATE_LOCK_ORDINALS',
    'PREEMPTIVE_HADR_LEASE_MECHANISM','PREEMPTIVE_OS_FLUSHFILEBUFFERS',
    'PREEMPTIVE_SP_SERVER_DIAGNOSTICS','PREEMPTIVE_XE_GETTARGETSTATE',
    'PVS_PREALLOCATE',
    'PWAIT_ALL_COMPONENTS_INITIALIZED','PWAIT_DIRECTLOGCONSUMER_GETNEXT',
    'PWAIT_EXTENSIBILITY_CLEANUP_TASK',
    'QDS_ASYNC_QUEUE','QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
    'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','QDS_SHUTDOWN_QUEUE',
    'REDO_THREAD_PENDING_WORK',
    'REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE',
    'SERVER_IDLE_CHECK','SLEEP_BPOOL_FLUSH',
    'SLEEP_DBSTARTUP','SLEEP_DBTASK','SLEEP_DCOMSTARTUP',
    'SLEEP_MASTERDBREADY','SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED',
    'SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TASK','SLEEP_TEMPDBSTARTUP',
    'SNI_HTTP_ACCEPT','SOS_WORK_DISPATCHER',
    'SP_SERVER_DIAGNOSTICS_SLEEP',
    'SQLTRACE_BUFFER_FLUSH','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    'UCS_SESSION_REGISTRATION','VDI_CLIENT_OTHER',
    'WAIT_FOR_RESULTS','WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
    'WAITFOR','WAITFOR_TASKSHUTDOWN',
    'XE_DISPATCHER_WAIT','XE_LIVE_TARGET_TVF','XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

Two-snapshot differential query (recommended approach)

Cumulative waits since restart can be misleading — a busy nightly backup from 2 weeks ago dominates. Capture a differential over 30 minutes instead:

-- Snapshot 1 (run at T0)
-- Note: shorter exclusion list is acceptable here because delta subtraction between identical
-- snapshots cancels out idle waits. For non-differential capture, use the full list above.
SELECT wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count
INTO #waits_before FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK','WAITFOR','LAZYWRITER_SLEEP',
    'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_DISPATCHER_WAIT');

WAITFOR DELAY '00:30:00';   -- wait 30 minutes (adjust as needed)

-- Snapshot 2 (run at T30)
SELECT
    a.wait_type,
    b.wait_time_ms - a.wait_time_ms               AS wait_time_ms_delta,
    b.signal_wait_time_ms - a.signal_wait_time_ms AS signal_wait_ms_delta,
    b.waiting_tasks_count - a.waiting_tasks_count  AS tasks_delta,
    CAST(100.0 * (b.wait_time_ms - a.wait_time_ms)
         / NULLIF(SUM(b.wait_time_ms - a.wait_time_ms) OVER (), 0)
         AS DECIMAL(5,2))                          AS pct_of_period
FROM #waits_before a
JOIN sys.dm_os_wait_stats b ON b.wait_type = a.wait_type
WHERE b.wait_time_ms > a.wait_time_ms
ORDER BY wait_time_ms_delta DESC;

DROP TABLE #waits_before;

Current session waits (point-in-time)

SELECT
    r.session_id,
    r.wait_type,
    r.wait_time / 1000.0 AS wait_sec,
    r.blocking_session_id,
    r.status,
    DB_NAME(r.database_id) AS database_name,
    SUBSTRING(t.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
          ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS current_statement
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50
  AND r.session_id <> @@SPID
ORDER BY r.wait_time DESC;

Server configuration capture (recommended)

Paste this alongside your wait statistics. The skill uses these values to adjust check interpretations — e.g., CXPACKET is interpreted differently based on MAXDOP and Cost Threshold for Parallelism; LCKM* changes based on RCSI state.

-- sp_configure values
SELECT name AS config_name, CAST(value_in_use AS INT) AS current_value
FROM sys.configurations
WHERE name IN (
    'max degree of parallelism',
    'cost threshold for parallelism',
    'max server memory (MB)',
    'optimize for ad hoc workloads',
    'max worker threads',
    'xp_cmdshell',
    'clr enabled',
    'lightweight pooling',
    'blocked process threshold (s)',
    'query governor cost limit'
);

-- Per-database settings (run for the database under investigation)
SELECT
    name AS database_name,
    is_read_committed_snapshot_on,
    recovery_model_desc,
    delayed_durability_desc
FROM sys.databases
WHERE database_id = DB_ID();

-- TempDB file count
SELECT COUNT(*) AS tempdb_data_file_count
FROM sys.master_files
WHERE database_id = 2 AND type = 0;

-- Always On commit mode (if configured)
SELECT ag.name AS ag_name, ar.availability_mode_desc AS commit_mode, ars.role_desc
FROM sys.availability_replicas ar
JOIN sys.availability_groups ag ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = ar.replica_id
WHERE ars.is_local = 1;

If configuration is not provided, the skill still runs all 26 checks and notes where config would change the interpretation.

Multi-snapshot trend capture (activates V19–V26)

Trend mode activates automatically when the input contains 3 or more distinct timestamps. Single-snapshot mode (V1–V18) is unchanged when only one time window is present.

Approach A — Staging table with SQL Agent job (recommended for automated capture)

-- Create once per server (or use tempdb.dbo for session-scoped capture)
CREATE TABLE dbo.WaitStatsTrend (
    snapshot_time       DATETIME2    NOT NULL DEFAULT SYSDATETIME(),
    wait_type           NVARCHAR(120) NOT NULL,
    wait_time_ms        BIGINT       NOT NULL,
    signal_wait_time_ms BIGINT       NOT NULL,
    waiting_tasks_count BIGINT       NOT NULL
);

-- Run every N minutes via SQL Agent job (or execute manually N times)
INSERT INTO dbo.WaitStatsTrend (wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count)
SELECT wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','WAITFOR','LAZYWRITER_SLEEP','CHECKPOINT_QUEUE',
    'REQUEST_FOR_DEADLOCK_SEARCH','XE_DISPATCHER_WAIT','XE_TIMER_EVENT',
    'BROKER_TO_FLUSH','BROKER_TRANSMITTER','SLEEP_DBSTARTUP','SLEEP_DBTASK',
    'SLEEP_MASTERDBREADY','SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED',
    'SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP',
    'SNI_HTTP_ACCEPT','SOS_WORK_DISPATCHER','SP_SERVER_DIAGNOSTICS_SLEEP',
    'SQLTRACE_BUFFER_FLUSH','SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
);

-- Query for trend analysis — paste result to /sqlwait-review alongside configuration
SELECT
    snapshot_time,
    wait_type,
    wait_time_ms   - LAG(wait_time_ms)   OVER (PARTITION BY wait_type ORDER BY snapshot_time) AS delta_wait_ms,
    signal_wait_time_ms - LAG(signal_wait_time_ms) OVER (PARTITION BY wait_type ORDER BY snapshot_time) AS delta_signal_ms,
    waiting_tasks_count - LAG(waiting_tasks_count) OVER (PARTITION BY wait_type ORDER BY snapshot_time) AS delta_tasks
FROM dbo.WaitStatsTrend
WHERE snapshot_time >= DATEADD(HOUR, -2, SYSDATETIME())
ORDER BY snapshot_time, delta_wait_ms DESC;

Approach B — Manual multi-run (no staging table)

-- Run every N minutes and paste all result sets together (labeled with a comment for each run)
-- The skill detects multiple timestamp values and activates trend mode automatically
-- Note: shorter exclusion list is acceptable for differential trend mode; delta subtraction
-- between consecutive cumulative snapshots cancels out idle waits. For the full exclusion
-- list, use the staging-table approach (Approach A) above.
SELECT
    CONVERT(NVARCHAR(20), SYSDATETIME(), 120) AS snapshot_time,
    wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count,
    CAST(100.0 * wait_time_ms / NULLIF(SUM(wait_time_ms) OVER(), 0) AS DECIMAL(5,2)) AS pct_total
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','WAITFOR','LAZYWRITER_SLEEP','CHECKPOINT_QUEUE',
    'REQUEST_FOR_DEADLOCK_SEARCH','XE_DISPATCHER_WAIT','XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

With Approach B, the skill computes per-period deltas by subtracting consecutive cumulative values within each wait_type across snapshots.

Minimum snapshots: 2 periods for V20/V21/V23; 3+ periods for V19/V22/V24/V25/V26 (full trend analysis).

How to Run

1. Parse the input into rows of: wait_type, wait_time_ms, waiting_tasks_count, signal_wait_time_ms, pct_total.

1a. Detect capture window duration — this determines whether absolute ms thresholds and the "In context" metric are computable:

• Trend mode: Compute the time difference between consecutive snapshot_time values for each wait_type. Report the median interval in minutes as the period length. If any consecutive pair differs by more than 20% from the median, flag unequal intervals — V21 and V22 must use per-minute normalization in that case.
• Single snapshot — timestamp present: Parse any window declaration from the input (e.g., a header comment stating "30-minute differential"). Use that value.
• Single snapshot — no timestamp: Flag: "Capture window unknown — state the differential interval or elapsed time for accurate 'In context' calculation and V18 threshold scaling. Percentage thresholds (V1–V17) remain fully valid."
• Cumulative since restart: Note that absolute ms totals reflect the entire uptime period; percentage thresholds are still fully valid, but absolute ms comparisons and "In context" are not meaningful.

1. Compute total actionable wait time = SUM(waittimems) across all rows provided.
2. Compute signal wait ratio = SUM(signalwaittimems) / SUM(waittime_ms) × 100.
3. Run V1–V18 — check each wait type's presence and share of total. V17 always fires (top-5 summary). V18 (poison waits) uses the window-scaled threshold from step 1a.
4. Flag any unknown wait types — if a wait type accounts for ≥ 2% of total wait time but does not match any V1–V18 or V27–V29 pattern, flag as Info: "Unknown wait type '' at % — may be new in your SQL Server version; review current Microsoft documentation." These are not errors but should be surfaced so the user is aware of gaps in automated analysis.
5. Check for known cross-wait correlations in single-snapshot mode — when V24 (correlated spikes) cannot fire because trend data is absent, flag these known co-occurring pairs if both exceed their individual thresholds in the same snapshot: (a) PAGEIOLATCH ≥ 10% + RESOURCESEMAPHORE > 0 ms → "These often share a root cause — a missing index causing large scans (driving I/O) that also request large memory grants." (b) WRITELOG ≥ 10% + HADRSYNCCOMMIT ≥ 5% → "Log I/O pressure — the synchronous secondary may be unable to keep up with the primary's commit rate." (c) LCKM ≥ 1% + SOSSCHEDULER_YIELD ≥ 15% → "Long-running scans may be holding locks while burning CPU quanta."* These are Info-level correlations, not independent findings — they guide the user to a common root cause.
6. Note the capture window — if cumulative since restart, high values for rare events (nightly backup, weekly DBCC) can skew results. Prefer the differential query output if available.
7. Output the single-snapshot report as defined in Output Format (V1–V18, V27–V29 findings).
8. Detect trend mode — count distinct timestamp values in the input. If ≥ 3: activate trend analysis for V19–V26.

• Approach A input (pre-computed deltas): use delta_wait_ms and compute pct_of_period = delta_wait_ms / SUM(delta_wait_ms per snapshot) × 100 per time window.
• Approach B input (cumulative values): for each consecutive pair of snapshots, compute delta = value[T] − value[T−1] per wait_type; then compute pct_of_period per window from those deltas.

1. Run V19–V26 using the per-period delta series. Also run V27–V29 (they work in both modes).
2. Append Trend Analysis section to the output after ### Passed Checks.

---

Optional: Memory and I/O detail capture queries

Paste these alongside wait stats for richer memory-pressure and file-I/O analysis (enables V37–V40):

Memory grant detail — forced grants and timeouts

SELECT
    resource_semaphore_id,
    target_memory_kb / 1024.0 / 1024.0 AS target_memory_gb,
    max_target_memory_kb / 1024.0 / 1024.0 AS max_target_memory_gb,
    total_memory_kb / 1024.0 / 1024.0 AS total_memory_gb,
    available_memory_kb / 1024.0 / 1024.0 AS available_memory_gb,
    granted_memory_kb / 1024.0 / 1024.0 AS granted_memory_gb,
    used_memory_kb / 1024.0 / 1024.0 AS used_memory_gb,
    grantee_count,
    waiter_count,
    forced_grant_count,
    timeout_error_c

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [vanterx](https://github.com/vanterx)
- **Source:** [vanterx/mssql-performance-skills](https://github.com/vanterx/mssql-performance-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.