Query Store: Your First Stop

Query Store is a flight recorder built into the database. It captures query text, every plan the optimizer chose, and per-interval runtime statistics — duration, CPU, reads, memory grants, executions. Unlike the plan cache, it survives restarts, evictions, and failovers.

In SQL Server 2022 it is on by default for new databases. On an older database, or one restored from an older instance, it may be off. Check first:

SELECT  DB_NAME()                AS database_name,
        actual_state_desc,
        readonly_reason,
        current_storage_size_mb,
        max_storage_size_mb,
        query_capture_mode_desc,
        stale_query_threshold_days
FROM sys.database_query_store_options;

actual_state_desc should be READ_WRITE. If it says OFF, or READ_ONLY with a non-zero readonly_reason (usually "storage full"), you are not collecting anything.

Turning it on is a normal ALTER DATABASE change, but it does write to the database — get the usual approval before running this on production:

ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;

ALTER DATABASE [YourDatabase] SET QUERY_STORE (
    OPERATION_MODE            = READ_WRITE,
    QUERY_CAPTURE_MODE        = AUTO,          -- skips trivial one-off queries
    MAX_STORAGE_SIZE_MB       = 2048,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES   = 60,
    SIZE_BASED_CLEANUP_MODE   = AUTO
);

Once it is on, you have to wait for the workload to run before it tells you anything. That is another reason to turn it on early rather than at the moment of crisis.

Top resource consumers

The SSMS built-in reports (Object Explorer → your database → Query Store) are genuinely good, and "Top Resource Consuming Queries" is where to start. But knowing the underlying query means you can run it anywhere, filter it, and paste it into a ticket.

-- Top 25 queries by total duration over the last 24 hours
SELECT TOP (25)
       qsq.query_id,
       qsp.plan_id,
       SUBSTRING(qst.query_sql_text, 1, 300)        AS query_snippet,
       SUM(rs.count_executions)                      AS executions,
       SUM(rs.avg_duration * rs.count_executions)/1000.0     AS total_duration_ms,
       AVG(rs.avg_duration)/1000.0                   AS avg_duration_ms,
       AVG(rs.avg_cpu_time)/1000.0                   AS avg_cpu_ms,
       AVG(rs.avg_logical_io_reads)                  AS avg_logical_reads,
       AVG(rs.avg_query_max_used_memory)             AS avg_memory_grant_pages
FROM sys.query_store_runtime_stats     AS rs
JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan              AS qsp ON qsp.plan_id  = rs.plan_id
JOIN sys.query_store_query             AS qsq ON qsq.query_id = qsp.query_id
JOIN sys.query_store_query_text        AS qst ON qst.query_text_id = qsq.query_text_id
WHERE rsi.start_time >= DATEADD(HOUR, -24, SYSDATETIMEOFFSET())
GROUP BY qsq.query_id, qsp.plan_id, SUBSTRING(qst.query_sql_text, 1, 300)
ORDER BY total_duration_ms DESC;

Sort by total duration first, not average. A query taking 200 ms that runs 400,000 times costs the server more than your 40-second report, and if you are hunting general slowness that is where the win is. When you are hunting one specific slow report, sort by average and search the text.

Finding your report's query

You rarely know the query_id up front. Search by a distinctive fragment of the SQL — a table name, a column, a comment:

SELECT  qsq.query_id,
        qsp.plan_id,
        qsp.is_forced_plan,
        qst.query_sql_text
FROM sys.query_store_query_text AS qst
JOIN sys.query_store_query      AS qsq ON qsq.query_text_id = qst.query_text_id
JOIN sys.query_store_plan       AS qsp ON qsp.query_id = qsq.query_id
WHERE qst.query_sql_text LIKE '%FactSalesReport%'    -- placeholder: a table or proc your report hits
ORDER BY qsq.query_id, qsp.plan_id;

If one query_id has several plan_id values, note that. It means the optimizer has produced more than one plan for the same text, which is the fingerprint of parameter sniffing (Module 5).

Regressed queries: same query, new plan, worse

This is the single most useful thing Query Store does, because it answers "what changed?" without anyone having to remember.

-- Queries whose average duration got worse after a plan change
WITH per_plan AS (
    SELECT  qsp.query_id,
            qsp.plan_id,
            MIN(rsi.start_time)          AS first_seen,
            SUM(rs.count_executions)     AS execs,
            SUM(rs.avg_duration * rs.count_executions)
              / NULLIF(SUM(rs.count_executions),0) / 1000.0 AS avg_ms
    FROM sys.query_store_runtime_stats AS rs
    JOIN sys.query_store_runtime_stats_interval AS rsi
         ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    JOIN sys.query_store_plan AS qsp ON qsp.plan_id = rs.plan_id
    WHERE rsi.start_time >= DATEADD(DAY, -14, SYSDATETIMEOFFSET())
    GROUP BY qsp.query_id, qsp.plan_id
)
SELECT  p.query_id,
        COUNT(*)          AS distinct_plans,
        MIN(p.avg_ms)     AS best_plan_avg_ms,
        MAX(p.avg_ms)     AS worst_plan_avg_ms,
        MAX(p.avg_ms) / NULLIF(MIN(p.avg_ms),0) AS regression_factor
FROM per_plan AS p
GROUP BY p.query_id
HAVING COUNT(*) > 1
   AND MAX(p.avg_ms) / NULLIF(MIN(p.avg_ms),0) > 3    -- at least 3x worse on the bad plan
ORDER BY regression_factor DESC;

A query with two plans, one averaging 300 ms and one averaging 40 seconds, is not a query that needs an index. It is a query that needs the right plan, reliably.

Forcing a plan — a real fix, and a temporary one

If the good plan exists in history, you can pin it:

EXEC sp_query_store_force_plan @query_id = 1234, @plan_id = 5678;

-- and to release it later
EXEC sp_query_store_unforce_plan @query_id = 1234, @plan_id = 5678;

This is a legitimate emergency lever: it restores yesterday's performance in seconds without a deployment. It is not a permanent answer, because a forced plan can stop being appropriate as data grows, and forcing can silently fail (check sys.query_store_plan.is_forced_plan and last_force_failure_reason_desc). Use it to buy time while you fix the underlying cause.

Proving the fix

This is the part people skip. Without a before and after, you do not actually know whether you fixed anything. After you deploy a change, come back to the same query_id and compare intervals:

SELECT  CAST(rsi.start_time AS date)  AS day,
        qsp.plan_id,
        SUM(rs.count_executions)      AS execs,
        AVG(rs.avg_duration)/1000.0   AS avg_ms,
        AVG(rs.avg_logical_io_reads)  AS avg_reads
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS qsp ON qsp.plan_id = rs.plan_id
WHERE qsp.query_id = 1234                      -- placeholder: your query_id
  AND rsi.start_time >= DATEADD(DAY, -14, SYSDATETIMEOFFSET())
GROUP BY CAST(rsi.start_time AS date), qsp.plan_id
ORDER BY day, plan_id;

"Average duration went from 41 seconds to 1.2 seconds and logical reads from 2.1 million to 8,400, measured over a week of real production runs" is an entirely different statement from "it feels faster now."