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; LCK_M_* 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.
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.
8. 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

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

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

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.

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)