/sqlquerystore-review

name: sqlquerystore-review
description: Analyze SQL Server Query Store data to identify regressed queries, plan instability, top resource consumers, query-level wait patterns, configuration issues, and SQL 2019/2022 IQP/PSP/DOP/CE feedback signals. Applies 32 checks (Q1–Q32). Use when a user pastes Query Store DMV output or asks about workload performance trends.
triggers:
  - /sqlquerystore-review
  - /qs-review
  - /query-store

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;

**Query B — Wait Stats Per Query (SQL 2017+)**

-- Requires Query Store wait stats capture enabled:
-- ALTER DATABASE CURRENT SET QUERY_STORE = ON (WAIT_STATS_CAPTURE_MODE = ON);

SELECT TOP 20
    ws.wait_category_desc,
    query_sql_text = TRY_CAST(qt.query_sql_text AS nvarchar(200)),
    q.query_hash,
    total_wait_time_ms   = SUM(ws.total_query_wait_time_ms),
    avg_wait_time_ms     = AVG(ws.avg_query_wait_time_ms),
    wait_category_rank   = ROW_NUMBER() OVER (PARTITION BY q.query_hash ORDER BY SUM(ws.total_query_wait_time_ms) DESC)
FROM sys.query_store_wait_stats AS ws
JOIN sys.query_store_plan AS p
    ON ws.plan_id = p.plan_id
JOIN sys.query_store_query AS q
    ON p.query_id = q.query_id
JOIN sys.query_store_query_text AS qt
    ON q.query_text_id = qt.query_text_id
WHERE ws.last_execution_time >= DATEADD(DAY, -7, GETUTCDATE())
GROUP BY ws.wait_category_desc, qt.query_sql_text, q.query_hash
ORDER BY total_wait_time_ms DESC;

**Query C — Query Store Configuration**

SELECT
    database_name            = DB_NAME(),
    desired_state_desc,
    actual_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    flush_interval_seconds,
    interval_length_minutes,
    max_plans_per_query,
    st