Skip to main content

Automatic Tuning in Azure SQL Database

Azure SQL Database includes an "Automatic Tuning" feature that continuously monitors database workloads and automatically identifies and applies actions to improve performance.

What is Automatic Tuning?​

Automatic Tuning is a performance tuning service based on AI and machine learning. Its main features include:

  • Performance Monitoring: Constantly monitors database workloads.
  • Recommendations: Proposes tuning actions to improve performance.
  • Automatic Application: Automatically applies recommendations when set to AUTO mode.

Main Tuning Options​

OptionDescription
FORCE_LAST_GOOD_PLANDetects regressed execution plans (slower query plans) and forces the use of the last known good plan.
CREATE_INDEXProposes and creates indexes that may improve performance.
DROP_INDEXDrops indexes that have not been used for a long time or are duplicates. (Note: Indexes involved in constraints like unique indexes are not dropped)

Note: For Azure SQL Managed Instance, only FORCE_LAST_GOOD_PLAN is currently supported and can only be configured via T-SQL.

Enabling at the Server Level​

Settings configured at the server level are inherited by all databases on that server (unless individually overridden).

Using Azure Portal​

  1. Navigate to the target SQL Server (logical server) in the Azure Portal.
  2. Select "Automatic tuning" from the left menu.
  3. Select the options you want to enable and click "Apply".

The Azure defaults are:

  • FORCE_LAST_GOOD_PLAN: Enabled
  • CREATE_INDEX: Disabled
  • DROP_INDEX: Disabled

Enabling at the Database Level​

For individual databases, you can choose to inherit server settings or configure unique settings.

Using Azure Portal​

  1. Navigate to the target SQL Database in the Azure Portal.
  2. Select "Automatic tuning" from the left menu.
  3. Configure via the "Inherit from server" tab or set individual options, then click "Apply".

Using T-SQL​

You can change settings by connecting to the database and running the following queries:

-- To inherit settings from the server
ALTER DATABASE current SET AUTOMATIC_TUNING = INHERIT;

-- To configure individually (e.g., enabling FORCE_LAST_GOOD_PLAN)
ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Automatic Tuning vs. IaC/Migrations​

Enabling CREATE_INDEX or DROP_INDEX allows Azure SQL Database to automatically modify the schema (indexes). However, if you manage your DB schema using Entity Framework Core (EF Core) Migrations or IaC tools like Terraform, this leads to drift between the code definition and the actual database state.

Potential Issues​

  • Schema Inconsistency: Automatically created indexes are not in your migration files, so they won't appear in scripts generated by dotnet ef migrations script.
  • Recreation Risk: During the next deployment or migration application, your tool might view these changes as "unintended" and attempt to drop the auto-created indexes, or recreate indexes that were removed by DROP_INDEX.

Best Practices​

For environments using IaC or Migration tools, the following operational practices are recommended:

  1. Disable automatic application of CREATE_INDEX / DROP_INDEX in Production

    • Recommended setting: Enable only FORCE_LAST_GOOD_PLAN and keep index operations OFF.
    • FORCE_LAST_GOOD_PLAN only prevents plan regression and does not affect schema definitions, so it does not conflict with migration tools.

  2. Use Automatic Tuning for "Discovery"

    • Regularly check "Recommendations" in development, staging, or even production environments without auto-application.

    • Review recommendations in the Azure Portal or via the sys.dm_db_tuning_recommendations view. If an index is beneficial, manually add it to your EF Core model or migration files.

      Example query to check recommendations:

      SELECT
          name,
          reason,
          state,
          JSON_VALUE(details, '$.implementationDetails.script') as script,
          details
      FROM sys.dm_db_tuning_recommendations
      WHERE reason LIKE '%Create_Index%';

    • This allows you to benefit from automatic tuning insights while keeping your codebase (migration files) as the "Single Source of Truth".

  3. Risks of DROP_INDEX

    • Automatic tuning identifies "unused indexes" as candidates for deletion. However, it might mistakenly remove critical indexes used only rarely (e.g., for annual financial closing processes).
    • To prevent critical indexes explicitly defined in your application logic from being deleted, it is generally recommended to keep this option disabled.

Troubleshooting​

Automatic Recommendation Management is Disabled​

If you see a message stating "Automatic recommendation management has been disabled", possible causes include:

  • Query Store Disabled: Check if the Query Store is enabled.
  • Read-Only Mode: The database might be in read-only mode, or the Query Store might be full and switched to read-only.

Consider adjusting the Query Store data retention period or increasing the maximum size.

Permissions​

To manage Automatic Tuning, you need at least SQL Database Contributor role membership.