# Sqlwait Review > 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. - Skill: `vanterx/sqlwait-review` (Agent Skill, multi-file: 11 files) - Install (CLI): `npx skillmds@latest add vanterx/sqlwait-review` - Raw SKILL.md: https://api.skillmd.com/api/skills/vanterx/sqlwait-review/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: vanterx (https://skillmd.com/u/vanterx) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/vanterx/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: ```sql -- 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: ```sql -- 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) ```sql 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; LCK_M_* changes based on RCSI state. ```sql -- 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)** ```sql -- 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)** ```sql -- 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. 2. **Compute total actionable wait time** = SUM(wait_time_ms) across all rows provided. 3. **Compute signal wait ratio** = SUM(signal_wait_time_ms) / SUM(wait_time_ms) × 100. 4. **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. 5. **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. 6. **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% + RESOURCE_SEMAPHORE > 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% + HADR_SYNC_COMMIT ≥ 5% → *"Log I/O pressure — the synchronous secondary may be unable to keep up with the primary's commit rate."* (c) LCK_M_* ≥ 1% + SOS_SCHEDULER_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. 7. **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. 6. **Output** the single-snapshot report as defined in Output Format (V1–V18, V27–V29 findings). 9. **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. 10. **Run V19–V26** using the per-period delta series. Also run V27–V29 (they work in both modes). 11. **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** ```sql 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_count FROM sys.dm_exec_query_resource_semaphores; ``` **Memory clerk breakdown — stolen memory check** ```sql SELECT type, name, pages_kb / 1024.0 / 1024.0 AS pages_gb, virtual_memory_reserved_kb / 1024.0 / 1024.0 AS virtual_gb, virtual_memory_committed_kb / 1024.0 / 1024.0 AS committed_gb FROM sys.dm_os_memory_clerks WHERE pages_kb > 1048576 -- > 1 GB ORDER BY pages_kb DESC; ``` **File I/O latency** ```sql SELECT DB_NAME(database_id) AS database_name, file_id, name AS file_name, type_desc, num_of_reads, num_of_writes, io_stall_read_ms, io_stall_write_ms, CAST(io_stall_read_ms / NULLIF(num_of_reads, 0) AS decimal(18, 2)) AS avg_read_latency_ms, CAST(io_stall_write_ms / NULLIF(num_of_writes, 0) AS decimal(18, 2)) AS avg_write_latency_ms, size_on_disk_bytes / 1024.0 / 1024.0 / 1024.0 AS size_gb FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_id ORDER BY io_stall_read_ms + io_stall_write_ms DESC; ``` ## Thresholds Reference **Important:** There are no universal thresholds for wait statistics. Compare against *your own system's baseline*, not industry averages. A CXPACKET percentage that is normal for a large analytics workload would be alarming on a pure OLTP system. The values below are investigative triggers — always verify with context. **Window dependency:** All percentage thresholds are window-independent — they reflect the proportion of wait time and are valid at any capture interval (5 min, 30 min, cumulative). Absolute ms thresholds scale with the window; per-minute rate equivalents are provided where applicable. | Metric | Investigative threshold | |--------|------------------------| | PAGEIOLATCH — I/O pressure | ≥ 10% investigate; ≥ 40% critical | | LCK_M — lock wait | any presence; ≥ 20% critical | | CXPACKET alone (not CXCONSUMER) | ≥ 15% investigate; ≥ 40% critical | | CXCONSUMER (SQL 2016 SP2+ / SQL 2017 CU3+) | generally benign — only investigate alongside very high CXPACKET | | RESOURCE_SEMAPHORE — memory grant queue | any presence > 0 ms | | RESOURCE_SEMAPHORE — critical | ≥ 5% of total wait time | | WRITELOG — log I/O | ≥ 10% investigate | | ASYNC_NETWORK_IO | ≥ 20% — but this is almost always a client-side problem, not SQL Server | | SOS_SCHEDULER_YIELD | ≥ 15% investigate — requires context; VM environments inflate this | | Signal wait ratio — CPU saturation | ≥ 15% warning; ≥ 25% critical | | THREADPOOL — thread exhaustion | any presence = Critical | | PAGELATCH (TempDB pages 1/2/3) | any presence = Warning | | LATCH_EX/SH (non-page latches) | ≥ 5% investigate | | LOGMGR_RESERVE_APPEND | any presence = Critical | | Single wait type dominance | ≥ 60% = focus all effort on this type | | Poison waits — window-scaled (V18) | `wait_time_ms > 1,000 × window_minutes` — e.g., > 5,000 ms for 5-min, > 30,000 ms for 30-min, > 60,000 ms for 60-min. If window unknown, use > 10,000 ms (conservative minimum). Cumulative: threshold formula `> 60,000 ms AND > (5,000 × hours_since_startup)`. | | "In context" concurrent sessions | `total_wait_ms ÷ window_ms`; requires known window — report N/A if window is unknown or cumulative | | Trend — spike (V20) | Single period ≥ 200% of that wait type's own average across all periods | | Trend — worsening (V19) | Delta % increases monotonically across ≥ 3 consecutive periods | | Trend — emerging (V23) | < 0.5% in period 1, ≥ 2.0% in any later period | | Trend — correlated (V24) | 2+ wait types each ≥ 150% of own average in the same period | | Forced memory grant (V37) | any forced_grant_count > 0 warning; > 10 critical | | Memory grant timeout (V38) | any timeout_error_count > 0 = Critical | | Stolen memory (V39) | ≥ 15% of max server memory warning; > 30% critical | | File I/O latency (V40) | avg read/write latency ≥ 100 ms warning; ≥ 500 ms critical | --- ## Wait Statistics Checks (V1–V36) ### V1 — Physical I/O Wait (PAGEIOLATCH) - **Trigger:** `PAGEIOLATCH_SH`, `PAGEIOLATCH_EX`, or `PAGEIOLATCH_UP` present AND combined ≥ 10% of total wait time - **Severity:** Warning (10–39%); Critical (≥ 40%) - **Fix:** Pages are being read from disk into the buffer pool. **Important:** do not blame the I/O subsystem first — the real question is *why is SQL Server reading so much data?* Inefficient queries (scans instead of seeks, missing indexes, stale statistics) are the root cause in most cases; the I/O subsystem is just the messenger. Fix options ranked: (1) Identify the heaviest-read queries with `/sqlstats-review` or `/sqltrace-review` and add covering indexes; (2) Add RAM to expand the buffer pool after addressing query efficiency; (3) Move data files to faster storage (SSD/NVMe) as a tertiary fix; (4) Identify hot tables with `sys.dm_os_buffer_descriptors`. ### V2 — Lock Waits (LCK_M) - **Trigger:** Any `LCK_M_*` wait type present AND combined ≥ 1% of total wait time - **Severity:** Warning (LCK_M combined 1–19%); Critical (≥ 20%) - **Fix:** Sessions are blocked waiting for row, page, or table locks. Key variants: `LCK_M_IX` (Intent Exclusive) — the most worrying lock wait, often caused by lock escalation or schema modification conflicts; `LCK_M_RS_*`, `LCK_M_RIn_*`, `LCK_M_RX_*` — range lock waits that indicate **SERIALIZABLE isolation level** is in use, holding range locks to prevent phantom reads. Fix options: (1) Use `sys.dm_os_waiting_tasks` to identify the blocking resource and head blocker; (2) Add indexes on WHERE clause columns to reduce scan-based lock scope; (3) Enable READ_COMMITTED_SNAPSHOT (`ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON`) to eliminate reader/writer shared lock conflicts; (4) For SERIALIZABLE range locks specifically: switch to SNAPSHOT isolation (`ALTER DATABASE ... SET ALLOW_SNAPSHOT_ISOLATION ON; SET TRANSACTION ISOLATION LEVEL SNAPSHOT`) — it provides consistent reads without range locks; (5) Use `/sqlblock-review` for the full blocking chain analysis. - **Configuration note:** If RCSI is **OFF** — enabling RCSI eliminates all reader-caused `LCK_M_S` and shared-lock conflicts in a single command; this is the highest-leverage fix and should be the first action. If RCSI is already **ON** — the remaining LCK_M waits come from explicit writers or lock escalation, which RCSI cannot resolve; focus on reducing scan scope with indexes and shortening transaction duration. ### V3 — Parallelism (CXPACKET / CXCONSUMER / CXSYNC_PORT / CXSYNC_CONSUMER / HT*) - **Trigger:** `CXPACKET` ≥ 15% of total wait time. `CXCONSUMER` alone is generally benign — only investigate if CXPACKET is also elevated. `CXSYNC_PORT` or `CXSYNC_CONSUMER` ≥ 5% (SQL 2022+ / Azure SQL only — see version note). `HTBUILD`, `HTDELETE`, `HTMEMO`, `HTREINIT`, `HTREPARTITION` (batch-mode hash build/repartition waits) — treat the same as CXPACKET; investigate skew before adjusting MAXDOP. - **Severity:** Warning (CXPACKET 15–39%); Critical (≥ 40%) — but **CXPACKET is not always a problem** - **Fix:** **Do not reflexively reduce MAXDOP.** CXPACKET records the control thread waiting for parallel worker threads to complete — this is normal and expected for parallel plans. The critical distinction: (1) If work is *evenly distributed* across threads and the query benefits from parallelism, high CXPACKET is fine; (2) If work is *skewed* (one thread does 90% of the work while others wait), that is the problem to fix. On SQL Server 2016 SP2 / SQL Server 2017 CU3 and later, `CXCONSUMER` was separated out — `CXPACKET` now represents the producer thread wait and is more actionable. Fix options when CXPACKET is genuinely problematic: (1) Raise Cost Threshold for Parallelism from default 5 to 25–50 — reduces unnecessary parallelism on medium-cost queries; (2) Update statistics — data skew causes uneven thread distribution; (3) Investigate specific queries via `sys.dm_exec_requests` (not `sys.dm_os_waiting_tasks` — CXPACKET threads may not appear there); (4) Only reduce MAXDOP after confirming parallelism is hurting, not helping. - **Configuration note:** If **MAXDOP = 0** and **CTPfP = 5** (both server defaults) — most medium-cost queries go parallel unnecessarily on modern multi-core hardware; raising CTPfP to 25–50 is the first fix and often resolves most of the CXPACKET wait without any MAXDOP change. If CTPfP is already ≥ 25 and MAXDOP is explicitly set — the CXPACKET is from large queries that genuinely benefit from parallelism; investigate per-query data skew with `sys.dm_exec_requests` before making any changes. Never reduce MAXDOP as a first response. ### V4 — Memory Grant Queue (RESOURCE_SEMAPHORE / RESOURCE_SEMAPHORE_QUERY_COMPILE) - **Trigger:** `RESOURCE_SEMAPHORE` present with any wait time > 0; `RESOURCE_SEMAPHORE_QUERY_COMPILE` present with any wait time > 0 AND ≥ 0.5% of total (lower threshold because compile-memory waits are usually small but impactful) - **Severity:** Warning (RESOURCE_SEMAPHORE < 5% of total, RESOURCE_SEMAPHORE_QUERY_COMPILE 0.5–2%); Critical (RESOURCE_SEMAPHORE ≥ 5%, RESOURCE_SEMAPHORE_QUERY_COMPILE ≥ 2%) - **Fix:** Two distinct memory grant pools — runtime and compile — each with different root causes: - **RESOURCE_SEMAPHORE (runtime memory grants):** queries queue for **execution memory** (Sort, Hash Match operators) before execution can begin. Fix: (1) Update statistics with FULLSCAN — stale stats → over-estimated row counts → oversized grants → few concurrent grants; (2) Add indexes to reduce sort/hash input sizes; (3) Add `OPTION (MIN_GRANT_PERCENT = n)` to cap individual grants; (4) Use Resource Governor to limit grant size per workload group; (5) Add RAM. Check `/sqlplan-review` S2/S3/S4 for the specific queries driving large grants. - **RESOURCE_SEMAPHORE_QUERY_COMPILE (compile memory grants):** queries queue for **compile memory** — a separate, smaller pool used during query optimization (plan compilation). Unlike runtime grants, compile memory exhaustion is driven by plan complexity and concurrency, not data volume. Fix: (1) Enable **optimize for ad hoc workloads** (`sp_configure 'optimize for ad hoc workloads', 1; RECONFIGURE`) — prevents storing full compiled plans for single-use ad-hoc queries, freeing compile memory; (2) Simplify complex queries — deeply nested views, very long IN lists, or queries referencing hundreds of tables consume disproportionate compile memory; (3) Use `OPTION (KEEPFIXED PLAN)` on queries that recompile unnecessarily — it suppresses recompilation from statistics changes; (4) If `RESOURCE_SEMAPHORE_QUERY_COMPILE` is the dominant wait (≥ 2%) while `RESOURCE_SEMAPHORE` is low, the bottleneck is compile-bound, not data-bound — `optimize for ad hoc workloads` is the highest-leverage fix. - **Configuration note:** If **Max Server Memory is 0** (the default, meaning unlimited) — SQL Server may consume all available RAM, leaving no room for new memory grants to be allocated concurrently; setting Max Server Memory to (total RAM × 90% − OS overhead) is the prerequisite fix. If Max Server Memory is already correctly bounded — the issue is individual grants being oversized due to stale statistics, not total RAM shortage; update statistics first. If `RESOURCE_SEMAPHORE_QUERY_COMPILE` is high and **optimize for ad hoc workloads** is **OFF** — enabling it is the single most effective fix for compile-memory pressure. ### V5 — Transaction Log I/O (WRITELOG / LOGBUFFER) - **Trigger:** `WRITELOG` or `LOGBUFFER` ≥ 10% of total wait time combined - **Severity:** Warning (10–29%); Critical (≥ 30%) - **Fix:** `WRITELOG` — every COMMIT flushes the transaction log synchronously. `LOGBUFFER` — threads waiting for space in the log buffer before writing; indicates the log buffer is full, often from very high DML rates. Both indicate log I/O pressure. Every COMMIT requires SQL Server to harden the log to disk before returning. Note: on faster storage, WRITELOG waits may *increase* as higher throughput generates more commits — this is not necessarily a problem, just higher transaction volume. Fix options when WRITELOG is genuinely the bottleneck: (1) Move the transaction log to dedicated fast storage (NVMe with low write latency — the log is sequential write, so IOPS matter less than latency); (2) Separate the log from data files so I/O does not compete; (3) Batch small transactions — reducing commit frequency reduces log flush frequency; (4) Delayed Durability (SQL Server 2014+) — `ALTER DATABASE YourDb SET DELAYED_DURABILITY = FORCED` batches log flushes; trade-off is potential data loss of the last batch on crash; (5) SQL Server 2012+ raised the per-database limit on outstanding log write I/Os (from 32 to 112 [Unverified]) — ensure you are not on SQL 2008. - **Configuration note:** If **Delayed Durability is DISABLED** and log I/O is the confirmed bottleneck — consider `ALTER DATABASE YourDb SET DELAYED_DURABILITY = ALLOWED`, which lets applications opt into batched log flushes for workloads that can tolerate up to ~1 ms of committed-but-not-hardened data on a crash. If **Delayed Durability is already FORCED** and WRITELOG is still high — the issue is raw log file I/O throughput (too many commits even after batching), not commit frequency; move the log to dedicated faster storage. ### V6 — Client Result Consumption (ASYNC_NETWORK_IO) - **Trigger:** `ASYNC_NETWORK_IO` ≥ 20% of total wait time - **Severity:** Info — **this wait type is almost never a SQL Server problem** - **Fix:** SQL Server has results ready in its output buffer but the client is not consuming them. This wait type is never indicative of a problem with SQL Server — the bottleneck is always client-side. Investigation steps: (1) Check if the client is processing rows one at a time (RBAR — row-by-row processing) instead of bulk reading; (2) Test raw network latency between SQL Server and application server; (3) Check for VM host oversubscription on the application server; (4) If using MARS (Multiple Active Result Sets), large result sets can inflate this wait; (5) Reduce result set size as a mitigation — `SET NOCOUNT ON`, explicit column lists, pagination. Do not tune SQL Server to fix ASYNC_NETWORK_IO. ### V7 — Scheduler Yield (SOS_SCHEDULER_YIELD) - **Trigger:** `SOS_SCHEDULER_YIELD` ≥ 15% of total wait time - **Severity:** Warning — but **this does NOT necessarily indicate CPU pressure and does NOT indicate LOCK_HASH spinlock contention** - **Fix:** SQL Server threads complete a 4 ms CPU quantum and voluntarily yield the scheduler. High SOS_SCHEDULER_YIELD is most commonly caused by queries doing large in-memory page scans (e.g., missing index → table scan, which repeatedly accesses buffer pool pages without suspending). **Critical clarification:** (1) SOS_SCHEDULER_YIELD does NOT indicate LOCK_HASH spinlock issues — threads backing off from spinlock collisions use Windows `Sleep()` which is invisible in wait statistics; (2) On virtual machines, this wait is often artificially elevated because the VM clock counter includes hypervisor scheduling delay, making threads appear to burn longer quanta than they actually do. Fix options: (1) Identify the specific queries via `sys.dm_exec_requests` (threads with this wait are RUNNABLE, not SUSPENDED — they may not appear in `sys.dm_os_waiting_tasks`); (2) Add indexes to eliminate in-memory scans; (3) If running in a VM, check host oversubscription before assuming a SQL Server problem. ### V8 — Thread Pool Exhaustion (THREADPOOL) - **Trigger:** `THREADPOOL` present with any wait time - **Severity:** Critical (any presence) - **Fix:** SQL Server has run out of worker threads. New requests queue waiting for a thread. This is a severe capacity problem. Immediate actions: (1) Kill long-running or orphaned sessions (`KILL spid`); (2) Increase `max worker threads` (`sp_configure`) — but investigate root cause first; (3) Root causes: many long-running blocking chains consuming threads, many parallel queries consuming multiple threads each (reduce MAXDOP), application creating too many connections (use connection pooling). Investigate with `sys.dm_os_workers` and `sys.dm_exec_sessions`. ### V9 — TempDB Allocation Contention (PAGELATCH) - **Trigger:** `PAGELATCH_EX` or `PAGELATCH_SH` present, especially on database ID 2 (TempDB) pages 1, 2, or 3 (PFS, GAM, SGAM allocation pages) - **Severity:** Warning - **Fix:** Multiple sessions are contending for TempDB allocation page latches. This happens when many sessions create/drop temp objects simultaneously. Fix: (1) Add TempDB data files (one per logical CPU core, up to 8) — distributes allocation page contention across files; (2) Enable trace flag 1118 (SQL 2014 and earlier) to use uniform extents — on SQL 2016+ TempDB always uses uniform extents and TF 1118 is no longer needed (for user databases, `ALTER DATABASE ... SET MIXED_PAGE_ALLOCATION OFF` controls this); (3) Use table variables instead of temp tables for small, single-row data sets; (4) Avoid dropping and recreating temp tables in loops. - **Configuration note:** Compare **TempDB data file count** against `min(logical CPU count, 8)`. If files < target — adding the missing files is the direct fix (this is the most common TempDB contention remedy). If already at 8 files and PAGELATCH persists — verify all files are **equal size**; SQL Server uses proportional fill, so a larger file receives more allocations and re-centralises contention. Also confirm Trace Flag 1118 / Mixed Extent Allocations is set correctly for the SQL Server version. ### V10 — Signal Wait Ratio (CPU Saturation Indicator) - **Trigger:** `signal_wait_time_ms / wait_time_ms` across all wait types ≥ 15% - **Severity:** Warning (15–24%); Critical (≥ 25%) - **Fix:** Signal wait time = time a thread waited for CPU after its lock/I/O was satisfied. High signal waits mean CPU is the bottleneck — threads are ready to run but no CPU is available. This often accompanies V7 (SOS_SCHEDULER_YIELD). Fix: reduce CPU-intensive queries (scans, large sorts), add CPU cores, or reduce parallelism to free per-query CPU threads. ### V11 — OLE DB Provider Calls (OLEDB) - **Trigger:** `OLEDB` ≥ 5% of total wait time — but **duration matters: short waits may be benign** - **Severity:** Info (milliseconds per call, millions of occurrences — likely monitoring tools); Warning (tens or hundreds of ms per call — likely linked servers or SSIS) - **Fix:** OLEDB is a preemptive wait — the thread does not yield the scheduler while waiting. Context determines severity: (1) **Millisecond waits with very high task counts** — monitoring tools (SQL Server Management Studio, third-party monitors, DMV polling) query internal providers constantly; these are benign and can appear in the top-10 without indicating a problem; (2) **Tens to hundreds of ms per wait** — linked server queries or SSIS are the cause; these need investigation. Fix for actionable OLEDB: (1) Identify the linked server queries with `/sqltrace-review`; (2) Replicate remote data locally and query locally; (3) Use `OPENQUERY` to push filters to the remote server; (4) Reduce monitoring poll frequency if monitoring tools are the cause. ### V12 — High Availability Synchronization (HADR / DBMIRROR) - **Trigger:** Any `HADR_*`, `PWAIT_HADR_*`, or `DBMIRROR_*` wait type ≥ 5% of total wait time - **Severity:** Warning - **Fix:** The primary replica is waiting for secondary replicas to acknowledge log hardening (synchronous commit) or log send (asynchronous). `HADR_SYNC_COMMIT` is the primary synchronous-commit latency wait — if this type dominates HADR waits, the secondary log I/O or network is the direct bottleneck. Fix options: (1) Switch non-critical databases to asynchronous commit mode; (2) Investigate network latency between primary and secondary; (3) Move secondary replicas to faster storage for log writes; (4) Use `sys.dm_hadr_database_replica_states` to identify the lagging secondary. - **Configuration note:** **Synchronous-commit mode** — every COMMIT on the primary waits for the secondary to acknowledge log hardening; secondary storage latency + network round-trip add directly to primary commit time, and HADR_SYNC_COMMIT waits are expected and proportional. **Asynchronous-commit mode** — HADR_SYNC_COMMIT should not appear at all; if it does, the replica's commit mode may have been changed or a formerly-async replica is being added to the synchronous quorum. Verify with `SELECT availability_mode_desc FROM sys.availability_replicas`. ### V13 — External / OS Calls (PREEMPTIVE Waits) - **Trigger:** Any `PREEMPTIVE_*` wait type ≥ 10% of total wait time - **Severity:** Warning - **Fix:** SQL Server is making preemptive OS calls — CLR assemblies, extended stored procedures, COM objects, or Windows authentication. These bypass SQL Server's cooperative scheduling. Fix: (1) Identify which CLR objects or xp_* calls are running via Extended Events; (2) Replace xp_cmdshell with SQL Server Agent jobs; (3) Minimize CLR usage or move CLR work to application layer. **Cross-correlation:** When `PREEMPTIVE_OS_WRITEFILEGATHERER` is prominent alongside V5 (WRITELOG), check for frequent autogrowth events — query `sys.dm_os_performance_counters` for the `Log Growths` counter per database, or review the default trace for autogrowth events. Autogrowth is a common trigger of `PREEMPTIVE_OS_WRITEFILEGATHERER` + `WRITELOG` co-occurrence. ### V14 — Single Wait Type Dominance - **Trigger:** Any single wait type accounts for ≥ 60% of total wait time - **Severity:** Info - **Fix:** The server has one dominant bottleneck — this is actually good news for troubleshooting. Focus all tuning effort on the root cause of that single wait type before addressing anything else. Report which wait type dominates and cross-reference the appropriate check above. ### V15 — Non-Page Latch Contention (LATCH_EX / LATCH_SH) - **Trigger:** `LATCH_EX` or `LATCH_SH` ≥ 5% of total wait time. **Distinguish from PAGELATCH** (V9): PAGELATCH protects in-memory data pages; LATCH_EX/SH protects internal SQL Server non-page data structures. - **Severity:** Warning - **Fix:** Non-page latches protect internal structures — index trees, log manager, file group control blocks, parallel scan infrastructure. Without knowing *which* latch class is contended, diagnosis is impossible. Fix steps: (1) Query `sys.dm_os_latch_stats` to identify the specific latch class: `SELECT * FROM sys.dm_os_latch_stats WHERE latch_class NOT IN ('BUFFER','ACCESS_METHODS_HOBT_COUNT') ORDER BY wait_time_ms DESC`; (2) Common latch classes and fixes: `ACCESS_METHODS_DATASET_PARENT` / `ACCESS_METHODS_SCAN_RANGE_GENERATOR` — parallel scan contention, often co-occurs with CXPACKET; `LOG_MANAGER` — transaction log growth contention (pre-size the log); `TRACE_CONTROLLER` — SQL Trace is enabled and generating excessive overhead (switch to Extended Events); `FGCB_ADD_REMOVE` — file auto-growth is triggering (pre-size data files); `DATABASE_MIRRORING_CONNECTION` — mirroring message throughput (check network). ### V16 — Log Space Exhaustion (LOGMGR_RESERVE_APPEND) - **Trigger:** `LOGMGR_RESERVE_APPEND` present with any wait time - **Severity:** Critical — this is very unusual to see as a top wait and always indicates a serious problem - **Fix:** A thread needs to write a log record but there is no space available in the transaction log. Most commonly occurs in SIMPLE recovery mode with zero or insufficient autogrowth. This causes all DML to block until log space is freed (via checkpoint and log reuse) or the log grows. Fix: (1) Immediately: determine why the log is full — `DBCC SQLPERF('LOGSPACE')` and `SELECT log_reuse_wait_desc FROM sys.databases`; (2) If SIMPLE recovery: the log cannot be backed up — it only frees space via checkpoint. A long-running active transaction may be preventing checkpoint from truncating the log. (3) Fix: increase log autogrowth size, or switch to FULL recovery with regular log backups so space is regularly reclaimed; (4) Never set autogrowth to 0 — that prevents the log …(truncated)