Will Microsoft Azure be able to find an index that your DBA couldn't find?

A small but interesting announcement for those who use Azure SQL DB.
On July 1, he went into public testing of Index Advisor for the Azure SQL Database.

Index Advisor is a tool available to all users of Azure SQL DB (V12) to create non-clustered indexes. Index Advisor (IA) provides a list of recommended indexes for your database. Helps to create them, as well as automatically test! Analyzing the load of requests and the nature of data access, IA selects those indices that, in his opinion, will bring the greatest performance gains individually for your scenario.


')

This is the main panel of Index Advisor.

On the Microsoft Azure portal, the assistant displays a table with recommended indexes. Each entry characterizes the index by the positive impact on the database, shows the columns and the table on which the index will be created, as well as the time to create the recommendation.

What is the attractiveness of Index Advisor?

Index Advisor works completely transparent to the user. First, he selects a list of several indexes for you, the creation of which will maximize the performance of the database.
Then, you "order" the creation of the index, and within 48 hours you will receive a report on the work done.

Why does it take 48 hours to create an index?

The thing is that after creating the index, the Index Advisor will check how well this one fits into your workload and, if the result is negative (the performance has fallen), the Index Advisor will automatically delete the index created by it.
48 hours, this is the maximum time, usually the operation ends faster. All this without any outside intervention, and without losing the connection (and of course the data).

What does this mean for me?

Your database will start to work faster, for the same money, but at worst, nothing will change. Shouldn't you try it?

How can I try?

In order to try Index Advisor you need:

• Azure SQL Database on the V12 server
• A database that has been used for some time.

It is important to note that while Index Advisor makes recommendations and allows you to create only non-clustered indexes.

Go:

1. Go to the portal
2. We select the database and in the Operations section we register for the public testing of the Index Advisor.
3. If you already have recommendations, congratulations! You're lucky, immediately start optimizing your database.
4. Otherwise, you will have to wait a couple of days for Index Advisor to accumulate enough information.
5. Select the index from the first table and click “Create Index” in the panel on the right.
   (For the curious: If you click “View Script”, you can see the matrix code to see which SQL query will be executed to create the index)

When the creation and testing is complete, the index will appear in the table of completed operations.



On the right, index performance: 2 queries run 42% faster . The index is 22MB

That's all!

Hey, what is this ...? Or oh, how cool!

Increasingly, Microsoft is trying to listen to its users by providing different feedback mechanisms.
If you liked Index Advisor and its recommendations, feel free to say so. Well, if he messed up, or suggested creating a stupid index, do not hesitate to doubly report it by clicking the “Feedback” button.



MSDN Documentation
Disclaimer: I participate in the work of the Index Advisor team and will try to answer your questions if they appear.