Let Azure SQL Database Fix Its Own Bad Plan Choices — With Guardrails

A reporting query that ran in 200 ms yesterday now takes 12 seconds. Nothing was deployed. The likely culprit: the optimizer picked a new execution plan – maybe after a statistics update changed its cost estimates – and the new plan is dramatically worse for the parameter values your app actually sends. In Azure SQL Database, you can have the platform detect exactly this pattern and force the last known good plan automatically. The feature is called automatic plan correction, and it's worth turning on — as long as you understand what it does and doesn't fix.

The flight recorder is already running

Automatic plan correction builds on Query Store, which is enabled by default on Azure SQL Database. Query Store captures query text, execution plans, and runtime statistics inside the user database — think of it as a per-database flight recorder for query performance. Without it, diagnosing a plan regression means catching the bad plan in the act; with it, you can compare what ran well last week against what’s running badly now. Because the data lives in your database, it consumes storage and adds modest write overhead, both of which are configurable.

What FORCE_LAST_GOOD_PLAN actually does

When you enable automatic plan correction, the engine continuously compares recent query performance against historical performance in Query Store. When it detects a query whose plan choice regressed – for example, a query that flipped from an efficient nested-loops plan to a much slower hash plan after parameter sniffing changed – it forces the last known good plan. Crucially, it doesn’t stop there: it keeps monitoring the forced plan and can unforce it if the forced plan stops performing better. Every action is logged, so this is auditable automation rather than a black box.

Turning it on and checking what it did

Enabling it is one statement, run in the context of the database you want to tune (it applies per database, not per server):

ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

You need ALTER permission on the database. To confirm the setting took effect:

SELECT name, desired_state, actual_state
FROM sys.database_automatic_tuning_options;

After your workload has run for a while, review what the feature detected and forced:

SELECT name, reason, score,
       JSON_VALUE(details, '$.implementationDetails.script') AS script
FROM sys.dm_db_tuning_recommendations;

The reason column tells you why a regression was flagged, and the details include the plan IDs involved, so you can inspect both plans in Query Store before deciding whether you trust the automation. To check Query Store’s own footprint and capture mode:

SELECT actual_state_desc, query_capture_mode_desc,
       current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

A concrete example

Consider a reporting query with a date-range parameter. When first compiled with a narrow range, the optimizer produced a nested-loops plan with an index seek – fast. A statistics update later triggered recompilation with a wide range, producing a hash join plan that got cached and then reused for narrow-range calls, where it scans far more rows than needed. Latency jumps from milliseconds to seconds, erratically, depending on which plan is in cache. With automatic plan correction on, the engine notices the regression against the query’s history, forces the nested-loops plan, and latency stabilizes – no code deploy, no 2 a.m. plan forcing by hand.

Where it falls short

Automatic plan correction addresses plan-choice regressions only. It will not create missing indexes, fix a schema design problem, or repair a query that’s simply written badly. Forced plans can also be invalidated by schema changes, and – more subtly – a forced plan can silently become suboptimal as your data distribution evolves. The last good plan was good for last month’s data; treat forcing as a stabilizer that buys you time to fix the root cause (often parameter sniffing, addressed with query hints, OPTIMIZE FOR, or a rewrite), not as a permanent solution. Also note the capture mode trade-off: Query Store’s AUTO capture mode skips very frequent or trivial queries to limit overhead, while ALL captures everything and can bloat storage on high-throughput databases. Defaults and behavior can vary by service tier and engine version, so verify against your own database rather than assuming.

A sensible rollout

Enable FORCE_LAST_GOOD_PLAN on a non-production database first, confirm the state via sys.database_automatic_tuning_options, and let a representative workload run. Then make a habit – weekly is fine – of reviewing sys.dm_db_tuning_recommendations. Every forced plan on that list is a query whose root cause deserves a proper fix. Used that way, automatic plan correction is less an autopilot and more an early-warning system that also stops the bleeding.