Efficient index management in Azure SQL Database using Index Advisor

We are happy to share with you another article from the Microsoft Azure cloud services series. This time, Andrei Antyufeev, Microsoft program manager from the SQL Server team and the Azure SQL Database, will continue his story about working with indexes in the Azure SQL Database cloud database. - Vladimir Yunev

Hello everyone, this note will be useful to everyone using the Azure SQL Database.


Last time , we reviewed the first version of Index Advisor. Since then, the assistant has managed to grow to GA, increasing stability, and acquire new features:

• ability to automatically apply the recommendations of the assistant
• new recommendations (Drop Index)
• load visualization after index creation
• other improvements

Forget about managing indexes

The process of creating new indexes occurs in several stages:

1. Index Advisor provides user recommendations indexes that will give a performance boost;
2. The user selects which recommendation to apply, the recommendation goes to the “Pending” state;
3. At this time, Index Advisor makes a control measurement of your load and creates an index;
4. In the last stage, IA again measures performance and, if it is lower than before the operation with indexes, the operation is canceled. Index in the status of “Reverted”.

Fig. 1. - Properties of the index, the effect of which was negative. The operation can be repeated by clicking the “Revert” button.
')
After some time, the load on the database may change, and a new index will be needed, or the old index will become unnecessary. The user will have to run this process again and again.

Autopilot

For those who are tired of having to think about indexes, we created the autopilot function - it automatically applies new recommendations and rolls back changes if the effect is negative.


Fig. 2. - Enable autopilot in the settings of Index Advisor


Fig. 3. - The properties of the completed operations will show who initiated it: the user or the autopilot.

“I gave birth to you, I will kill you”

Index Advisor constantly monitors telemetry and database load - if it detects an index that no longer does any good or is duplicated by some other index, then it will offer to delete it.


For example, in the internal testing stage, we found a database with 72 identical indices that simply took up space. This was due to a third-party index administration program that did not work as it should.

To remove indexes, the distinctive feature of Index Advisor becomes even more important: verification after . Through the use of the Query Store, Index Advisor knows what the effect of creating or deleting an index will be. If it turns out to be negative, the assistant will automatically return everything as it was.

Additional notifications

Every day we publish thousands of recommendations. If they are all applied, the performance of SQL Azure as a whole and for customers will significantly increase. Therefore, we will try to convey these recommendations to users as soon as possible.

The first step is the Status bar on the database screen:


If you have active recommendations, then when you visit the new Azure portal, you will see a notification inviting them to apply them. In the near future, we plan to make regular notifications about the availability of recommendations in the Index Advisor.

This is not a hoax! It works

After creating or deleting an index, you can see in the operation properties how the database parameters have changed:

To do this, select the operation that ended successfully, and run Query Insights:


As you can see, the index was created on the 6th, and the total DTU consumption fell from 80% to 40%, and the CPU consumption by requests dropped. (The improvement is not always so significant, but it is always there)

Coincidence? I do not think. Query Performance Insight is a useful tool in itself, and I’m going to tell you about it in the next article.

Why do I have no recommendations?

So it may happen that your database does not need additional indexes at the moment. Then Index Advisor will display a message explaining the lack of recommendations. The reasons may be as follows:

1. We have analyzed your database, and we have nothing to add or remove (you are a great developer!);
2. We do not have enough data for reliable recommendations. This happens for several reasons:
3. We cannot make changes to the database structure (for example, if it is marked as Read-only);
4. At the moment, there are other index change operations for your database (new recommendations may appear after completion of all planned changes);
5. Index Advisor has a day off (and it happens).

The list may change over time, but we will always try to give a specific reason.

Other

Small changes like:

• Syntax highlighting in the script field for manual implementation of the recommendation (Index Details -> “View Script”);
• If the application of the recommendation fails, the reason will be indicated in the properties of the recommendation.

New recommendation model

As you might have guessed, administering indexes is only the first step. Over time, we hope to expand our Advisor with new tips and tricks.

At first they will be launched as a pre-order and marked with an asterisk, for example, Drop Index.

Total

Index Advisor is now in GA stage, it will help you find the missing indexes (and also offer to remove unnecessary ones) and improve the performance of your database.

Turning on the autopilot Index Advisor, you can leave to forget about managing indexes, the cloud will do everything for you.

Feedback

• It would be very cool to hear the opinions / comments of experienced DBA and new Code-first developers. You can start a discussion here.
• On the Index Advisor page there is a Feedback button, where you can express an opinion on a specific index.
• Would you like to send greetings to SQL Server / SQL Azure teams: anant@microsoft.com I will try to find the desired destination.

In the second part, we will look at Query Performance Insight, a tool that shows the top 10 most voracious queries in your database.

about the author

Andrey Antyufeev



SQL Server & Azure SQL Database Program Manager, Microsoft
sitox