SQL Server Query Store: Find Plan Regressions and Force a Good Plan

When a query suddenly gets slower because SQL Server picked a new execution plan, Query Store lets you fix it without touching application code: open the Regressed Queries report (or query sys.query_store_runtime_stats) to find the query whose duration jumped, compare its plans, and run sp_query_store_force_plan with the query_id and the plan_id of the plan that performed well. Then check sys.query_store_plan for forcing failures, and consider turning on automatic plan correction (FORCE_LAST_GOOD_PLAN) so the engine does the same thing itself and reverts it if the forced plan isn't better.

Who this is for and what you will have at the end

This guide is for DBAs and developers on SQL Server 2016 or later, Azure SQL Database or Azure SQL Managed Instance who are dealing with a query that "was fine yesterday": after a statistics update, an index change, a compatibility level upgrade or a parameter-sensitive recompile.

At the end you will have:

• Query Store confirmed as collecting data, not silently read-only.
• A list of regressed queries with the good and bad plans side by side.
• A forced plan, verified to be in use.
• A monitoring query for forcing failures and a plan to unforce later.
• Automatic plan correction configured where it fits.

How Query Store and plan forcing work

The plan cache only holds the latest plan for a query, and plans get evicted under memory pressure. Query Store keeps a history of queries, their plans and their runtime and wait statistics, aggregated into fixed time intervals. Because it keeps several plans per query (up to max_plans_per_query), it can tell the optimizer to use a specific one again. That is plan forcing.

Some behaviors to understand before you force anything:

• Forcing works like a query hint applied from outside the application. The query is recompiled, and the result is the same or a similar plan to the one you forced, not necessarily identical. In rare cases the performance difference can be large and negative.
• You can only force a plan that Query Store recorded for that query while it was active.
• If forcing fails, an Extended Event is fired and the optimizer compiles the query normally. Nothing errors in the application.
• Forced plans persist across restarts. Manually forced plans should not stay forced forever; the optimizer should eventually be allowed to choose again.

Prerequisites

• Query Store enabled on the database. It is on by default for new databases in Azure SQL Database and Azure SQL Managed Instance, and in READ_WRITE mode for new databases from SQL Server 2022. It is off by default in SQL Server 2016, 2017 and 2019. It can't be enabled for master or tempdb.
• ALTER permission on the database to force or unforce plans.
• VIEW DATABASE STATE (or VIEW DATABASE PERFORMANCE STATE from SQL Server 2022) to read the Query Store catalog views.
• A current version of SQL Server Management Studio for the Query Store reports.
• Enough history: Query Store needs to have captured the period before the regression, otherwise there is no good plan to force.

If Query Store isn't on, enable it now so you have data next time:

ALTER DATABASE [Sales]
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Step 1: Make sure Query Store is actually collecting

Query Store can switch modes on its own, most often to read-only when it runs out of space. Check the state first:

USE [Sales];
SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

If actual_state_desc differs from desired_state_desc, the mode changed automatically. A readonly_reason of 65536 means the size quota was exceeded. Increase MAX_STORAGE_SIZE_MB, or clear old data, then switch back to read-write:

ALTER DATABASE [Sales]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Microsoft also recommends enabling size-based cleanup, a time-based stale query threshold, and QUERY_CAPTURE_MODE = AUTO so Query Store stays below its limit. Avoid SET QUERY_STORE CLEAR during an incident unless you have to: it deletes the history you need to find the good plan.

Step 2: Find the regressed queries

With the SSMS report

In Object Explorer, expand the database, then Query Store, and open Regressed Queries. It lists queries whose metrics got worse in the recent interval compared with history. Choose the metric that matches the symptom: Duration (the default), CPU Time, Logical Reads, Physical Reads, Memory Consumption, Wait Time and others. Select a query to see its plans in the chart; the size of each shape reflects the execution count in the interval.

The Queries With High Variation report is useful when one query swings between fast and slow, which can point to parameter sensitivity.

With T-SQL

This query from the Microsoft documentation returns queries whose average duration more than doubled in the last 48 hours because the plan changed. It compares every pair of runtime statistics intervals for the same query:

SELECT qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    rs1.runtime_stats_id AS runtime_stats_id_1,
    rsi1.start_time AS interval_1,
    p1.plan_id AS plan_1,
    rs1.avg_duration AS avg_duration_1,
    rs2.avg_duration AS avg_duration_2,
    p2.plan_id AS plan_2,
    rsi2.start_time AS interval_2,
    rs2.runtime_stats_id AS runtime_stats_id_2
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p1
    ON q.query_id = p1.query_id
INNER JOIN sys.query_store_runtime_stats AS rs1
    ON p1.plan_id = rs1.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi1
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p2
    ON q.query_id = p2.query_id
INNER JOIN sys.query_store_runtime_stats AS rs2
    ON p2.plan_id = rs2.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi2
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())
    AND rsi2.start_time > rsi1.start_time
    AND p1.plan_id <> p2.plan_id
    AND rs2.avg_duration > 2 * rs1.avg_duration
ORDER BY q.query_id,
    rsi1.start_time,
    rsi2.start_time;

Remove the p1.plan_id <> p2.plan_id condition to include regressions that aren't caused by a plan change. The same documentation page has a second query that compares a recent window (one hour) with a history window (one day) and ranks queries by the extra duration they added.

Step 3: Compare the plans and pick the good one

Once you have a query_id, list its plans with their aggregate runtime statistics:

DECLARE @query_id BIGINT = 48;
 
