SQL Server

How to Find SQL Server Performance Regressions with Query Store

A practical Query Store workflow for SQL Server regressions: compare time windows, find queries that slowed down, inspect plan changes, force plans safely and undo them.

By JaviPublished 13 min readIntermediate

“It was fast last week” is one of the most common SQL Server complaints, and one of the hardest to answer from live DMVs alone. By the time someone looks, the slow query may have finished, the plan may have left the cache, and the server may have restarted. What you need is history: which queries ran, with which plans, and how they performed before and after the change.

Query Store keeps that history inside the database. This article is a practical workflow for using it to investigate a performance regression, from the first symptom to a validated fix:

symptom → evidence → regression → candidate plan → validation → action

The examples use the documented Query Store catalog views and stored procedures, and work on SQL Server 2017 and later, Azure SQL Database and Azure SQL Managed Instance unless noted.

What a performance regression is

A regression is a query, or a workload, that performs worse than it did before for comparable work. That last part matters. A report that takes longer because it now reads ten times more data has not regressed; its workload changed. A regression is a change in efficiency, not just in duration.

Common causes:

  • Plan changes. The optimizer chooses a different plan after a statistics update, a recompile, an index change, a parameter-sensitive compile, a compatibility-level change or an upgrade.
  • Data growth or distribution changes. The same plan is now poor for the data it meets.
  • Code changes. A deployment changed the query text, which Query Store treats as a new query.
  • Resource contention. Blocking, memory pressure or I/O latency make an unchanged plan slower.

Query Store is strongest at the first two, and it helps separate them from the last two.

Why Query Store is the right tool

Query Store captures, per database:

  • Queries and their text (sys.query_store_query, sys.query_store_query_text).
  • Every plan each query has used (sys.query_store_plan), including the showplan XML, compile times, the engine version and compatibility level that produced it.
  • Runtime statistics per plan per time interval (sys.query_store_runtime_stats with sys.query_store_runtime_stats_interval): executions, duration, CPU, logical reads, writes and more.
  • Wait statistics per plan and interval (sys.query_store_wait_stats), when wait capture is enabled.

Durations and CPU times are stored in microseconds, and logical reads in 8 KB pages. Statistics are aggregated per interval (60 minutes by default), so you compare intervals, not individual executions.

The current-state view of active requests and sessions tells you what is happening now. Query Store tells you what changed.

Step 0: make sure Query Store is collecting

Query Store is enabled by default for new Azure SQL databases and, since SQL Server 2022, for new SQL Server databases. Older databases, and databases upgraded from earlier versions, may not have it on. Check before you rely on it:

SQL query: Step 0: make sure Query Store is collectingT-SQL
SELECT
    actual_state_desc,
    desired_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    interval_length_minutes,
    stale_query_threshold_days,
    query_capture_mode_desc,
    size_based_cleanup_mode_desc
FROM sys.database_query_store_options;
T-SQL11 linesRead-only query display. It is not connected to a database.

The result you want is READ_WRITE in both state columns. If actual_state_desc is READ_ONLY while desired_state_desc is READ_WRITE, Query Store stopped collecting, most often because it reached its maximum size. You then have history up to that point and nothing after it, which can make a regression invisible.

A reasonable production configuration:

SQL query: Step 0: make sure Query Store is collecting (2)T-SQL
ALTER DATABASE CURRENT
SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 2048,
    SIZE_BASED_CLEANUP_MODE = AUTO
);
T-SQL8 linesRead-only query display. It is not connected to a database.

QUERY_CAPTURE_MODE = AUTO skips insignificant ad hoc queries, which keeps the store focused. Size the storage for the history you want to keep, and keep SIZE_BASED_CLEANUP_MODE = AUTO so cleanup runs before the store fills. Retention is controlled by the stale query threshold; make sure it covers the comparison windows you need, such as “same day last week”.

Step 1: define the symptom and the windows

Before querying anything, write down:

  • What is slow: a report, an API endpoint, a batch job, or “everything”.
  • When it started: a deployment, an index rebuild, a statistics job, an upgrade, a data load.
  • A baseline window: a period of comparable load when things were normal, ideally the same weekday and hours.
  • An incident window: the same length, after the problem started.

Comparing a quiet Sunday with a busy Monday finds load changes, not regressions. Equal-length windows with similar business activity are what make the comparison meaningful. If the problem is happening right now, start with the SQL Server troubleshooting workflow and active request checks to rule out blocking and saturation first.

