what is the consideration for reverting the automatic index recommendation in Azure sql database

Question

Want to know about the Automatic index recommendations in Azure SQL database.

On what consideration(base) the automatic index recommendations will be implemented?
Also, what is the consideration for reverting the index? How long it will consider before reverting the index
In case creation of index failed due to the high CPU/IO, what is the frequency to re-checking the implementing of those failed indexes

Answer 1

Hi, ShannuD-5262 Thanks for posting your question in Microsoft Q&A,
Here are the answers to the above questions.

Azure SQL Database has an intelligent automatic tuning mechanism that takes care of your indexes. If you cannot identify and monitor your indexes, you can let the Azure SQL database do index management for you, tune your database, and ensure that your data structures dynamically adapt to your workload. Here is the documentation and blog which you can refer to.

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.

I am not sure on the rechecking frequency of failed index however in case if the index fails/is postponed due to high CPU, IO, low Storage – once the resource utilization is normal and the automatic tuning believes the index would be beneficial, the process(creation of index) will start again.