query-store-review
SQL Server Query Store Review Skill
Purpose
Analyze SQL Server Query Store (sys.query_store_* DMV) output to identify the most impactful queries in a workload, detect performance regressions, surface plan instability, flag resource hotspots, and audit Query Store configuration health. Applies 25 checks across five categories: regressed queries (Q1–Q6), plan stability (Q7–Q12), resource hotspots (Q13–Q18), query-level waits (Q19–Q22), and operational health (Q23–Q25).
Query Store is the most powerful built-in monitoring tool in SQL Server 2016+. It persists query execution history, plan history, runtime statistics, and wait statistics across server restarts — enabling trend analysis without external monitoring tools. This skill is the diagnostic counterpart to sqlplan-review: Query Store tells you which queries need attention; execution plan review tells you why.
Based on patterns from Erik Darling's sp_QuickieStore and the Microsoft Query Store DMV documentation.
Input
Accept any of:
- Raw
sys.query_store_runtime_stats+sys.query_store_query+sys.query_store_planquery output (paste result grid) sys.query_store_wait_statsoutput (SQL 2017+, optional)- Query Store configuration output from
sys.database_query_store_options - A
.csvor.txtfile containing any of the above - A natural language description of Query Store findings ("3 queries regressed after the deployment, Proc_Report went from 200ms to 8s")
More from vanterx/mssql-performance-skills
sqlplan-review
Analyze SQL Server execution plans for performance anti-patterns. Applies all 99 checks (S1–S33 statement-level, N1–N66 node-level). Use when a user pastes a .sqlplan XML, describes operators, or asks why a query is slow.
2sqlplan-deadlock
Analyze SQL Server deadlock XML (from system_health XE session, SSMS deadlock graph, or trace) to identify root cause and produce a prioritized remediation plan. Use when a deadlock monitor captures a graph or users report intermittent deadlock errors (error 1205).
1sqlplan-compare
Diff two SQL Server execution plans (baseline vs regression) to identify what changed — join strategies, memory grants, operator topology, new warnings, and missing indexes. Use when a query regressed after a deployment, statistics update, or schema change.
1procstats-review
Analyze SQL Server procedure/trigger/function runtime stats collected from sys.dm_exec_procedure_stats into collect.proc_stats. Applies 20 checks (R1–R20) across four categories — top consumers, per-execution efficiency, pattern detection, and trend analysis. Use when pasting output from the report queries in sql/collection/04_report_queries.sql.
1sqlstats-review
Parse and analyze SQL Server SET STATISTICS IO, TIME ON output. Extracts per-table IO metrics and per-statement CPU/elapsed times, computes % logical read share, detects 22 performance patterns (I1–I15 IO checks, W1–W7 time checks). Use when a user pastes SSMS statistics output or asks why a query does too much I/O.
1sqlwait-review
Analyze SQL Server wait statistics to identify why the server or a session is slow. Applies 40 checks (V1–V40) 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, and file I/O latency. Based on Paul Randal and Brent Ozar methodologies. Use when pasting sys.dm_os_wait_stats or sys.dm_exec_requests output.
1