Step 2: find queries that became slower

This query compares two windows per query. It weights each interval’s averages by its execution count, so a busy interval counts more than a quiet one, and it reports aborted and failed executions separately. Client timeouts are recorded as aborted executions, so a regression that ends in timeouts can look like fewer slow executions rather than more.

SQL query: Queries that became slowerT-SQL
DECLARE @baseline_start datetimeoffset = '2026-09-28T08:00:00+00:00';
DECLARE @baseline_end   datetimeoffset = '2026-09-28T12:00:00+00:00';
DECLARE @incident_start datetimeoffset = '2026-10-05T08:00:00+00:00';
DECLARE @incident_end   datetimeoffset = '2026-10-05T12:00:00+00:00';

WITH runtime AS (
    SELECT
        p.query_id,
        CASE
            WHEN rsi.start_time >= @incident_start AND rsi.start_time < @incident_end
                THEN 'incident'
            ELSE 'baseline'
        END AS period,
        rs.execution_type,
        rs.count_executions,
        rs.avg_duration,
        rs.avg_cpu_time,
        rs.avg_logical_io_reads
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_runtime_stats_interval AS rsi
        ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE (rsi.start_time >= @baseline_start AND rsi.start_time < @baseline_end)
       OR (rsi.start_time >= @incident_start AND rsi.start_time < @incident_end)
),
per_period AS (
    SELECT
        query_id,
        period,
        SUM(CASE WHEN execution_type = 0 THEN count_executions ELSE 0 END) AS regular_executions,
        SUM(CASE WHEN execution_type <> 0 THEN count_executions ELSE 0 END) AS aborted_or_failed,
        SUM(CASE WHEN execution_type = 0 THEN avg_duration * count_executions END)
            / NULLIF(SUM(CASE WHEN execution_type = 0 THEN count_executions END), 0) / 1000.0 AS avg_duration_ms,
        SUM(CASE WHEN execution_type = 0 THEN avg_cpu_time * count_executions END)
            / NULLIF(SUM(CASE WHEN execution_type = 0 THEN count_executions END), 0) / 1000.0 AS avg_cpu_ms,
        SUM(CASE WHEN execution_type = 0 THEN avg_logical_io_reads * count_executions END)
            / NULLIF(SUM(CASE WHEN execution_type = 0 THEN count_executions END), 0) AS avg_logical_reads
    FROM runtime
    GROUP BY query_id, period
)
SELECT TOP (25)
    i.query_id,
    b.regular_executions AS baseline_executions,
    i.regular_executions AS incident_executions,
    i.aborted_or_failed AS incident_aborted_or_failed,
    CAST(b.avg_duration_ms AS decimal(18, 2)) AS baseline_avg_ms,
    CAST(i.avg_duration_ms AS decimal(18, 2)) AS incident_avg_ms,
    CAST(b.avg_cpu_ms AS decimal(18, 2)) AS baseline_cpu_ms,
    CAST(i.avg_cpu_ms AS decimal(18, 2)) AS incident_cpu_ms,
    CAST(b.avg_logical_reads AS decimal(18, 0)) AS baseline_reads,
    CAST(i.avg_logical_reads AS decimal(18, 0)) AS incident_reads,
    CAST((i.avg_duration_ms - b.avg_duration_ms) * i.regular_executions / 1000.0 AS decimal(18, 1))
        AS additional_seconds,
    LEFT(qt.query_sql_text, 200) AS query_text_start
FROM per_period AS i
INNER JOIN per_period AS b
    ON b.query_id = i.query_id
   AND b.period = 'baseline'
INNER JOIN sys.query_store_query AS q
    ON q.query_id = i.query_id
INNER JOIN sys.query_store_query_text AS qt
    ON qt.query_text_id = q.query_text_id
WHERE i.period = 'incident'
  AND i.avg_duration_ms > 1.5 * b.avg_duration_ms
ORDER BY additional_seconds DESC;
T-SQL66 linesRead-only query display. It is not connected to a database.

Notes

  • lines 1–4The two windows to compare. Give them the same length and time of day.
  • lines 33–34Query Store stores durations in microseconds: dividing by 1000.0 gives milliseconds.
  • lines 53–54The extra time the slowdown cost during the incident window: the list is ranked by it.
  • line 65Keeps queries at least 50% slower on average. Change 1.5 to widen or narrow the list.

