10.2 Automatic Tuning and Database-Scoped Configuration
Key Takeaways
- Azure SQL Database automatic tuning has three options: CREATE_INDEX, DROP_INDEX, and FORCE_PLAN (forces last known good plan); each is set to ON, OFF, or INHERIT.
- On SQL Server, MI, and VMs, the equivalent is automatic plan correction (FORCE_LAST_GOOD_PLAN), built on Query Store; CREATE_INDEX/DROP_INDEX auto-apply is not available there.
- ALTER DATABASE SCOPED CONFIGURATION sets MAXDOP, LEGACY_CARDINALITY_ESTIMATION, PARAMETER_SNIFFING, QUERY_OPTIMIZER_HOTFIXES, IDENTITY_CACHE, and ELEVATE_ONLINE/RESUMABLE per database.
- ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE clears the plan cache for one database only, unlike DBCC FREEPROCCACHE which clears the whole instance or server.
- Database compatibility level gates IQP features; DSC tunes the optimizer within a level; the recommended post-upgrade process uses Query Store before raising the compat level.
Why Automatic Tuning and DSC Matter
Two related mechanisms let you tune one database without touching server-wide settings or other tenant databases. Automatic tuning lets the platform observe workload behavior through Query Store and act on regressions. Database-scoped configuration (DSC) gives you per-database knobs that previously were server-wide only (MAXDOP, trace-flag-equivalent optimizer hotfixes, parameter sniffing, etc.). Both appear on DP-300 because they are the levers you pull for "one database regressed after upgrade, others on the same server are fine."
Automatic Tuning in Azure SQL Database
Azure SQL Database automatic tuning uses built-in intelligence to monitor workload via Query Store and recommend or automatically apply three actions:
| Option | Action | Default |
|---|---|---|
| CREATE_INDEX | Identifies indexes that would improve workload and creates them | Inherit |
| DROP_INDEX | Identifies and drops unused or duplicate indexes | Inherit |
| FORCE_PLAN | Forces the last known good plan for a query whose new plan regresses performance | Inherit |
Each option has three states: ON (apply automatically), OFF (disabled), and INHERIT (inherit the logical server setting, which is the default). Configure at server level to set a policy across all databases, then override per database. The Azure portal's automatic tuning blade exposes the recommendations and the applied history; PowerShell and the REST API script it.
The mechanism behind FORCE_PLAN is last good plan detection: the platform compares the runtime stats of the current plan against the previous plan in Query Store; if the new plan is materially worse, it forces the previous one. A common scenario: a stats update or upgrade causes a critical query to slow down. Enabling FORCE_PLAN on that database restores the prior performance automatically, without manual plan forcing.
Automatic Plan Correction on SQL Server, MI, and VMs
On SQL Server (on-prem and Azure VMs) and SQL Managed Instance, the equivalent capability is automatic plan correction, built on Query Store. It is enabled with ALTER DATABASE ... SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON) — the option name in SQL Server 2017+; note the difference from Azure SQL DB's FORCE_PLAN. You can also force plans manually from Query Store in SSMS or via sp_query_store_force_plan. Unlike Azure SQL DB, CREATE_INDEX and DROP_INDEX automatic tuning do not exist on SQL Server/MI/VMs — index recommendations surface through Database Engine Tuning Advisor, Query Store missing-index hints, or the Database Tuning Recommendations in the Azure portal for MI, but are not auto-applied.
Which Azure SQL Database automatic tuning option, when set to ON, identifies queries whose new execution plan regresses performance and automatically forces the last known good plan?
ALTER DATABASE SCOPED CONFIGURATION
ALTER DATABASE SCOPED CONFIGURATION (DSC) sets per-database options that previously required server-level changes or trace flags. The syntax is ALTER DATABASE SCOPED CONFIGURATION SET <option> = <value> and the settings live with the database — they travel with it when it moves between servers or is restored elsewhere.
| Setting | Purpose | Default |
|---|---|---|
MAXDOP | Caps the degree of parallelism for queries in this database | 0 (use server) |
LEGACY_CARDINALITY_ESTIMATION | Uses the CE from SQL Server 2012 (compat level 110) for this DB regardless of compat level | OFF |
PARAMETER_SNIFFING | Enables/disables parameter sniffing; OFF forces plan-uniform behavior | ON |
QUERY_OPTIMIZER_HOTFIXES | Enables post-RTM optimizer hotfixes (the per-DB equivalent of trace flag 4199) | OFF |
IDENTITY_CACHE | Caches identity values for faster inserts; OFF avoids gaps on failover | ON |
ELEVATE_ONLINE | Promotes operations to online where supported (Enterprise) | OFF |
ELEVATE_RESUMABLE | Promotes operations to resumable where supported | OFF |
GLOBAL_TEMPDB_AUTOMATIC_GROWTH / TEMPDB_RETENTION | tempdb metadata and history retention on SQL MI | varies |
CLEAR PROCEDURE_CACHE | Clears the plan cache for this database only | n/a (action) |
The FOR SECONDARY variant lets you set DSC on a primary so that the configuration applies to the secondary when the database is part of an availability group or geo-replication relationship. This is the way, for example, to disable parameter sniffing on the readable secondary only.
ELEVATE_ONLINE, ELEVATE_RESUMABLE, and Identity Cache
ELEVATE_ONLINE = ON makes index operations run online where the engine supports it, without requiring you to write ONLINE = ON in every ALTER INDEX statement. ELEVATE_RESUMABLE = ON does the same for resumable operations. Both are Enterprise-only on SQL Server and are typically paired: enabling both gives you a database where maintenance jobs "just run" without locking tables. They are supported on Azure SQL DB and MI in the platform.
IDENTITY_CACHE = ON (the default) caches identity values to speed up bulk inserts, but a failover can leave a gap in the identity sequence. Workloads that need contiguous identity values (some financial or compliance systems) set IDENTITY_CACHE = OFF and accept the performance cost.
Clearing the Plan Cache Per Database
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE flushes the plan cache for only that database, unlike DBCC FREEPROCCACHE which clears the entire instance cache (and on Azure SQL DB, the server). Use it after a stats update, an index change, or a DSC change that you expect to produce new plans, without disrupting other tenant databases on the same server or instance.
Compatibility Level and DSC Interaction
DSC options interact with the database compatibility level. Changing the compatibility level is the gating switch for Intelligent Query Processing (IQP) features (covered in 10.4), while DSC options tune the optimizer behavior within that level. A typical post-upgrade recipe:
- Upgrade the engine to SQL Server 2022 (or move the database to Azure SQL DB).
- Keep the database compatibility level at the pre-upgrade value (for example, 110).
- Enable Query Store and let it baseline workload performance.
- Enable DSC options as needed (
MAXDOP,QUERY_OPTIMIZER_HOTFIXES = ON). - Raise the compatibility level one step at a time (130, 140, 150, 160), watching Query Store for regressions.
This is the Database Upgrade process Microsoft recommends — never raise the compat level immediately after an engine upgrade; use Query Store to validate first. Exam scenarios that ask "after upgrade, queries regressed, what next?" point to this process. DMA (Data Migration Assistant) and Query Store's regressed-queries report are the tools to validate each step.
You want to enable post-RTM query optimizer hotfixes for one database on a SQL Server 2022 instance without affecting other databases on the same instance. What should you do?