Rajendra Gupta
automatic tuning options

Automatic Index advisor in Azure SQL database

October 26, 2021 by

This article will explore automatic index advisor (CREATE_INDEX, DROP_INDEX) for Azure SQL Database.

Introduction

Azure SQL Database enables its users to focus on database objects, tuning queries without managing the underlying infrastructure resources. Database professionals always focus on indexes for improving query performance on both on-premises and Azure cloud databases. These indexes help the query optimizer engine to retrieve data quickly and serve client requests. You might have seen missing index recommendations for on-premises SQL Server in the actual execution plan or using DMV sys.dm_db_missing_index_details.

A DBA can never follow these missing indexes recommendations as it might hamper other queries if indexes are not designed properly. These unused indexes can increase WRITELOG wait statistics because indexes require additional logging. Similarly, a new index can decrease data updates without providing any benefits to read queries.

This article discusses the automatic index advisor or index creation for the Azure SQL Database.

Azure Automatic database tuning features of Azure databases

Azure SQL Database has an in-built service to monitor queries continuously and provide recommendations for the indexes based on the query workload. Users can review these indexes and apply them manually or enable the auto-tuning option so that Azure can automatically apply them. Azure database continuously learns from workload behavior and ensures that the database is optimally indexed. It uses built-in intelligence and advanced configured rules to predict optimal indexes. Using this feature, Azure SQL Database ensures optimizing the majority of workload (queries) for reading data with minimal impact on updating data.

The query store is enabled for Azure SQL Databases by default. It gives recommendations for CREATE INDEX, DROP INDEX that can improve query performance. Azure SQL Database monitors the query performance after an index is created using automatic tuning. The automatic indexing uses data from the missing index DMV, and it monitors the recommendation over time using the query store. If it does not detect any performance improvement, it automatically reverts the recommendations.

By default, Azure default for automatic tuning index is below:

  • CREATE_INDEX = Disabled
  • DROP_INDEX = Disabled

You can enable these automatic tuning options at the server level or database level. If you configured settings at the server level, all databases would inherit these settings. However, you have an option to disable them on an individual database using the Azure portal or Azure CLI.

Automatic Tuning Azure SQL Database

To view the default automatic index tuning, navigate to Intelligent Performance -> Automatic tuning for Azure SQL Server in the Azure portal. As shown below, the default configuration is to inherit from the Azure defaults.

automatic tuning options

You can also customize these automatic tuning for the individual database. In the Azure portal, navigate to Performance recommendations -> Automate.

Here, you get the following inheritance options to choose from.

Inherit from:

  • Server: In this option, the Azure SQL Database gets automatic tuning configurations from the Azure Server
  • Azure default: It uses Azure default configurations ( Create Index = Off, Drop Index = Off)
  • Don’t inherit: You can specify the tuning configuration for the specific database

Inherit DB properties

Azure combines the recommendations based on their potential performance impact.

  • High Impact: These recommendations provide the most significant performance improvement
  • Medium: This recommendation improves the performance noticeably
  • Low: These recommendations provide considerable performance impact

The following image shows a few high impacts create and drop index recommendations.

Index and their impact

Image reference: Microsoft Tech Community

You can open the create index recommendation, and it gives information such as table name, included columns, impact, disk space needed, status. Click on the view script to check the index script for review or deploy it manually.

Index details

Image reference: Microsoft Docs

Azure might not immediately apply the recommendation automatically. It monitors the workload and resource consumption before using these recommendations. You can watch the status column that has the following values.

Status

Description

Success

Azure database successfully applied the recommendation. It has validated the performance as well post-implementation.

Pending

The recommendation is scheduled for execution.

Executing

The Index recommendations apply process is running.

Validating

Azure has applied index recommendations. However, its validation is in progress.

Error

There is an error while implementing the index recommendation. It can be due to a transient issue or schema change.

Reverting

Azure applied the index recommendation, but during validation, it does not observe performance improvement. Therefore, it is reverting (rollback)the changes.

Reverted

The rollback process is finished. Azure database has reverted to the original state in terms of indexing for specific objects.

Find out index created by Azure Index recommendations

You can query the index view sys.indexes to find out indexes created automatically by the Azure Index recommendations. Query this view and look at the column auto_created.

  • Auto_created 1: Index created by automated tuning
  • Auto_created 0: Manually created index

The following screenshot references the sys.indexes view in the Azure database.

Sys.indexes view

  • Note: The performance tuning recommendation or the automatic tuning is an online process. Azure does not take a database offline for applying these recommendations

Enable or disable automatic tuning configuration for an Azure SQL Database

You can query system view sys.database_automatic_tuning_options to check the automatic tuning options configuration.

The below screenshot shows that, by default, CREATE_INDEX and DROP_INDEX options are disabled, and its reason is that this configuration is inherited from Azure SQL Server.

Index advisor DMV

  • Note: Here, we do see an additional option MAINTAIN_INDEX. However, there is no official documentation around it as of now. It might be related to the future enhancement for Azure SQL Database
  • However, if you modify the inheritance property to Azure defaults for an individual database, the value for reason_desc changes to Auto_Configured.

    Modify database inherit property

    Auto configured values

    • Note: If Azure disabled the automatic tuning due to high resource utilization or query store status changes to Read-only, you might see value Disabled by the system in the reason_desc column. It indicates that Azure has temporarily disabled the automatic tuning feature of the Azure SQL Database

    Index recommendation DMV – sys.dm_db_tuning_recommendations

    You can also utilize the dynamic management view sys.dm_db_tuning_recommendations for identifying query performance regression and recommendations for the CREATE_INDEX and DROP_INDEX. Even If you have not enabled the automatic tuning for a database, you can query this DMV for recommendations.

    The following query (reference: Grant Fritchey) can help you in identifying query and their recommendations:

    Importance points

    CREATE_INDEX

    Azure uses a back-off policy that prevents it from applying index recommendations during high resource utilization. It considers CPU, Log IO, Data IO, and storage.

    • If the CPU, Data, and Log IO utilization is greater than 80% in the previous 30 minutes, Azure postpones creating index recommendations
    • Suppose the available storage is less than 10% after the index creation. In this case, the index recommendation status changes to error. Once the space is available, it might process the index again based on future workloads and utilization

    DROP_INDEX

    In addition to the new indexes, Azure analyzes existing index performance as well. If the index is not used, it raises a recommendation to drop it. It considers dropping index in the following cases:

    • The index is a duplicate (same index and included columns)
    • The index is not used for a prolonged 93 days period

    Azure SQL Database also monitors workload performance after dropping an index automatically. If the performance degrades, it reverts the recommendations (recreate the index).

    Conclusion

    Azure SQL Database intelligent automatic tuning mechanism automatically recommends suitable indexes to create or drop based on the workload, performance review. Azure takes care of the index management and runs optimized queries for better resource utilization and throughput if you are not an expert.

    Rajendra Gupta
    Latest posts by Rajendra Gupta (see all)
Azure, Indexes, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

360 Views