Azure SQL Database Automatic Tuning: Verify Query Store Before You Rely on It

A reporting query that usually finishes in two seconds suddenly takes two minutes. You open Query Store and see a new execution plan appeared after statistics updated. You could manually force the last good plan, but by the time you finish the investigation, the business impact is already done. Azure SQL Database automatic tuning can detect regressions and apply corrections automatically, but it is not a set-and-forget feature. If Query Store is disabled or the database inherits a server-level setting you did not inspect, the tuning option may show as enabled while doing nothing.

The useful takeaway: enable automatic tuning at the database level, confirm Query Store is read-write, and verify actual_state rather than assuming desired_state means protection is active.

What Automatic Tuning Actually Does

Azure SQL Database automatic tuning for single databases and elastic pools can perform two categories of action:

• Automatic plan correction (FORCE_LAST_GOOD_PLAN): when a query regresses, the engine forces a previously verified good plan from Query Store.
• Automatic index management (CREATE_INDEX, DROP_INDEX): the engine creates or drops indexes based on workload patterns.

These settings can be configured at the logical server level and inherited by databases, or overridden per database. This distinction matters because a database may appear tuned while actually inheriting a disabled server policy.

Configure at the Right Scope

Connect to the user database—not master—with a principal that has ALTER DATABASE permission, such as the server admin login or a member of the dbmanager or db_owner role. The following examples are illustrative; confirm exact syntax against your database engine version before running them in production.

-- Example: enable automatic plan correction on the current database
ALTER DATABASE Current
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

To enable index management as well:

-- Example: enable automatic index creation and dropping
ALTER DATABASE Current
SET AUTOMATIC_TUNING (CREATE_INDEX = ON, DROP_INDEX = ON);

To make the database follow the server-level policy instead:

-- Example: inherit server-level automatic tuning settings
ALTER DATABASE Current
SET AUTOMATIC_TUNING = INHERIT;

Worked Example: Verify Before You Trust It

Automatic plan correction depends on Query Store. Without Query Store capturing a known-good plan, FORCE_LAST_GOOD_PLAN has nothing to revert to. Run these checks on the target database:

1. Confirm Query Store is enabled and in read-write mode:

SELECT * FROM sys.database_query_store_options;

Look for actual_state_desc returning READ_WRITE. If it is OFF or READ_ONLY, plan correction cannot operate.

1. Inspect the automatic tuning options:

SELECT name, desired_state, actual_state, reason
FROM sys.database_automatic_tuning_options;

desired_state is what you requested. actual_state is what the engine is actively applying. A value of ON under desired_state with actual_state of OFF means the setting is not active—commonly because Query Store is unavailable or the option is not supported in the current tier or region.

1. Review generated recommendations:

SELECT * FROM sys.dm_db_tuning_recommendations;

This view shows whether the engine has identified plan regressions or missing indexes and whether those recommendations were applied.

Limitations and Trade-Offs

• Plan forcing requires history. FORCE_LAST_GOOD_PLAN cannot help a query that never had a good plan captured in Query Store.
• Index churn. Automatic creation and dropping can change execution plans during production workloads. Test in a non-production copy of the database first.
• Not universal. Behavior and available options differ by service tier and region. SQL Managed Instance and SQL Server on Azure Virtual Machines have different tuning support.
• Not a design substitute. Recommendations do not replace workload-specific indexing and schema review.

Rollback

Because ALTER DATABASE changes database state, you can disable a tuning action if it causes instability:

-- Example: disable automatic plan correction
ALTER DATABASE Current
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = OFF);

Practical Closing Checklist

• Connect to the user database, not master.
• Confirm sys.database_query_store_options shows Query Store as READ_WRITE.
• Set tuning at the database level unless you intentionally want server-level inheritance.
• Check actual_state in sys.database_automatic_tuning_options, not just desired_state.
• Review sys.dm_db_tuning_recommendations after enabling to confirm the engine is acting on real workload signals.

Automatic tuning is most useful when you treat it as a safety net with verified prerequisites, not as a replacement for understanding why a query regressed in the first place.