/sqlwait-review

name: sqlwait-review
description: Analyze SQL Server wait statistics to identify why the server or a session is slow. Applies 44 checks (V1–V44) covering I/O, locks, parallelism, memory, CPU, TempDB, log I/O, network, latch contention, log space exhaustion, poison/throttle waits, backup I/O, insert hotspots, cumulative skew detection, multi-snapshot trend analysis, In-Memory OLTP, Columnstore, Query Store, Transaction/DTC, Service Broker, Full Text Search, Parallel Redo, forced memory grants, grant timeouts, stolen memory, file I/O latency, SQL 2019/2022 IQP/PSP/ADR feature waits, and TempDB memory-optimized metadata contention. Based on community wait statistics methodology. Use when pasting sys.dm_os_wait_stats or sys.dm_exec_requests output.
triggers:
  - /sqlwait-review
  - /wait-review
  - /waits

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 wai