The query was fine yesterday, then a new plan made it crawl. Automatic plan correction can spot that regression and recommend the earlier plan, but the recommendation deserves a careful read before you enable a database-wide setting.
Automatic Plan Correction Starts With Query Store
SQL Server needs Query Store history to recognize a plan regression. Check that Query Store is on, collecting, and large enough to retain useful history. A recommendation cannot compare yesterday's good plan with today's poor one if yesterday's evidence has already been cleaned out. I check this before I blame a parameter or add another index. A sudden slowdown has several possible causes, and a plan change is only one.
SELECT actual_state_desc, desired_state_desc,
current_storage_size_mb, max_storage_size_mb,
readonly_reason
FROM sys.database_query_store_options;Run this in the affected database. If actual_state_desc is READ_ONLY, check the reason before changing a tuning option. Capture the query text and plan IDs while the evidence is present. Query Store has a retention policy, and the tuning recommendation DMV is not a permanent audit log. Save the output you need for investigation.
Read the Recommendation as Data
The recommendations view exposes the reason, state, type, and a JSON document called details. That JSON includes plan IDs and estimated improvement data from the detection point. Do not assume that its CPU figures are current runtime results. The query below reads the IDs and current status. It also leaves the full JSON visible, because the payload is useful when a path differs by engine build.
SELECT reason, state, type,
TRY_CONVERT(bigint,
JSON_VALUE(details, '$.planForceDetails.regressedPlanId'))
AS regressed_plan_id,
TRY_CONVERT(bigint,
JSON_VALUE(details, '$.planForceDetails.recommendedPlanId'))
AS recommended_plan_id,
TRY_CONVERT(float, JSON_VALUE(details,
'$.planForceDetails.regressedPlanCpuTimeAverage'))
AS regressed_cpu_us,
TRY_CONVERT(float, JSON_VALUE(details,
'$.planForceDetails.recommendedPlanCpuTimeAverage'))
AS recommended_cpu_us,
details
FROM sys.dm_db_tuning_recommendations
WHERE type = N'FORCE_LAST_GOOD_PLAN';The CPU averages in the JSON are microseconds per execution at detection time. Compare the execution counts beside them before calling one plan faster. Averages from two different parameter mixes can mislead. Query Store runtime intervals provide a fresh check after the recommendation appears. If a query runs both short and long forms, compare like with like. A lower average CPU value can coexist with worse duration when waits change. Keep the JSON as a timestamped lead, then use the actual Query Store plan IDs to inspect runtime data.
If those JSON paths return NULL, inspect details instead of treating NULL as plan zero. A recommendation has a life cycle. The engine can detect a regression, try a forced plan, verify results, or abandon the correction. Read the state and reason together. The state explains what the engine has actually done, while the reason explains why it reached that point.
Compare the Two Plans in Query Store
Use the plan IDs from details to open both plans in Query Store. Look at the predicates, join order, memory grant, and actual runtime history. The recommendation's CPU comparison is a trigger for investigation, not a license to ignore reads, duration, or the application result. A plan that saves CPU but spills heavily or behaves badly for a different parameter can still be the wrong fix.
DECLARE @RegressedPlanId bigint = 0;
DECLARE @RecommendedPlanId bigint = 0;
SELECT plan_id, query_id, is_forced_plan,
force_failure_count, last_force_failure_reason_desc,
query_plan
FROM sys.query_store_plan
WHERE plan_id IN (@RegressedPlanId, @RecommendedPlanId);Replace the zero placeholders with the IDs from the recommendation. Open query_plan in SSMS, then compare the same query under representative parameter values. I also check whether a recent statistics update, index change, or compatibility-level change explains the switch. If the underlying issue remains, forcing the older plan can buy time while the real fix is tested.
Enable Automatic Plan Correction Deliberately
FORCE_LAST_GOOD_PLAN lets SQL Server try to force a prior plan after it detects a qualifying regression. It is a database setting, not a command for one statement. Change it in a controlled window and record the old value. The command below names the database explicitly, so confirm that name first. A database-wide automatic correction policy needs the same change review as any other performance feature.
SELECT DB_NAME() AS target_database;
-- Replace YourDatabase with the confirmed database name.
ALTER DATABASE [YourDatabase]
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);The statement changes a setting, so run it only in the intended environment. If you need one specific plan while you investigate, Query Store's manual force operation gives a narrower control. Do not enable both approaches blindly for the same query. What problem are you asking the engine to correct, and what observation will tell you that it succeeded?
Verify the Correction Held
After the workload runs again, check the tuning option and the recommendation state. Compare Query Store runtime statistics for the same time windows and parameter patterns. A state such as LastGoodPlanForced tells you an action occurred. It does not replace a check of duration, CPU, reads, and error reports. A forced plan can fail after a schema change, and verification can be aborted by a restart or Query Store cleanup.
SELECT name, desired_state_desc, actual_state_desc,
reason_desc
FROM sys.database_automatic_tuning_options
WHERE name = N'FORCE_LAST_GOOD_PLAN';
SELECT reason, state, type
FROM sys.dm_db_tuning_recommendations
WHERE type = N'FORCE_LAST_GOOD_PLAN';Keep the original plan IDs and a before-and-after Query Store report. If the good plan is no longer good for current data, investigate estimates and indexes instead of treating the force as permanent. Automatic correction is a guardrail. It still needs an owner who reads the dashboard. The engine can make a recommendation, but it cannot attend your change review meeting.
Know When to Undo Automatic Plan Correction
If the option introduces an unwanted correction, turn it off for that database and document why. Check manual plan forces separately; changing the automatic tuning option does not explain every forced plan in Query Store. If the recommendation disappears, use saved Query Store evidence to reconstruct what happened. I prefer a small, reversible change with a clear measurement window over a server-wide reaction to one bad morning.
A useful closeout note names the affected query, the two plans, the parameter values tested, and the runtime comparison. It also names the date when the force will be reviewed. Leave no mystery for the next DBA. A setting that quietly fixed a regression can quietly become obsolete after the workload changes. Read the evidence again after each major index or schema change, and let measured behavior decide whether the guardrail stays.
Related reading on this blog: Create Efficient Query Plans Using Query Store: Analyzing SQL Server Query Plans: Part 3 and Parameter Sniffing and Bad Plan.
A plan recommendation is not an automatic fix, it is a candidate the workload must confirm.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
