Azure SQL Database Automatic Tuning Isn't Improving Performance: A Diagnostic Guide

You enabled Automatic Tuning on an Azure SQL Database expecting it to fix regression plans and create missing indexes, but weeks later the same slow queries persist. In most cases the feature is doing exactly what it was told to do — the problem is that it was never fully enabled, its recommendations are expiring unapplied, or the workload falls outside what it can tune. This guide walks through how to confirm each condition and what to do about it.

Recognizable condition

Typical symptoms: sys.dm_db_tuning_recommendations is empty or shows only Expired rows; the portal shows "No recommendations"; the same plan regressions keep appearing in Query Store; or indexes suggested by the missing-index DMVs are never created even though CREATE_INDEX appears enabled.

Common causes at a glance

Finding	Likely cause
No recommendations at all	Query Store off, read-only, or too little workload activity
Recommendations exist but stay Active forever	Automatic apply not enabled; suggestions await manual approval
Recommendations move to Expired	Schema changes or low activity invalidated them before application
Recommendations Reverted	Tuning applied the change, measured a regression, and rolled it back (working as designed)
FORCE_LAST_GOOD_PLAN never fires	Option disabled, or regressions don't meet the detection threshold
CREATE_INDEX suggestions absent	Insufficient or bursty workload, or the database is on a tier/size where index churn is suppressed

Ordered checks

1. Confirm Query Store is capturing plans

Automatic Tuning's recommendations are built on Query Store data. If Query Store is off, read-only, or sized too small, nothing downstream works. Run this in the user database (requires VIEW DATABASE STATE):

SELECT actual_state_desc, desired_state_desc, readonly_reason,
       current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

You want actual_state_desc = READ_WRITE. If it flipped to READ_ONLY because storage hit the cap, increase MAX_STORAGE_SIZE_MB or shorten CLEANUP_POLICY. Note that on a readable secondary replica, Query Store on the primary may be capturing while the secondary shows read-only — check where your workload actually runs.

2. Check which tuning options are actually enabled

"Automatic Tuning is on" in the portal can mean only one of three options is enabled. Verify per-option state:

SELECT name, desired_state_desc, actual_state_desc, reason_desc
FROM sys.database_automatic_tuning_options;

The three options are FORCE_LAST_GOOD_PLAN, CREATE_INDEX, and DROP_INDEX. A common finding: FORCE_LAST_GOOD_PLAN is ON (it defaults on for new databases) but CREATE_INDEX is OFF — so plan regressions get fixed while missing indexes are never created. Enable an option explicitly:

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (CREATE_INDEX = ON);

Run this in the user database; it requires ALTER permission on the database. Enabling CREATE_INDEX changes state — the engine may create real indexes on production tables — so validate the recommendation pattern on a copy of the database first if the workload is sensitive.

3. Inspect the recommendation lifecycle

SELECT name, reason, state,
       JSON_VALUE(state, '$.currentValue')  AS current_state,
       JSON_VALUE(state, '$.reason')        AS state_reason,
       valid_since, last_refresh
FROM sys.dm_db_tuning_recommendations
ORDER BY last_refresh DESC;

The state column is JSON; currentValue tells you whether the recommendation is Active, Verifying, Success, Reverted, or Expired. state_reason is the diagnostic gold: values like "SchemaChanged", "QueryExpired", or regression-related reasons explain why a recommendation never landed. An empty result set with a healthy Query Store usually means the workload genuinely has no tunable patterns — or too little activity to detect them.

4. Rule out workload and safety conditions

Automatic Tuning deliberately backs off in several situations:

• Low activity: a dev or bursty database may never accumulate enough Query Store data for statistically confident recommendations.
• Frequent schema changes: index recommendations expire when the underlying schema changes; a database under continuous deployment churns recommendations before they're applied.
• Regression reversal: if a forced plan or new index measured worse, the feature reverts it. Repeated Reverted states mean the feature is protecting you, not failing.
• Index churn protection: the engine suppresses create/drop index oscillation, so a table alternating between two access patterns may see no action.

5. Check Azure Monitor and the portal for blocked actions

If you have a diagnostic setting sending the "Automatic tuning" category to Log Analytics, query it for errors and state transitions. In the portal, the database's Intelligent Performance > Recommendations blade shows the same lifecycle plus any suppressed actions. Warnings here (for example, recommendations blocked during a maintenance window or on a tier where an action isn't supported) explain silent gaps.

Fixes tied to findings

• Query Store READ_ONLY: raise storage or tighten retention, then allow a few days of representative workload before judging recommendations.
• Option OFF: enable the specific option you need (step 2), understanding CREATE_INDEX and DROP_INDEX modify your schema automatically.
• Recommendations expire before applying: reduce schema churn, or apply high-value index recommendations manually from the details JSON (it contains the full T-SQL script) during a controlled window.
• Everything healthy, still slow: the bottleneck is outside Automatic Tuning's scope — parameter sniffing, blocking, resource limits, or application design. Move to wait statistics (sys.dm_db_wait_stats), Query Store top-resource queries, and possibly a service-tier review.

Verifying the fix

After enabling options or fixing Query Store, don't judge results the same day. Give the workload a representative period (several days for most OLTP systems), then: re-query sys.dm_db_tuning_recommendations for Success/Verifying states; compare Query Store regressed-queries reports before and after; and confirm any created indexes are actually used via sys.dm_db_index_usage_stats (user_seeks/user_scans increasing). If a created index shows zero usage after a full business cycle, the recommendation may have been wrong for your real workload — consider dropping it manually.

Limitations and escalation criteria

Automatic Tuning addresses plan regression and index management only. It will not fix blocking, deadlocks, tempdb contention, insufficient DTU/vCore capacity, or poorly written queries. Escalate to a manual performance investigation — or Microsoft support with the recommendation history exported — when: Query Store and all three options are healthy yet no recommendations appear after two weeks of steady production load; recommendations repeatedly revert; or wait statistics are dominated by resource waits (CPU, IO, memory grants) rather than plan issues. Behavior described here reflects long-standing Azure SQL Database functionality, but portal labels and DMV details evolve — confirm against current Microsoft documentation before relying on specific state-reason strings.