Turning Azure SQL Database into a Self‑Tuning Engine: How to Enable and Verify Automatic Tuning

Problem: Manual Tuning Is Time‑Consuming

As workloads grow, keeping query plans optimal becomes a moving target. A single schema change or data volume spike can break a previously efficient plan, leading to CPU spikes or slow‑downs. The traditional response is to dig into execution plans, tweak indexes, or rewrite queries—tasks that consume DBA hours and delay production changes.

What Automatic Tuning Does

Azure SQL Database’s Automatic Tuning feature is a built‑in optimizer that continuously monitors query performance and applies one‑click fixes when it detects a regression. It supports three main actions:

• Force the last good plan – when a new plan causes a measurable performance drop, the engine rolls back to the previous plan.
• Optimize for sequential key – the engine can add or drop the OPTIMIZE_FOR_SEQUENTIAL_KEY hint automatically for scans that benefit from it.
• Auto‑index creation/drop – based on query patterns, the engine can propose and automatically create or drop indexes.

All three are enabled per database with a single ALTER DATABASE statement, and the feature is available on Standard, Premium, and Hyperscale tiers.

Enabling Automatic Tuning

Run the following T‑SQL in a query editor that has ALTER DATABASE privileges (usually a user with db_owner role). Replace MyDB with your database name.

ALTER DATABASE [MyDB] SET AUTOMATIC_TUNING = (
    FORCE_LAST_GOOD_PLAN = ON,
    OPTIMIZE_FOR_SEQUENTIAL_KEY = ON,
    AUTO_CREATE_STATISTICS = ON
);

After the command succeeds, the database will start collecting plan change events. The engine records the current plan as the "good" plan and will automatically revert if a regression is detected.

Verifying the Settings

Check that the options are active by querying the system view:

SELECT * FROM sys.database_automatic_tuning;

The result includes columns like is_enabled and last_plan_change_time. A value of 1 in is_enabled confirms the feature is on.

Monitoring Real‑World Impact

• Query Performance Insight – in the Azure portal, navigate to the database, click Query Performance Insight, and filter by Automatic Tuning to see applied changes.
• DMVs – sys.dm_exec_query_stats shows plan IDs and execution counts. Compare last_execution_time before and after a plan change to confirm the engine is reverting.
Resource Metrics – watch CPU percentage and Data IO for a few minutes after enabling; a temporary spike is normal as the engine re‑evaluates plans.

Trade‑offs and Limitations

While Automatic Tuning saves effort, it is not a silver bullet:

• During the first few minutes after a plan change, the engine may use more CPU or I/O as it tests alternative plans.
• Complex or highly custom workloads may still need manual index or query tuning; the feature does not replace a DBA’s knowledge.
• Auto‑index creation can lead to a small increase in storage usage and maintenance overhead; review proposed indexes in the portal’s Indexes blade.

Because the feature can alter execution plans, it is wise to enable it in a staging environment first and monitor the impact before rolling out to production.

Actionable Next Steps

1. Enable Automatic Tuning on a test database using the ALTER DATABASE command above.
2. Run a representative workload and monitor sys.database_automatic_tuning and Query Performance Insight.
3. If the feature improves performance without excessive spikes, enable it on the production database.
4. Periodically review the Indexes blade to prune any auto‑created indexes that are no longer beneficial.

By turning Azure SQL Database into a self‑tuning engine, you free up DBA time for higher‑value work while keeping query performance at its best.