Read the output in this order:

  • additional_seconds ranks queries by the extra time they added to the incident window. A query that got 3× slower but runs twice a day matters less than one that got 30% slower and runs ten thousand times an hour.
  • Duration vs CPU vs reads. If CPU and reads rose with duration, the query is doing more work, which suggests a plan or data change. If duration rose but CPU and reads did not, the query is waiting, which points at blocking, I/O or memory rather than the plan.
  • Executions. A large change in execution count is a workload change. Treat it separately from per-execution regressions.
  • Aborted or failed. A rise here can mean timeouts. Check whether the slow executions were cut off by the client.

Queries that appear only in the incident window are not in this result, because they have no baseline. They are often new code or new query text after a deployment; list them separately if the deployment is a suspect.

Step 3: check whether the plan changed

For each candidate query, look at its plans and their performance over time:

SQL query: Plans of one query over timeT-SQL
DECLARE @query_id bigint = 4711;

SELECT
    p.plan_id,
    p.is_forced_plan,
    p.compatibility_level,
    p.engine_version,
    p.last_compile_start_time,
    rsi.start_time AS interval_start,
    rs.execution_type_desc,
    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,
    CAST(rs.avg_logical_io_reads AS decimal(18, 0)) AS avg_logical_reads,
    CAST(rs.max_duration / 1000.0 AS decimal(18, 2)) AS max_duration_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.start_time >= DATEADD(day, -14, SYSUTCDATETIME())
ORDER BY rsi.start_time, p.plan_id;
T-SQL23 linesRead-only query display. It is not connected to a database.

Notes

  • line 1Replace 4711 with a query_id from the previous result.

Three patterns are common:

  1. One plan before, another plan after, and the new plan is consistently worse. This is a classic plan regression, and the best candidate for plan forcing.
  2. The same plan before and after, but it got slower. The plan did not change; the data or the environment did. Look at table growth, statistics, blocking and resource pressure. Table sizes and the performance tuning checklist are the next stops.
  3. Several plans alternating, each good for some executions and bad for others. That usually means parameter sensitivity: a plan compiled for one parameter value is reused for very different values. Forcing a single plan may help some callers and hurt others.

On SQL Server 2022 and later, parameter sensitive plan optimization can store several variant plans for one parameterized query. sys.query_store_plan.plan_type_desc distinguishes dispatcher and variant plans, and sys.query_store_query_variant links them. Take that into account before concluding that a query has “too many plans”.

Step 4: compare the candidate plans

Pull the plans and compare them in SQL Server Management Studio or another plan viewer:

SQL query: Step 4: compare the candidate plansT-SQL
SELECT
    plan_id,
    query_id,
    last_compile_start_time,
    TRY_CONVERT(xml, query_plan) AS query_plan_xml
FROM sys.query_store_plan
WHERE plan_id IN (82, 97);
T-SQL7 linesRead-only query display. It is not connected to a database.

TRY_CONVERT returns NULL for the rare plans too deeply nested to convert to xml; in that case save the query_plan text with a .sqlplan extension and open it directly.

When comparing, look for the operator that changed the cost profile: a scan where there used to be a seek, a nested loop over a large estimated input, a different join order, a missing parallelism, or a large gap between estimated and actual rows if you also have an actual plan. Note the compile time and compatibility level of each plan; a plan compiled right after an upgrade or a compatibility-level change is a strong clue.

The built-in Regressed Queries and Queries With Forced Plans reports in Management Studio show the same data visually. They are a good way to confirm what the T-SQL told you.

Step 5: decide whether to force a plan

If a previous plan was consistently better for the same workload, forcing it is a fast, reversible mitigation:

SQL query: Step 5: decide whether to force a planT-SQL
EXEC sys.sp_query_store_force_plan @query_id = 4711, @plan_id = 82;
T-SQL1 lineRead-only query display. It is not connected to a database.

Forcing tells the optimizer to use that plan for the query. It does not change the query text, and it takes effect on the next compilation.

Do not force a plan when:

  • The plan did not change. Forcing the same plan fixes nothing; the cause is elsewhere.
  • The old plan was only better for some parameter values. You trade one group of slow callers for another.
  • The old plan depends on an index or object that has since changed. Forcing can fail, and the optimizer falls back to normal compilation.
  • You have not compared equivalent workloads. A plan that looked better on a quiet day may not be better under load.
  • The real fix is cheap and available, such as correcting a statistics problem or an obvious missing index. Forcing should not hide a cause you can remove.

Forcing is a mitigation. Record why you forced the plan, who did it, and when it will be reviewed.

Step 6: validate the result

