Study guide
10.2 Automatic Tuning and Database-Scoped Configuration
DP-300: Administering Microsoft Azure SQL Solutions Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources
2 Planning and Deploying Azure SQL Solutions
2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching
3 Configuring Resources for Scale and Performance
3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression
4 Planning and Implementing a Migration Strategy
4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting
5 Configuring Database Authentication and Authorization
5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management
6 Implementing Security for Data at Rest and in Transit
6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints
7 Implementing Compliance Controls for Sensitive Data
7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL
8 Monitoring Resource Activity and Performance
8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events
9 Monitoring and Optimizing Query Performance
9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights
10 Configuring Database Solutions for Optimal Performance
10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing
11 Creating and Managing SQL Server Agent Jobs
11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates
12 Automating Deployments and Database Tasks in Azure
12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks
13 Planning an HA/DR Strategy and Backup and Restore
13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations
14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups
14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups
15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting
15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions
10.2 Automatic Tuning and Database-Scoped Configuration
Key Takeaways Explain This Topic AI
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.
Last updated: August 2026
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.