Automatic Tuning default disabled in new Azure SQL Databases

Default State of Automatic Tuning

Automatic Tuning is disabled by default for newly created Azure SQL Databases. Administrators must explicitly enable the specific tuning features—such as Force Last Good Plan, Create Index, and Drop Index—on a per-database basis. There is currently no official documentation indicating a planned shift to an "enabled by default" state for future releases.

Resource Usage and Billing in Elastic Pools

Because Automatic Tuning is a database-level setting, there is no pool-wide toggle. When enabled within an elastic pool, the resource impact is as follows:

• Resource Consumption: Operations like automatic index creation consume CPU and IO from the pool's shared resource budget (eDTUs or vCores). This means a tuning operation in one database can potentially create resource pressure for other databases sharing the same pool.
• Billing: There is no separate line-item charge for Automatic Tuning. Billing remains tied to your service tier or elastic pool allocation. However, inefficient tuning or frequent index rebuilds can lead to higher overall resource utilization, which may necessitate a tier upgrade if the pool reaches its limits.

Mitigating Performance Regressions

To prevent transient performance dips when enabling Automatic Tuning, consider these mitigations:

• Staged Enablement: Enable features one by one (e.g., start with Force Last Good Plan, which has lower overhead than Create Index) to monitor the impact on your specific workload.
• Off-Peak Scheduling: While the engine manages the timing, monitoring the impact during known high-traffic windows allows you to determine if the resource spikes are acceptable.
• Baseline Monitoring: Establish a performance baseline using Query Store before enabling tuning to accurately identify if a regression is caused by the tuning process or the underlying query.

Verification Commands

To verify the current status of tuning options for your database, execute the following query:

SELECT name, desired_state, actual_state 
FROM sys.database_automatic_tuning_options;

To track the history of actions taken by the engine, use:

SELECT * FROM sys.dm_db_automatic_tuning;

Diagnostic Detail Needed: To provide a more tailored resource recommendation, please specify if your elastic pool is currently operating near its CPU or IO limits during peak hours.