After forcing, wait for the query to recompile and run under representative load, then repeat the Step 2 comparison with a new “after” window. You want to see lower duration, CPU and reads per execution, with comparable execution counts and no rise in aborted executions.

Also confirm that forcing actually succeeds. A forced plan is not guaranteed to be used; if the optimizer cannot produce it (for example because an index it uses was dropped), forcing fails and the query compiles normally:

SQL query: Step 6: validate the resultT-SQL
SELECT
    p.plan_id,
    p.query_id,
    q.object_id AS containing_object_id,
    p.plan_forcing_type_desc,
    p.force_failure_count,
    p.last_force_failure_reason_desc
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q
    ON q.query_id = p.query_id
WHERE p.is_forced_plan = 1;
T-SQL11 linesRead-only query display. It is not connected to a database.

force_failure_count increases only when the query is recompiled, not on every execution. Check this list regularly, not only after an incident. Renaming the database, dropping an index the plan uses, or schema changes to referenced objects can all break forcing silently.

Step 7: undo or replace the forced plan

Unforce a plan when it stops helping, when a durable fix is in place, or when validation shows it made things worse:

SQL query: Step 7: undo or replace the forced planT-SQL
EXEC sys.sp_query_store_unforce_plan @query_id = 4711, @plan_id = 82;
T-SQL1 lineRead-only query display. It is not connected to a database.

Plan forcing is a holding pattern. The durable fix is usually one of: better statistics maintenance, an index that supports the predicate, a query rewrite, separating genuinely different workload shapes, or a targeted hint. On SQL Server 2022 and later and in Azure SQL, Query Store hints (sys.sp_query_store_set_hints and sys.sp_query_store_clear_hints) can apply a query hint to a query without changing application code, which is often a better long-term control than forcing a whole plan.

Automatic plan correction

SQL Server 2017 and later can do the plan-regression part of this workflow automatically. With automatic tuning enabled, the engine detects plan choice regressions, forces the last known good plan, verifies that it helps, and reverts it if it does not:

SQL query: Automatic plan correctionT-SQL
ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
T-SQL2 linesRead-only query display. It is not connected to a database.

Even without enabling it, you can read the recommendations it would make:

SQL query: Automatic plan correction (2)T-SQL
SELECT
    name,
    reason,
    score,
    JSON_VALUE(state, '$.currentValue') AS current_state,
    JSON_VALUE(details, '$.implementationDetails.script') AS script
FROM sys.dm_db_tuning_recommendations;
T-SQL7 linesRead-only query display. It is not connected to a database.

sys.dm_db_tuning_recommendations is not persisted across restarts, and automatic correction handles only plan choice regressions. It does not fix data growth, blocking or resource pressure. Treat it as a helpful first responder, and keep the manual workflow for everything else.

Storage and capture considerations

  • Size it for the history you need. A store that fills up switches to read-only and stops recording exactly when you need it.
  • Capture mode. AUTO is the practical default. ALL captures every query and can bloat the store on ad hoc workloads; CUSTOM lets you set thresholds.
  • Non-parameterized workloads create a new query for every literal value, which fills the store and makes comparison hard. Parameterize at the application, or consider forced parameterization where appropriate.
  • Interval length trades detail for space. Shorter intervals help pinpoint when a change happened; 60 minutes is a sensible default for most systems.
  • Read access. Query text can contain sensitive values. Control who can read Query Store views in production.

Common mistakes

  • Comparing windows with different load, then calling a workload change a regression.
  • Using unweighted averages across intervals, so a quiet interval counts as much as a busy one.
  • Forcing a plan without checking that the plan changed.
  • Forcing a plan for a parameter-sensitive query and moving the pain to other callers.
  • Never checking forced plans again, so failures go unnoticed for months.
  • Ignoring aborted executions, which hides timeouts.
  • Letting Query Store go read-only and investigating a period it never recorded.

Recommendation

Make Query Store part of normal operations, not an emergency tool: keep it READ_WRITE, size it for at least a few weeks of history, and know how to compare two windows before you need to.

When a regression appears, follow the evidence: confirm comparable windows, rank queries by the extra time they added, check whether the plan changed, compare candidate plans, force only when one plan is clearly better for the same workload, validate the result, and schedule the durable fix. Return to the performance tuning workflow for the full investigation order, use real-time monitoring for current activity, and keep the Query Store quick reference nearby.

Tags

  • SQL Server
  • Performance
  • Troubleshooting
  • Query Store