sqlquerystore-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, audit Query Store configuration health, and detect SQL 2019/2022 IQP/PSP/DOP/CE feedback signals. Applies 32 checks across six categories: regressed queries (Q1–Q6), plan stability (Q7–Q12), resource hotspots (Q13–Q18), query-level waits (Q19–Q22), operational health (Q23–Q25), and modern IQP/feedback checks (Q26–Q32).

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 Microsoft Query Store DMV documentation and SQL Server community best practices.

Input

Accept any of:

• Raw sys.query_store_runtime_stats + sys.query_store_query + sys.query_store_plan query output (paste result grid)
• sys.query_store_wait_stats output (SQL 2017+, optional)
• Query Store configuration output from sys.database_query_store_options
• A .csv or .txt file 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")

Recommended capture queries

Run these in SSMS and paste the output. The primary query (A) is required; queries B and C provide richer analysis.

Query A — Top Resource Consumers (SQL 2016+)

-- Replace the date range as needed. Default: last 7 days.
DECLARE @start_date datetimeoffset = DATEADD(DAY, -7, GETUTCDATE());
DECLARE @end_date   datetimeoffset = GETUTCDATE();
DECLARE @top_n      integer = 20;

SELECT TOP (@top_n)
    database_name   = DB_NAME(),
    query_sql_text  = TRY_CAST(qt.query_sql_text AS nvarchar(200)),
    object_name     = OBJECT_NAME(q.object_id),
    query_id        = q.query_id,
    query_hash      = q.query_hash,
    plan_count      = COUNT(DISTINCT p.plan_id),
    total_executions    = SUM(rs.count_executions),
    avg_duration_ms     = SUM(rs.avg_duration) / NULLIF(SUM(rs.count_executions), 0) / 1000.0,
    avg_cpu_ms          = SUM(rs.avg_cpu_time) / NULLIF(SUM(rs.count_executions), 0) / 1000.0,
    avg_logical_reads   = SUM(rs.avg_logical_io_reads) / NULLIF(SUM(rs.count_executions), 0),
    avg_physical_reads  = SUM(rs.avg_physical_io_reads) / NULLIF(SUM(rs.count_executions), 0),
    avg_logical_writes  = SUM(rs.avg_logical_io_writes) / NULLIF(SUM(rs.count_executions), 0),
    avg_memory_grant_mb = SUM(rs.avg_query_max_used_memory) / NULLIF(SUM(rs.count_executions), 0) * 8.0 / 1024.0,
    max_duration_ms     = MAX(rs.max_duration) / 1000.0,
    min_duration_ms     = MIN(rs.min_duration) / 1000.0,
    max_cpu_ms          = MAX(rs.max_cpu_time) / 1000.0,
    min_cpu_ms          = MIN(rs.min_cpu_time) / 1000.0,
    last_execution_time = MAX(rs.last_execution_time),
    is_forced_plan      = MAX(CASE WHEN p.is_forced_plan = 1 THEN 1 ELSE 0 END),
    force_failure_count = MAX(p.force_failure_count),
    last_force_failure_reason_desc = MAX(p.last_force_failure_reason_desc),
    aborted_count       = SUM(CASE WHEN rs.execution_type = 3 THEN rs.count_executions ELSE 0 END),
    exception_count     = SUM(CASE WHEN rs.execution_type = 4 THEN rs.count_executions ELSE 0 END),
    avg_tempdb_mb       = SUM(rs.avg_tempdb_space_used) / NULLIF(SUM(rs.count_executions), 0) * 8.0 / 1024.0
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
    ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time >= @start_date
  AND rs.last_execution_time <  @end_date
  AND rs.execution_type IN (0, 3, 4) -- 0=regular, 3=aborted (client-initiated), 4=exception
GROUP BY qt.query_sql_text, q.query_id, q.query_hash, q.object_id
HAVING SUM(rs.count_executions) > 0
ORDER BY SUM(rs.avg_cpu_time * rs.count_executions) DESC;