Let Azure SQL Database Auto‑Tune Itself: Indexing and Plan Correction in Action

A Real‑World Performance Problem

When a new release or data model change is pushed to a production database, you often notice that query latency spikes. Manually hunting for missing indexes or regressed plans can take days, and the window of impact may be narrow. Azure SQL Database’s Automatic Tuning feature promises to relieve that pain by continuously monitoring the workload, recommending and applying index changes, and forcing the last good plan when a regression occurs.

How Automatic Tuning Works

The feature is built on two core components:

• Intelligent Performance Recommendations Engine – analyzes query patterns captured by Query Store and decides whether creating or dropping an index will improve overall performance.
• Automatic Plan Correction – when a query plan’s runtime exceeds a threshold compared to the previous best plan, the engine forces the last good plan to prevent the regression from persisting.

Both mechanisms respect the database’s DTU or vCore limits; a recommended change is applied only if the engine predicts a net benefit and the change will not exceed the resource budget.

Enabling Automatic Tuning

Automatic Tuning can be turned on via the Azure portal or with T‑SQL. The portal is quick for a single database, while T‑SQL is handy for automation scripts.

Portal Steps

1. Navigate to your SQL Database resource in the Azure portal.
2. Select Intelligent performance under the Security section.
3. Toggle Automatic tuning to On. You can leave the default options (Create index, Drop index, Force last good plan) enabled or disable any you prefer.
4. Click Apply.

T‑SQL Method

-- Requires ALTER ANY DATABASE permission (e.g., db_owner)
ALTER DATABASE [YourDatabase] SET AUTOMATIC_TUNING = ON;
-- Optional: enable specific mechanisms explicitly
ALTER DATABASE [YourDatabase] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
ALTER DATABASE [YourDatabase] SET AUTOMATIC_TUNING (CREATE_INDEX = ON);
ALTER DATABASE [YourDatabase] SET AUTOMATIC_TUNING (DROP_INDEX = ON);

Ensure that Query Store is enabled, because Automatic Tuning relies on it. New databases have Query Store on by default, but if you disabled it, re‑enable it:

ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;

Worked Example: From Recommendation to Reality

Consider a table Orders that is frequently queried with a filter on CustomerID but lacks an index on that column. After enabling Automatic Tuning, run a representative workload for a few hours. Then inspect the tuning actions that were applied.

-- View applied tuning recommendations
SELECT recommendation_name,
       improvement_type,
       been_relevant,
       last_time
FROM sys.dm_db_tuning_recommendations
WHERE status = 'Applied';

If the engine identified a missing index, you should see a recommendation of type CreateIndex listed as Applied. You can confirm the index exists with:

SELECT name, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.Orders');

After the index is created, monitor DTU or vCore usage in Azure Monitor. A successful recommendation typically results in a measurable drop in average query latency and a steady or lower resource consumption profile.

Trade‑Offs and Caveats

• Resource spikes – Creating or dropping indexes can briefly increase DTU/vCore usage and may block active sessions. Monitor the sys.dm_db_resource_stats DMV or Azure Monitor metrics during the first few minutes after a tuning action.
• Schema change policy – In environments with strict change management, Automatic Tuning may alter indexes without explicit DBA approval. Consider disabling CreateIndex or DropIndex if that is a concern.
• Query Store dependency – Plan correction requires Query Store to be enabled. Without it, the engine cannot detect regressions.
• Limited index types – The engine does not generate filtered indexes, columnstore indexes, or other advanced indexing constructs. Manual tuning is still required for those scenarios.

How to Verify the Impact

1. Enable Automatic Tuning and Query Store.
2. Run a workload that mimics your peak traffic for at least 30 minutes.
3. After the engine has had time to analyze, query sys.dm_db_tuning_recommendations to see applied actions.
4. Compare the average query latency and DTU/vCore consumption before and after the actions using Azure Monitor or sys.dm_exec_query_stats.
5. For plan correction, force a bad plan on a known query (e.g., with sp_create_plan_guide) and verify that the engine restores the last good plan once the runtime exceeds the threshold.

Actionable Takeaway

If you run a workload that is stable enough for the engine to learn from, enable Automatic Tuning on a test database first. Use the portal or T‑SQL to turn on the three mechanisms, enable Query Store, and then monitor the Intelligent performance blade for applied actions. Once you are comfortable with the subtle resource spikes and the types of indexes the engine creates, roll the setting out to production. Remember to keep a change‑control policy in place if your organization requires explicit approval for schema changes.