Automatic Index advisor in Azure SQL database

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.

Note: I would suggest you refer SQLShack articles on performance tuning using indexes

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.

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.

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

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.

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.

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.

SELECT auto_created,object_name(object_id) AS ObjectName, type_desc ,*

FROM sys.indexes

WHERE object_name(object_id) LIKE 'Sales%'

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.

1 2	SELECT name, desired_state_desc, actual_state_desc, reason_desc FROM sys.database_automatic_tuning_options

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.

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.

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

1	ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO /* Allowed values : Auto, INHERIT, CUSTOM */

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:

WITH DbTuneRec

AS (SELECT ddtr.reason,

ddtr.score,

pfd.query_id,

pfd.regressedPlanId,

pfd.recommendedPlanId,

JSON_VALUE(ddtr.state,

'$.currentValue') AS CurrentState,

JSON_VALUE(ddtr.state,

'$.reason') AS CurrentStateReason,

JSON_VALUE(ddtr.details,

'$.implementationDetails.script') AS ImplementationScript

FROM sys.dm_db_tuning_recommendations AS ddtr

CROSS APPLY

OPENJSON(ddtr.details,

'$.planForceDetails')

WITH (query_id INT '$.queryId',

regressedPlanId INT '$.regressedPlanId',

recommendedPlanId INT '$.recommendedPlanId') AS pfd)

SELECT qsq.query_id,

dtr.reason,

dtr.score,

dtr.CurrentState,

dtr.CurrentStateReason,

qsqt.query_sql_text,

CAST(rp.query_plan AS XML) AS RegressedPlan,

CAST(sp.query_plan AS XML) AS SuggestedPlan,

dtr.ImplementationScript

FROM DbTuneRec AS dtr

JOIN sys.query_store_plan AS rp

ON rp.query_id = dtr.query_id

AND rp.plan_id = dtr.regressedPlanId

JOIN sys.query_store_plan AS sp

ON sp.query_id = dtr.query_id

AND sp.plan_id = dtr.recommendedPlanId

JOIN sys.query_store_query AS qsq

ON qsq.query_id = rp.query_id

JOIN sys.query_store_query_text AS qsqt

ON qsqt.query_text_id = qsq.query_text_id;

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.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023