WITH plan_stats AS (
    SELECT rs.plan_id,
           SUM(rs.count_executions) AS executions,
           ROUND(SUM(rs.avg_duration * rs.count_executions)
                 / NULLIF(SUM(rs.count_executions), 0), 2) AS avg_duration,
           ROUND(SUM(rs.avg_physical_io_reads * rs.count_executions)
                 / NULLIF(SUM(rs.count_executions), 0), 2) AS avg_physical_io_reads
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE p.query_id = @query_id
    GROUP BY rs.plan_id
)
SELECT p.plan_id,
       p.is_forced_plan,
       p.last_compile_start_time,
       p.last_execution_time,
       s.executions,
       s.avg_duration,
       s.avg_physical_io_reads,
       TRY_CAST(p.query_plan AS XML) AS plan_xml
FROM plan_stats AS s
INNER JOIN sys.query_store_plan AS p
    ON p.plan_id = s.plan_id
ORDER BY s.avg_duration;

In SSMS you can select two plans in the Regressed Queries chart and use Compare Plans to see the operator differences, for example a seek replaced by a scan or a nested loops join replaced by a hash join.

Before forcing, decide whether forcing is the right fix:

A plan can also be the difference between a deadlock and no deadlock. If one plan scans the table being updated and the other seeks, forcing the seeking plan can stop the cycle; see capturing and fixing SQL Server error 1205 deadlocks.

Step 4: Force the plan

In the Regressed Queries report, select the query, select the good plan, and choose Force Plan. Or run:

USE [Sales];
EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

The procedure also takes @disable_optimized_plan_forcing (bit, default 0) and, when Query Store for readable secondary replicas is enabled, @replica_group_id so you can force a plan for a specific readable secondary. That feature is in preview; it is available in SQL Server 2025, Azure SQL Database (not the Hyperscale tier) and Azure SQL Managed Instance with the SQL Server 2025 or Always-up-to-date update policy. Run both force and unforce on the primary replica. Arguments must be supplied in the documented order.

Plan forcing is also supported for fast forward and static cursors in SQL Server 2019 and later and in Azure SQL Database.

Step 5: Confirm the forced plan is being used

Forcing only sets a flag. Check that the next compilations actually succeeded:

USE [Sales];
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
JOIN sys.query_store_query AS q ON p.query_id = q.query_id
WHERE p.is_forced_plan = 1;

• plan_forcing_type_desc is MANUAL for plans you forced and AUTO for plans forced by automatic tuning (SQL Server 2017 and later).
• force_failure_count increases only when the query recompiles, not on every execution, and resets to 0 each time the plan is forced again.
• last_force_failure_reason_desc explains a failure:

To be alerted, capture the query_store_plan_forcing_failed Extended Event. In SSMS, the Queries With Forced Plans report lists everything currently forced, and Tracked Queries lets you watch a forced query's executions in near real time.

Then compare the query's runtime statistics in the intervals after forcing with the intervals before the regression, using the Step 3 query.

Step 6: Let the engine do it with automatic plan correction

From SQL Server 2017, the engine detects plan choice regressions itself and publishes them in sys.dm_db_tuning_recommendations, including the regressed plan, the recommended plan and the exact sp_query_store_force_plan call. This query from the documentation extracts the script and estimated gain:

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

estimated_gain is the estimated number of seconds saved by the recommended plan. The DMV is cleared when the Database Engine restarts.

To let the engine apply these recommendations itself:

ALTER DATABASE [Sales]
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

With the option on, the engine forces a recommendation when the estimated CPU gain is more than 10 seconds or when the new plan has more errors than the recommended one. It then monitors the forced plan: if it isn't better than the regressed plan, it is unforced and a new plan is compiled; if it is better, it stays forced until the next recompile, such as a statistics update or schema change. If the instance restarts before a forcing action is verified, that plan is unforced.

Check that the option is really active:

SELECT name, desired_state_desc, actual_state_desc, reason_desc
FROM sys.database_automatic_tuning_options;

reason_desc explains a mismatch: QUERY_STORE_OFF, QUERY_STORE_READ_ONLY, DISABLED (disabled by the system) or NOT_SUPPORTED, which the documentation describes as available only in SQL Server Enterprise edition.

Platform differences:

Microsoft specifically recommends automatic plan correction when you raise a database compatibility level, after capturing a Query Store baseline at the old level.

Step 7: Unforce when the cause is fixed

Forced plans are a mitigation. Once you have fixed the underlying cause (an index, statistics, the query text or parameter handling), remove the forcing and let the optimizer choose:

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;

Keep a list of forced plans with the reason and the date, and review it after each release, index change or compatibility level change.

Things that silently break plan forcing

• Renaming the database. Plans reference objects by three-part names, so forcing fails and every execution recompiles.
• Dropping and re-creating a stored procedure, function or trigger. Query Store creates a new query entry for the same text, so the forced plan no longer applies. Use ALTER instead.
• Dropping or disabling an index the plan uses (NO_INDEX).
• Plans containing bulk insert, references to external tables, distributed queries or full-text operations, elastic queries, dynamic or keyset cursors, or invalid star join specifications can't be forced.

Troubleshooting

Checklist

• Confirm Query Store is in READ_WRITE mode with room to grow.
• Find the regressed query in the Regressed Queries report or with the T-SQL comparison.
• Compare plans and decide whether forcing is the right fix.
• Force with sp_query_store_force_plan and verify force_failure_count stays at 0.
• Compare runtime statistics before and after.
• Enable FORCE_LAST_GOOD_PLAN where supported, and check sys.database_automatic_tuning_options.
• Track forced plans and unforce them once the cause is fixed.

References

• Monitor performance by using the Query Store
• Tune performance with the Query Store
• Best practices for monitoring workloads with Query Store
• sp_query_store_force_plan
• sys.query_store_plan
• Automatic tuning
• sys.database_automatic_tuning_options
• Automatic tuning overview for Azure SQL