SQL Server Wait Statistics Review Skill

Purpose

Analyze SQL Server wait statistics and identify the dominant bottleneck using the Waits and Queues methodology (Paul Randal, SQLskills.com; Brent Ozar First Responder Kit). Applies 40 checks (V1–V40): 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).

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