This article will explore automatic index advisor (CREATE_INDEX, DROP_INDEX) for Azure SQL Database.
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.
- 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.
Azure database successfully applied the recommendation. It has validated the performance as well post-implementation.
The recommendation is scheduled for execution.
The Index recommendations apply process is running.
Azure has applied index recommendations. However, its validation is in progress.
There is an error while implementing the index recommendation. It can be due to a transient issue or schema change.
Azure applied the index recommendation, but during validation, it does not observe performance improvement. Therefore, it is reverting (rollback)the changes.
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 ,*
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.
SELECT name, desired_state_desc, actual_state_desc, reason_desc
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
- 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
- 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
- The index is a duplicate (same index and included columns)
- The index is not used for a prolonged 93 days period
- Exploring Azure SQL Database Geo-Restore - September 2, 2022
- Deploy a SQL Server container using Azure Kubernetes Services (AKS) - August 31, 2022
- Deploy Azure SQL Elastic Pools for Azure SQL Database - August 23, 2022
However, if you modify the inheritance property to Azure defaults for an individual database, the value for reason_desc changes to Auto_Configured.
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:
AS (SELECT ddtr.reason,
'$.currentValue') AS CurrentState,
'$.reason') AS CurrentStateReason,
'$.implementationDetails.script') AS ImplementationScript
FROM sys.dm_db_tuning_recommendations AS ddtr
WITH (query_id INT '$.queryId',
regressedPlanId INT '$.regressedPlanId',
recommendedPlanId INT '$.recommendedPlanId') AS pfd)
CAST(rp.query_plan AS XML) AS RegressedPlan,
CAST(sp.query_plan AS XML) AS SuggestedPlan,
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;
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.
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:
Azure SQL Database also monitors workload performance after dropping an index automatically. If the performance degrades, it reverts the recommendations (recreate the index).
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.