Managing Azure SQL Database Automatic Tuning Modes for Safe Plan Control

Problem: a query slows down overnight

\n

You wake to alerts that a critical report in Azure SQL Database is running slower than usual. The query plan appears different, and you haven't changed any indexes or statistics. Could the database automatically swapped the plan? Automatic Tuning, a set-it-and-forget-it feature, can and does make plan changes without downtime -- but without visibility, those changes can feel like black boxes.

\n\n

Automatic Tuning modes at a glance

\n

Azure SQL Database provides three built-in automatic tuning modes that determine how aggressively the service adjusts execution plans:

\n

\n
• AUTO: The service monitors query performance and automatically forces a last-good plan if performance degrades beyond a threshold. It may also enable Parameter Sensitive Plan support when beneficial.
\n
• FORCE_LAST_GOOD_PLAN: The service only forces the last known good plan when a query's performance drops. It does not enable PSP automatically.
\n
• OFF: Automatic Tuning is disabled; the database uses whatever plan the optimizer generates.
\n

\n\n

Parameter Sensitive Plan (PSP) support

\n

Workloads with skewed parameter values -- such as a search parameter that is frequently popular versus rare -- can cause a single plan to perform well for one value and poorly for another. PSP lets the optimizer generate different plans per parameter value, improving overall throughput. In Automatic Tuning, PSP can be activated alongside AUTO mode, and the service will remember which parameter combinations received which plans.

\n\n

Inspecting and controlling plan changes with T-SQL

\n

Before adjusting modes, it helps to see what's already happening. The following commands require ALTER DATABASE permission on the target database and should be run in a query window connected to the database.

\n

-- Check current automatic tuning mode\nSELECT tuning_mode \nFROM sys.dm_db_tuning_mode;\n-- Expected result: 0 = AUTO, 1 = FORCE_LAST_GOOD_PLAN, 2 = OFF\n\n-- View plan forcing history from Query Store\nSELECT \n    q.query_id,\n    p.plan_id,\n    p.last_execution_time,\n    p.count_executions,\n    p.avg_cpu_time,\n    p.avg_duration\nFROM sys.query_store_query q\nJOIN sys.query_store_plan p ON q.query_id = p.query_id\nWHERE p.plan_id IN (\n    SELECT plan_id FROM sys.query_store_plan \n    WHERE last_execution_time > DATEADD(day, -7, GETUTCDATE())\n)\nORDER BY p.last_execution_time DESC;\n

\n

What to check: After running a mode change, re-query sys.dm_db_tuning_mode to confirm the setting took effect. Compare avg_duration and count_executions before and after the change to assess impact.

\n\n

-- Set automatic tuning to FORCE_LAST_GOOD_PLAN mode\nALTER DATABASE CURRENT \nSET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);\n

\n

Risk note: Forcing a last-good plan can temporarily increase CPU or I/O as the database stabilizes around the new plan. If the forced plan was good only for a narrow parameter range, overall workload latency may rise. Always monitor the first few hours after a mode change.

\n\n

Trade-off and limitation

\n

Automatic Tuning operates per-database. Plan histories, forced plans, and PSP adaptations are not shared across databases in an elastic pool or geo-replicated setup. If you manage multiple databases with similar workloads, you'll need to apply mode changes and verify results individually. Additionally, the stabilization period after a plan change can briefly spike resource usage -- a known behavior that typically resolves once the optimizer settles on an effective plan.

\n\n

Actionable next steps

\n

\n
1. Run SELECT tuning_mode FROM sys.dm_db_tuning_mode on each database to confirm the current mode.
\n
2. If you observe frequent plan flips or performance degradation, consider switching to FORCE_LAST_GOOD_PLAN or disabling Automatic Tuning with ALTER DATABASE ... SET AUTOMATIC_TUNING (OFF).
\n
3. For workloads with highly skewed parameters, enable Automatic Tuning in AUTO mode and monitor Query Store for PSP activity -- look for multiple plan IDs associated with the same query ID.
\n
4. Re-evaluate after 24-48 hours and adjust the mode or manually adjust indexes/statistics if the automatic changes don't meet performance goals.
\n

\n

Automatic Tuning can save hours of manual plan management, but it's not set-and-forget. A quick check of sys.dm_db_tuning_mode and regular Query Store reviews keep the feature working for you, not against you.