Efficient index management in Azure SQL Database with Index Advisor

We are pleased to share with you another article in a series of articles about Microsoft Azure cloud services. This time, Andrei Antyufeev, Microsoft Program Manager from the SQL Server and Azure SQL Database teams, will continue his talk about working with indexes in the Azure SQL Database cloud database. - Vladimir Yunev

Hello everyone, this post will be useful to everyone who uses Azure SQL Database.


The last time , we surveyed the first version of the Index Advisor. Since then, the assistant managed to grow to GA, increasing stability, and gaining new opportunities:

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

Forget about index management

The process of creating new indexes takes place in several stages:

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

Fig. 1. - Properties of the index, the effect of which was negative. The operation can be repeated by pressing the “Revert” button.

After some time, the load on the database may change, and a new index will be needed or the old one will become unnecessary. The user will have to start 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 the changes if the effect turned out to be negative.


Fig. 2. - You can enable autopilot in the settings of Index Advisor


Fig. 3. - The properties of the completed operations will show who initiated it: user or 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 benefits or is duplicated by some other index, then it will offer to delete it.


For example, in the internal testing phase, we found a database with 72 identical indexes 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 distinguishing feature of the 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, then 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 increase significantly. Therefore, we will try to convey these recommendations to users as quickly 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 you 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 the 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 CPU consumption by requests fell. (Not always an improvement is so significant, but it is always there)

Coincidence? I don’t think so. Query Performance Insight is a useful tool in itself, and I’m going to talk about it in the next article.

Why don't I have any recommendations?

It may so happen that your database does not need additional indexes at the moment. Then the Index Advisor will show 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, other operations have been launched to change indexes for your database (new recommendations may appear after all planned changes are completed);
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

Minor changes like:

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

New recommendation model

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

First they will be launched as a preview and marked with an asterisk, for example, Drop Index.

Total

Index Advisor is now in the GA stage, it will help you find the missing indexes (as well as suggest removing unnecessary ones) and improve the performance of your database.

By enabling Index Advisor autopilot, you can leave behind forgetting about index management, the cloud will do everything for you.

Feedback

• It would be really cool to hear the opinions / comments of seasoned DBAs and new Code-first developers. You can start the discussion here.
• There is a Feedback button on the Index Advisor page, where you can give an opinion on a specific index.
• Want to say hello to SQL Server / SQL Azure teams: anant@microsoft.com I ’ll try to find the right recipient.

In the second part, we'll look at Query Performance Insight, a tool that shows the TOP-10 most gluttonous queries in your database.

about the author

Andrey Antyufeev



Program Manager SQL Server & Azure SQL Database, Microsoft
sitox