Automatic Tuning for Azure SQL Database

Azure SQL Database automatic tuning helps you continuously monitor your database and apply performance tuning recommendations. It automatically handles performance optimizations that were previously done manually by a database administrator.

What is Automatic Tuning?

Automatic tuning is a feature of Azure SQL Database that identifies and resolves performance issues. It focuses on specific recommendations that can improve performance with minimal or no impact on your workload.

Index Management: Automatically creates and drops indexes to improve query performance.
Plan Forcing: Forcing a query to use a specific execution plan to avoid performance regressions.

Automatic tuning uses intelligent algorithms to analyze your workload and apply recommendations only when they are beneficial. It's designed to be safe and effective.

Key Capabilities

Automatic tuning offers two primary capabilities:

Index Management:
This capability analyzes your query workload and identifies potential missing or redundant indexes. Azure SQL Database can then automatically create new indexes that are expected to improve performance or drop unused indexes that may be slowing down DML operations.

Automatic Index Creation:

Automatic Index Dropping:
Plan Forcing:
This capability helps protect your applications from performance degradation caused by query plan changes. If a query's execution plan changes and causes a performance regression, Azure SQL Database can automatically detect this and revert to a previously known good plan.

Automatic Plan Forcing:

How Automatic Tuning Works

Automatic tuning works by observing your database workload over time. It collects query performance data and uses machine learning to identify patterns and potential optimizations. When a recommendation is found that is predicted to significantly improve performance without negatively impacting your workload, it's applied.

You can choose to have Azure SQL Database automatically apply these recommendations, or you can review them and apply them manually.

Configuring Automatic Tuning

You can configure automatic tuning settings at the database level using the Azure portal, PowerShell, or T-SQL.

Using the Azure Portal

Navigate to your Azure SQL Database in the Azure portal.
In the left-hand menu, under "Settings", select Automatic tuning.
Choose the tuning options you want to enable (Index recommended, Plan forcing recommended). You can also choose to let Azure automatically apply these options.
Click Save.

Using T-SQL

You can manage automatic tuning settings using the following T-SQL commands:


-- Enable index management and plan forcing recommendations
ALTER DATABASE [YourDatabaseName]
SET AUTOMATIC_TUNING (INDEX_KEY = ON, FORCE_LAST_GOOD_PLAN_KEY = ON);

-- Disable index management recommendations
ALTER DATABASE [YourDatabaseName]
SET AUTOMATIC_TUNING (INDEX_KEY = OFF);

-- Disable plan forcing recommendations
ALTER DATABASE [YourDatabaseName]
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN_KEY = OFF);

Monitoring and Recommendations

The Automatic Tuning page in the Azure portal also provides a dashboard to view existing recommendations. You can review the details of each recommendation, see the expected performance impact, and choose to apply, dismiss, or script the recommendation.

It's recommended to regularly review the performance recommendations provided by automatic tuning, even if you have automatic application enabled, to understand the optimizations being made to your database.

Best Practices

Start by enabling automatic tuning for recommendations only, to review before applying.
Gradually enable automatic application of recommendations as you gain confidence.
Monitor your database performance closely after enabling automatic tuning.
Consider using query performance insights to complement automatic tuning analysis.

While automatic tuning is designed to be safe, it's always a good practice to have a rollback strategy in place, especially for critical production workloads.