Azure SQL Automatic Tuning: Self-Healing Database Performance

Azure SQL’s Automatic Tuning uses machine learning to continuously monitor and improve database performance. It can automatically create indexes, drop unused indexes, and force optimal query plans.

Enabling Automatic Tuning

-- Enable all automatic tuning options
ALTER DATABASE MyDatabase
SET AUTOMATIC_TUNING (
    FORCE_LAST_GOOD_PLAN = ON,
    CREATE_INDEX = ON,
    DROP_INDEX = ON
);

-- Check current automatic tuning state
SELECT
    name,
    desired_state_desc,
    actual_state_desc,
    reason_desc
FROM sys.database_automatic_tuning_options;

Using Azure CLI:

# Enable automatic tuning at server level
az sql server configuration set \
    --resource-group myResourceGroup \
    --server myserver \
    --name "createIndex" \
    --value "On"

az sql server configuration set \
    --resource-group myResourceGroup \
    --server myserver \
    --name "dropIndex" \
    --value "On"

az sql server configuration set \
    --resource-group myResourceGroup \
    --server myserver \
    --name "forceLastGoodPlan" \
    --value "On"

Understanding Automatic Index Management

-- View automatic tuning recommendations
SELECT
    name,
    reason,
    score,
    details,
    state_desc,
    is_executable_action,
    is_revertable_action,
    execute_action_start_time,
    execute_action_duration,
    revert_action_start_time
FROM sys.dm_db_tuning_recommendations
ORDER BY score DESC;

-- View index recommendations specifically
SELECT
    r.name AS recommendation_name,
    r.reason,
    r.score,
    JSON_VALUE(r.details, '$.implementationDetails.script') AS create_script,
    JSON_VALUE(r.details, '$.indexName') AS index_name,
    JSON_VALUE(r.details, '$.schema') AS schema_name,
    JSON_VALUE(r.details, '$.table') AS table_name,
    JSON_VALUE(r.details, '$.columns') AS index_columns
FROM sys.dm_db_tuning_recommendations r
WHERE r.type = 'CREATE_INDEX'
ORDER BY r.score DESC;

Monitoring Automatic Plan Correction

-- Find queries where plan was auto-corrected
SELECT
    qsq.query_id,
    qsqt.query_sql_text,
    qsp.plan_id,
    qsp.is_forced_plan,
    qsp.force_failure_count,
    qsp.last_force_failure_reason_desc,
    rs.avg_duration AS current_avg_duration
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats rs ON qsp.plan_id = rs.plan_id
WHERE qsp.is_forced_plan = 1
ORDER BY rs.avg_duration DESC;

-- View plan forcing history
SELECT
    query_id,
    plan_id,
    force_failure_count,
    last_force_failure_reason_desc,
    count_forced_plan_executions,
    last_forced_plan_execution_time
FROM sys.query_store_plan
WHERE force_failure_count > 0;

Building an Auto-Tuning Dashboard

# Python script to monitor automatic tuning
import pyodbc
import pandas as pd
from datetime import datetime, timedelta

class AutoTuningMonitor:
    def __init__(self, connection_string):
        self.connection_string = connection_string

    def get_recommendations(self):
        query = """
        SELECT
            name,
            type,
            reason,
            score,
            state_desc,
            is_executable_action,
            execute_action_start_time,
            execute_action_duration,
            JSON_VALUE(details, '$.implementationDetails.script') AS script
        FROM sys.dm_db_tuning_recommendations
        WHERE state_desc IN ('Active', 'Verifying', 'Success')
        ORDER BY score DESC
        """

        with pyodbc.connect(self.connection_string) as conn:
            return pd.read_sql(query, conn)

    def get_applied_recommendations(self, days_back=30):
        query = f"""
        SELECT
            name,
            type,
            reason,
            score,
            state_desc,
            execute_action_start_time,
            execute_action_duration,
            revert_action_start_time
        FROM sys.dm_db_tuning_recommendations
        WHERE execute_action_start_time >= DATEADD(DAY, -{days_back}, GETDATE())
        ORDER BY execute_action_start_time DESC
        """

        with pyodbc.connect(self.connection_string) as conn:
            return pd.read_sql(query, conn)

    def get_performance_improvement(self):
        query = """
        SELECT
            r.name,
            r.type,
            r.score,
            JSON_VALUE(r.details, '$.estimatedImpact[0].deltaValue') AS estimated_impact,
            JSON_VALUE(r.details, '$.estimatedImpact[0].unit') AS impact_unit,
            r.execute_action_start_time,
            r.state_desc
        FROM sys.dm_db_tuning_recommendations r
        WHERE r.state_desc = 'Success'
        AND r.execute_action_start_time >= DATEADD(DAY, -7, GETDATE())
        ORDER BY estimated_impact DESC
        """

        with pyodbc.connect(self.connection_string) as conn:
            return pd.read_sql(query, conn)

    def generate_report(self):
        recommendations = self.get_recommendations()
        applied = self.get_applied_recommendations()
        improvements = self.get_performance_improvement()

        report = f"""
        Automatic Tuning Report - {datetime.now().strftime('%Y-%m-%d')}
        ================================================

        Active Recommendations: {len(recommendations)}
        Applied in Last 30 Days: {len(applied)}

        Top Recommendations by Score:
        {recommendations.head(10).to_string()}

        Recent Improvements:
        {improvements.head(10).to_string()}
        """

        return report

# Usage
monitor = AutoTuningMonitor(connection_string)
print(monitor.generate_report())

Handling Automatic Tuning Events

// C# Azure Function to respond to tuning events
public class AutoTuningAlertFunction
{
    [FunctionName("ProcessAutoTuningAlert")]
    public async Task Run(
        [ServiceBusTrigger("auto-tuning-alerts")] string message,
        ILogger log)
    {
        var alert = JsonConvert.DeserializeObject<AutoTuningAlert>(message);

        switch (alert.Type)
        {
            case "CREATE_INDEX":
                log.LogInformation($"Index created: {alert.IndexName} on {alert.TableName}");
                await NotifyTeam(alert);
                break;

            case "DROP_INDEX":
                log.LogWarning($"Index dropped: {alert.IndexName}");
                await NotifyTeam(alert);
                break;

            case "FORCE_PLAN":
                log.LogInformation($"Plan forced for query {alert.QueryId}");
                await AnalyzePlanForcing(alert);
                break;
        }
    }

    private async Task NotifyTeam(AutoTuningAlert alert)
    {
        // Send notification via Teams, Slack, or email
    }

    private async Task AnalyzePlanForcing(AutoTuningAlert alert)
    {
        // Additional analysis for plan corrections
    }
}

public class AutoTuningAlert
{
    public string Type { get; set; }
    public string IndexName { get; set; }
    public string TableName { get; set; }
    public long? QueryId { get; set; }
    public double Score { get; set; }
}

Best Practices

1. Start with observation: Enable recommendations but review before auto-apply
2. Monitor revertions: High revert rates indicate unstable workloads
3. Combine with Query Store: Use Query Store data for deeper analysis
4. Set up alerts: Get notified when significant changes occur

Automatic tuning reduces the operational burden of database performance management while ensuring your databases adapt to changing workload patterns.