Diagnosing Azure SQL Database Plan Regression with Automatic Tuning Plan Correction

Recognizable condition

A workload that previously ran steadily begins to show higher query latency, timeout errors, or increased CPU/DTU consumption in Azure SQL Database metrics. The change often appears after a statistics update, parameter sniffing shift, or schema alteration, and the Query Store shows a new execution plan with a significantly higher cost than the prior good plan.

Short cause/diagnostic table

Check	What to look for	Why it matters
Query Store status	actual_state_desc = 'ON' in sys.database_query_store_options	If Query Store is off, plan regression data is not collected.
Regressed query identification	Compare avg_duration or avg_cpu_time in sys.query_store_runtime_stats for the same query_id across two time windows; a spike indicates regression.	Pinpoints the query whose plan has degraded.
Automatic Tuning recommendation	Rows in sys.dm_db_tuning_recommendations with reason = 'Plan regression' and type = 'FORCE_LAST_GOOD_PLAN'	Shows that the service has detected a better prior plan and can force it.
Blocking conditions	Read‑only secondary, Basic edition, or manual plan forcing that prevents auto‑apply.	Automatic Tuning will skip applying the recommendation if any of these exist.

Ordered checks

1. Verify Query Store is enabled and has a suitable retention policy.

   SELECT actual_state_desc, readonly_reason, max_storage_size_mb, stale_query_threshold_days
   FROM sys.database_query_store_options;

   Run this in the target database. You need VIEW DATABASE STATE permission. Expected result: actual_state_desc = 'ON'. If it shows OFF or READ_ONLY, enable Query Store first (ALTER DATABASE SET QUERY_STORE = ON).

2. Identify regressed queries by comparing runtime stats.

   SELECT q.query_id, q.object_id, q.query_sql_text,
          rs1.avg_duration AS avg_duration_before,
          rs2.avg_duration AS avg_duration_after
   FROM sys.query_store_query q
   JOIN sys.query_store_plan p1 ON p1.query_id = q.query_id
   JOIN sys.query_store_runtime_stats rs1 ON rs1.plan_id = p1.plan_id
   JOIN sys.query_store_plan p2 ON p2.query_id = q.query_id AND p2.plan_id <> p1.plan_id
   JOIN sys.query_store_runtime_stats rs2 ON rs2.plan_id = p2.plan_id
   WHERE rs1.last_execution_time < '2026-09-01'  -- before change
     AND rs2.last_execution_time >= '2026-09-01' -- after change
     AND rs2.avg_duration > rs1.avg_duration * 2;  -- arbitrary threshold for regression
   

   Adjust the date thresholds to your observed incident window. This query requires VIEW DATABASE STATE. The output lists queries whose average duration doubled after the change.

3. Confirm Automatic Tuning mode for plan correction.

   SELECT name, desired_state_desc, actual_state_desc
   FROM sys.database_automatic_tuning_options
   WHERE name = 'FORCE_LAST_GOOD_PLAN';

   You need VIEW DATABASE STATE. Desired states: ON (auto‑apply enabled), INHERIT (inherits from server), or OFF. If it shows OFF, enable it: ALTER DATABASE SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON).

4. Review any blocking conditions that could prevent auto‑apply.

   • Check edition: SELECT edition FROM sys.database_service_objectives; – Basic tier is not supported.
   • Check for read‑only secondary: SELECT secondary_role_allow_connections_desc FROM sys.dm_geo_replication_links;
   • Look for existing manual plan forcing: SELECT is_forced_plan FROM sys.query_store_plan WHERE query_id = <your query id>;

   If any of these are present, Automatic Tuning will defer the recommendation.

Fixes tied to findings

When the checks above confirm that Automatic Tuning can act:

• Allow the service to apply the recommendation automatically. It will execute sp_query_store_force_plan internally, setting is_forced_plan = 1 for the last good plan.

  You can verify the force after a few minutes:

  SELECT q.query_id, p.plan_id, p.is_forced_plan
  FROM sys.query_store_query q
  JOIN sys.query_store_plan p ON p.query_id = q.query_id
  WHERE q.query_id = <regressed query id>;

  Expected result: is_forced_plan = 1 for the plan_id indicated in the recommendation.

• If Automatic Tuning cannot apply (e.g., blocked by a read‑only secondary), manually force the plan using the recommendation’s plan_id:

  EXEC sp_query_store_force_plan @query_id = <regressed query id>, @plan_id = <recommended good plan id>;

  You need ALTER ANY QUERY STORE permission. After execution, re‑run the verification query above to confirm is_forced_plan = 1.

• If the recommendation is skipped due to conflicting hints or in‑memory OLTP, consider:

  • Updating statistics with UPDATE STATISTICS to stabilize the plan.
  • Using query hints (OPTION (RECOMPILE)) only as a temporary workaround.
  • Opening a support ticket if the regression persists.

Escalation criteria

Escalate to Azure support or a senior database engineer when:

• Query Store is enabled, Automatic Tuning is set to ON or INHERIT, but no FORCE_LAST_GOOD_PLAN recommendation appears after 30 minutes of observing the regression.
• The forced plan is applied (is_forced_plan = 1) yet query latency or DTU usage does not improve, indicating the regression may be due to factors other than plan choice (e.g., resource contention, blocking).
• The database resides in a Basic tier or a failover group secondary where Automatic Tuning is inherently unavailable, and you need to consider moving to a supported tier or adjusting the failover configuration.

In each case, gather the output of the verification queries listed above and include them in the support request to reduce troubleshooting time.