Azure SQL Database Automatic Plan Correction: A Safety Net for Plan Regressions

Your query ran in 80 milliseconds for months. Then, overnight, latency jumps to several seconds. No code changed, no schema changed — but a statistics update nudged the optimizer onto a different execution plan, and the new plan is worse. This is a plan regression, and it's one of the most common causes of "the database got slow for no reason" in Azure SQL Database.

Azure SQL Database has a built-in answer: automatic plan correction (the FORCE_LAST_GOOD_PLAN automatic tuning option). It uses Query Store telemetry to detect when a plan change causes a significant regression in CPU or duration, then forces the last known good plan — no 2 a.m. intervention required. This post covers how it works, how to enable and inspect it, and where it stops being useful.

How the engine decides a plan regressed

Automatic plan correction sits on top of Query Store, which continuously records query text, plans, and runtime statistics. When the optimizer compiles a new plan for a query and the new plan's performance is significantly worse than the previous one, the engine flags it as a regression and can force the earlier plan. If the forced plan later stops being valid — say, after a schema change invalidates it — the engine unforces it and lets normal compilation resume.

Two prerequisites matter here:

• Query Store must be enabled and in read-write mode. A database with Query Store off, or stuck in read-only (which can happen when it hits its size quota), gets no automatic correction at all.
• The option is an explicit choice. Automatic tuning is configured per database, and you should verify the current state rather than assume a default — defaults have varied across deployment contexts over time.

One honest caveat: Microsoft does not publish a simple universal threshold for "how much worse" triggers a correction. Treat the detection logic as internal heuristics, not a tunable SLA.

Enabling it and watching what it does

Run these in the context of the target user database (not master), with a login that has ALTER permission on the database — a database owner or a member of db_owner works. First, confirm Query Store is healthy:

SELECT actual_state_desc, readonly_reason
FROM sys.database_query_store_options;

You want READ_WRITE. Then enable plan correction:

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

Verify the setting landed:

SELECT name, desired_state_desc, actual_state_desc
FROM sys.database_automatic_tuning_options;

The interesting part is what happens next. Detection events, forced plans, and automatic reverts are all visible in one DMV:

SELECT reason, score, state,
       JSON_VALUE(state, '$.currentValue') AS current_state,
       JSON_VALUE(details, '$.planForceDetails.queryId') AS query_id
FROM sys.dm_db_tuning_recommendations;

The reason column tells you why the engine acted (for example, that a plan change regressed CPU), and the JSON in state and details tells you whether a plan is currently forced and which query it applies to. If the engine later unforces the plan — common after schema changes — you'll see that transition here too.

A worked example: the parameter-sniffing flip

A realistic scenario: you have a parameterized report query on an orders table. Most executions filter on a narrow date range, so the optimizer compiles a plan using a nonclustered index seek. A statistics update fires, the next compilation happens with an atypical parameter value (a huge date range), and the optimizer produces a scan-oriented plan. That plan gets cached and reused for the narrow-range executions, and latency spikes.

Without automatic correction, you discover this from a monitoring alert, dig through Query Store, and manually force the old plan with sp_query_store_force_plan. With FORCE_LAST_GOOD_PLAN = ON, the engine detects the regression from its own telemetry, forces the prior plan, and you find out after the fact by querying sys.dm_db_tuning_recommendations — the incident is a log entry instead of a page.

You can reproduce the shape of this on a non-production database: create a skewed table, run a parameterized query with a common value, update statistics, then execute with an outlier value to provoke a new plan, and watch the recommendations DMV. Don't expect a guaranteed trigger on demand — the detection heuristics aren't documented to that level — but the DMV is where any real detection will surface.

What it won't fix, and the trade-offs

Automatic plan correction addresses exactly one failure mode: the optimizer switching to a worse plan for a query that previously had a good one. It does nothing for:

• Missing indexes or badly written queries. If no good plan ever existed, there's nothing to revert to.
• The root cause of plan instability. Parameter sniffing and stale statistics don't go away because you forced a plan. The forced plan is a tourniquet; the underlying sensitivity is still there.
• Schema evolution. Forced plans go stale. Add or drop an index the plan depends on, and the force is invalidated — which is correct behavior, but it means yesterday's fix silently expires.

There's also a scoping note on the sibling options. Automatic tuning exposes CREATE_INDEX and DROP_INDEX recommendations too, but those carry materially more risk — dropping an index that's only used by a monthly report, for instance, is a regression you won't notice for weeks. Evaluate those separately and deliberately; enabling plan correction doesn't require enabling them.

A sensible operating posture

Enable FORCE_LAST_GOOD_PLAN on production databases where Query Store is healthy, and treat sys.dm_db_tuning_recommendations as a review queue, not a black box. A weekly look at what was forced tells you which queries are plan-unstable — and those are the queries worth real engineering attention: better indexing, a plan guide, a query hint, or a rewrite. The safety net catches the fall; your job is to fix the ladder.