Can Microsoft Azure find an index that your DBA could not find?


    A small but interesting announcement for those using Azure SQL DB.
    July 1st went publicly testing Index Advisor for Azure SQL Database.

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



    This is how the main Index Advisor panel looks



    On the Microsoft Azure portal , the assistant shows a table with recommended indexes. Each record characterizes the index by the positive effect on the database, shows the columns and table on which the index will be created, as well as the time the recommendation was created.

    What is the appeal of the Index Advisor?


    Index Advisor is completely transparent to the user. First, he picks you a list of several indexes, the creation of which will maximize the database performance.
    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, Index Advisor will check how well it fits into your workload and, if the result is negative (performance has fallen), Index Advisor will automatically delete the index it created.
    48 hours, this is the maximum time, usually the operation ends faster. All this without any outside interference, and without losing the connection (and of course the data).

    What does this mean for me?


    Your database will work faster, for the same money, but at worst, nothing will change. Isn't it worth a try?

    How do i try?


    In order to try Index Advisor you need:
    • Azure SQL Database on 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. We go to the portal
    2. We select a database and in the Operations section we register for a public testing of Index Advisor.
    3. If you already have recommendations, congratulations! You are lucky, immediately start optimizing your database.
    4. Otherwise, you 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 creation and testing is completed, the index appears in the table of completed operations.



    On the right, index performance: 2 queries are 42% faster . Index occupies 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 screwed up, or suggested creating a stupid index, do not hesitate to double and report by clicking the “Feedback” button. MSDN



    Documentation Disclaimer: I’m participating in the Index Advisor team and will try to answer your questions if they appear.

    Also popular now: