Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: PerformanceDifficulty: IntermediateUpdated

Query Store Quick Reference

Use SQL Server Query Store to find regressions, compare plans, analyze runtime history, force or unforce plans safely, and understand when Query Store data can mislead you.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Check whether Query Store is enabled
  5. 2. Find top queries by average duration
  6. 3. Find top queries by total CPU
  7. 4. Find top queries by logical reads
  8. 5. Show all plans for one query
  9. 6. Compare runtime by plan
  10. 7. Compare a query before and after a change window
  11. 8. Force a known-good plan
  12. 9. Unforce a plan
  13. 10. Find queries with multiple plans
  14. 11. Show recent runtime history for one query
  15. What the result means
  16. What to look for
  17. Same query, different plan, major runtime difference
  18. Duration increases but CPU does not
  19. CPU and logical reads both increase
  20. One plan dominates only after a deployment
  21. High average duration with very low execution count
  22. Production notes
  23. Query Store is historical, but not infinite history
  24. Query Store timestamps are stored in UTC-oriented metadata
  25. Runtime stats are aggregated
  26. Weighted averages are better than averaging averages
  27. Plan forcing is operationally powerful
  28. Query Store can be read-only
  29. Common mistakes
  30. 1. Averaging avg_duration across intervals directly
  31. 2. Assuming multiple plans means parameter sniffing
  32. 3. Forcing the fastest-looking plan without context
  33. 4. Treating plan forcing as the final fix
  34. 5. Ignoring server-wide conditions
  35. 6. Comparing UTC timestamps to local times without conversion
  36. 7. Assuming Query Store has complete history
  37. Next step
  38. Related deep dives

Problem

A query was fast yesterday and slow today, a deployment changed performance, or an intermittent issue has already disappeared before you could inspect the live workload.

sys.dm_exec_requests only shows what is running now. Query Store helps you answer historical questions:

  • Which queries consumed the most CPU or duration over time?
  • Which query regressed?
  • Did SQL Server switch execution plans?
  • Which plan was faster?
  • When did the regression begin?
  • Can a known-good plan be forced temporarily?
  • Is the problem isolated to one query or part of a broader workload change?

Query Store is most useful when you need history, plan comparison, and regression analysis.

Quick answer

Start with Query Store enabled and use the built-in views to find expensive queries by recent runtime.

SELECT TOP (20)
    q.query_id,
    qt.query_sql_text,
    p.plan_id,
    rs.count_executions,
    CAST(rs.avg_duration / 1000.0 AS decimal(18,2)) AS avg_duration_ms,
    CAST(rs.avg_cpu_time / 1000.0 AS decimal(18,2)) AS avg_cpu_ms,
    rs.avg_logical_io_reads,
    rsi.start_time,
    rsi.end_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
    rsi.end_time > DATEADD(hour, -1, SYSUTCDATETIME())
ORDER BY
    rs.avg_duration DESC;

Use this as a starting point, then group by query and plan to compare historical behavior.

Code / commands

1. Check whether Query Store is enabled

SELECT
    actual_state_desc,
    desired_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    query_capture_mode_desc,
    size_based_cleanup_mode_desc,
    stale_query_threshold_days
FROM sys.database_query_store_options;

What you usually want:

  • actual_state_desc = READ_WRITE
  • enough free Query Store storage
  • a capture mode appropriate for the workload

If Query Store is READ_ONLY, do not assume collection is working normally.


2. Find top queries by average duration

SELECT TOP (20)
    q.query_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS executions,
    CAST(
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_duration_ms
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
GROUP BY
    q.query_id,
    qt.query_sql_text
ORDER BY
    weighted_avg_duration_ms DESC;

Weighting by execution count is important.

A plain average of averages can misrepresent workload when runtime intervals contain very different execution counts.


3. Find top queries by total CPU

SELECT TOP (20)
    q.query_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS executions,
    CAST(
        SUM(rs.avg_cpu_time * rs.count_executions)
        / 1000.0
        AS decimal(18,2)
    ) AS total_cpu_ms
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
GROUP BY
    q.query_id,
    qt.query_sql_text
ORDER BY
    total_cpu_ms DESC;

This is often more useful than average CPU when you want to know which query consumed the most CPU overall.


4. Find top queries by logical reads

SELECT TOP (20)
    q.query_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS executions,
    CAST(
        SUM(rs.avg_logical_io_reads * rs.count_executions)
        AS decimal(18,2)
    ) AS total_logical_reads
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
GROUP BY
    q.query_id,
    qt.query_sql_text
ORDER BY
    total_logical_reads DESC;

High logical reads can reveal workload that is expensive even when duration looks acceptable because data is already cached.


5. Show all plans for one query

DECLARE @query_id bigint = 123;

SELECT
    p.query_id,
    p.plan_id,
    p.is_forced_plan,
    p.force_failure_count,
    p.last_force_failure_reason_desc,
    p.last_execution_time,
    p.query_plan
FROM sys.query_store_plan AS p
WHERE
    p.query_id = @query_id
ORDER BY
    p.plan_id;

Use this when you suspect plan regression.

A query with multiple plans is not automatically a problem.

The important question is whether different plans correlate with materially different runtime behavior.


6. Compare runtime by plan

DECLARE @query_id bigint = 123;

SELECT
    p.plan_id,
    p.is_forced_plan,
    SUM(rs.count_executions) AS executions,

    CAST(
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_duration_ms,

    CAST(
        SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_cpu_ms,

    CAST(
        SUM(rs.avg_logical_io_reads * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        AS decimal(18,2)
    ) AS weighted_avg_logical_reads,

    MIN(rsi.start_time) AS first_interval,
    MAX(rsi.end_time) AS last_interval
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
    p.query_id = @query_id
GROUP BY
    p.plan_id,
    p.is_forced_plan
ORDER BY
    weighted_avg_duration_ms DESC;

This is one of the most useful Query Store queries for plan-regression analysis.


7. Compare a query before and after a change window

DECLARE @query_id bigint = 123;
DECLARE @change_time datetimeoffset = '2026-10-04T14:00:00-04:00';

SELECT
    CASE
        WHEN rsi.end_time <= @change_time THEN 'Before'
        ELSE 'After'
    END AS period,
    p.plan_id,
    SUM(rs.count_executions) AS executions,

    CAST(
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_duration_ms,

    CAST(
        SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_cpu_ms
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
    p.query_id = @query_id
    AND (
        rsi.end_time <= @change_time
        OR rsi.start_time >= @change_time
    )
GROUP BY
    CASE
        WHEN rsi.end_time <= @change_time THEN 'Before'
        ELSE 'After'
    END,
    p.plan_id
ORDER BY
    period,
    p.plan_id;

Use this after:

  • compatibility-level changes
  • deployments
  • index changes
  • statistics changes
  • configuration changes

Be careful with intervals that straddle the change timestamp. The filter above excludes those mixed intervals.


8. Force a known-good plan

Only do this after you have identified a specific query and validated the plan.

EXEC sys.sp_query_store_force_plan
    @query_id = 123,
    @plan_id = 456;

Then verify:

SELECT
    query_id,
    plan_id,
    is_forced_plan,
    force_failure_count,
    last_force_failure_reason_desc
FROM sys.query_store_plan
WHERE
    query_id = 123;

Plan forcing is a mitigation, not necessarily the final fix.


9. Unforce a plan

EXEC sys.sp_query_store_unforce_plan
    @query_id = 123,
    @plan_id = 456;

Unforce when:

  • the root cause is fixed
  • the forced plan no longer fits the workload
  • forcing causes regressions
  • schema or data distribution changed enough that the old plan is no longer appropriate

10. Find queries with multiple plans

SELECT
    q.query_id,
    COUNT(*) AS plan_count
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
GROUP BY
    q.query_id
HAVING
    COUNT(*) > 1
ORDER BY
    plan_count DESC;

This is a discovery query only.

Multiple plans can be normal.

Use runtime evidence to decide whether variation is harmful.


11. Show recent runtime history for one query

DECLARE @query_id bigint = 123;

SELECT
    rsi.start_time,
    rsi.end_time,
    p.plan_id,
    rs.count_executions,
    CAST(rs.avg_duration / 1000.0 AS decimal(18,2)) AS avg_duration_ms,
    CAST(rs.avg_cpu_time / 1000.0 AS decimal(18,2)) AS avg_cpu_ms,
    rs.avg_logical_io_reads
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
    p.query_id = @query_id
ORDER BY
    rsi.start_time DESC,
    p.plan_id;

This is useful for seeing exactly when a query became slower and whether a plan change lines up with the regression.

What the result means

Field Why it matters
query_id Stable Query Store identifier for the query entry.
plan_id Query Store identifier for a captured execution plan.
count_executions Number of executions represented by a runtime-stat row.
avg_duration Average duration for executions in that runtime-stat entry.
avg_cpu_time Average CPU time for executions in the entry.
avg_logical_io_reads Average logical reads associated with the entry.
is_forced_plan Indicates whether Query Store plan forcing is currently active.
force_failure_count Number of times SQL Server failed to force the plan.
last_force_failure_reason_desc Reason for the most recent plan-forcing failure.
runtime_stats_interval_id Groups runtime stats into configured Query Store intervals.
start_time / end_time Boundaries of the runtime interval.

What to look for

Same query, different plan, major runtime difference

This is classic plan-regression evidence.

Validate:

  • the slower period aligns with the new plan
  • execution count is sufficient to compare
  • parameter values/workload are comparable
  • the regression is not caused by blocking or server-wide pressure

Duration increases but CPU does not

Possible causes include:

  • blocking
  • I/O waits
  • memory waits
  • concurrency
  • external resource pressure

Do not assume the plan is the only variable.

CPU and logical reads both increase

This often points toward a less efficient access path.

Inspect the plan for:

  • larger scans
  • different join strategy
  • row-estimate issues
  • lookup amplification
  • missing/changed indexes

One plan dominates only after a deployment

Good candidate for before/after analysis.

Compare:

  • compatibility level
  • parameterization
  • statistics
  • indexes
  • query text
  • optimizer behavior

High average duration with very low execution count

Do not over-prioritize a one-off query without understanding business impact.

Frequency and importance matter.

Production notes

Query Store is historical, but not infinite history

Retention depends on:

  • max storage size
  • stale-query cleanup
  • capture mode
  • read/write state

If the store fills or becomes read-only, collection can stop or degrade.

Query Store timestamps are stored in UTC-oriented metadata

Be careful when comparing Query Store interval timestamps with local deployment or incident times.

Normalize time zones before drawing conclusions.

Runtime stats are aggregated

Query Store does not store every execution as an individual row in the runtime stats views.

It stores aggregated statistics by interval.

That means:

  • outliers can be hidden by averages
  • very different executions can be grouped
  • execution-count weighting matters

Weighted averages are better than averaging averages

When combining intervals, weight by count_executions.

Otherwise, one interval with 1 execution can influence your result as much as another interval with 10,000 executions.

Plan forcing is operationally powerful

Plan forcing can stabilize a regression quickly, but it should be monitored.

A forced plan can later become inappropriate because of:

  • data growth
  • schema changes
  • index changes
  • parameter distribution shifts
  • optimizer changes

Query Store can be read-only

Common causes include reaching configured storage limits or other state transitions.

Always check sys.database_query_store_options before assuming new data is being captured.

Common mistakes

1. Averaging avg_duration across intervals directly

This produces a mathematically misleading result when intervals contain different execution counts.

Use weighted averages.

2. Assuming multiple plans means parameter sniffing

Multiple plans can result from many things.

Treat plan variation as evidence, not a diagnosis.

3. Forcing the fastest-looking plan without context

A plan that is fast for one parameter set may be poor for another.

Validate workload shape before forcing.

4. Treating plan forcing as the final fix

Forcing can be an excellent mitigation.

But you still need to investigate:

  • statistics
  • indexes
  • cardinality estimation
  • query design
  • parameter sensitivity
  • compatibility changes

5. Ignoring server-wide conditions

A slow Query Store interval may coincide with:

  • CPU saturation
  • blocking
  • storage latency
  • memory pressure

Do not attribute every regression to the plan.

6. Comparing UTC timestamps to local times without conversion

This can shift your incident correlation by hours.

Normalize first.

7. Assuming Query Store has complete history

Retention, cleanup, capture mode, and storage limits all affect what remains available.

Next step

Use the pattern you find to choose the next action.

  • Same query + new plan + clear regression → validate and consider temporarily forcing the known-good plan.
  • No plan change + higher duration → inspect blocking, waits, CPU, and I/O conditions.
  • CPU + reads increased with new plan → inspect execution-plan shape, estimates, and indexing.
  • Query Store is read-only → resolve storage/state before relying on fresh data.
  • Very short history window → collect more data before making structural changes.
  • Forced plan is failing → inspect last_force_failure_reason_desc and validate schema/index changes.
  • Problem is intermittent → combine Query Store with live DMVs and monitoring for a fuller picture.

← All cheat sheets