Azure SQL DB Index Advisor

Play Azure SQL DB Index Advisor

Description

In this episode of Data Exposed, Scott welcomes Vladimir Ivanovic, Senior Program Manager on the SQL Server team. Today, Vladimir introduces us to an exciting preview feature in Azure SQL DB called the Index Advisor which provides insight to developers and DBAs into how best to apply indexes and improve the overall performance of the Azure SQL database.

Vladimir begins by explaining how the Index Advisor works by gathering OLTP telemetry data from your database over a period of time and provides recommendations, if any, for indexes. Vladimir shows us how easy it is to sign up for the Index Advisor, then jumps right in to show how the Index Advisor works. He explains how index recommendations are produced and how easy it is to create recommended indexes with Index Advisor.

There are  powerful features in the Index Advisor including showing the estimated impact to workload performance, space usage for the index, how long the index will take to create, and more importantly, the actual impact the index has had on DB performance (via a report if you do decide to create the index via the Index Advisor). This is powerful because you can now make an educated decision on whether or not to keep the index, or have Azure automatically roll any indexes back that have a negative performance impact. You can rest assured that your performance will only get better with Index Advisor.

Great demos by Vladimir in this episode and a great watch.

Embed

Download

Right click to download this episode

The Discussion

  • User profile image
    Niko Neugebauer

    Automatically revery a "bad" index?
    Well, this might lead to some serious problems, if the definition of what is "bad" is being incorrect - which happens a lot of time.
    For example, I want to improve some certain query from reporting apps that belong to management, while slowing down some other less critical operations.
    Please consider at least including an option of accepting this "automatic reversal".

  • User profile image
    ZippyV

    Why do we need to sign up for this feature if information is already collected in the background? Just show the results for everyone.

    Will we need to pay for this feature in the future?

  • User profile image
    vladiv

    @Niko - thanks for the question!

    We automatically rollback only the indexes created explicitly via the Index Advisor's "Create Index" functionality, and only in cases when our validation logic detects that the newly created index has negatively impacted the performance of the workload. This logic is there to protect the users from situations where a sudden change in the workload causes one of the recommended indexes to stop being a good choice (which should happen rarely, but the protection is still there).

    When users create the indexes outside of Index Advisor, the automated logic index validation logic does not apply -> users have full control, but also the responsibility to monitor the index creation process, measure the impact and rollback manually if needed.

    I'm happy to follow up in more details - feel free to reach out to me directly:  vladiv AT microsoft.com

    Thanks,
    Vladimir

  • User profile image
    vladiv

    @ZippyV - Index Advisor is in preview stage at this point, so sign up is necessary (but it's super simple and fast, and it's once per subscription). We are working to get Index Advisor into GA later this calendar year. 
     
    No additional charges for Index Advisor are planned at this time
     
    Thanks,
    Vladimir

  • User profile image
    Davele

    Hi Vladimir, 
      Nice feature. 

    I'm concerned about the inability to control the names of the indexes you create. If I script it to control the index name I lose the benefit of the Index Wizard. 
    Please consider providing some UI where the DBA has some control over the index that you create. At minimum an "advanced" option where we can a) change the name, b) turn on a "Unique" property. Clearly clearly a wizard can't recommend a unique index, but a DBA will have that business insight. Significantly improving the benefit of the index to the optimiser. 

    Why does the name matter? 
    Many companies have naming standards &/or run automated metadata/schema documentation tools. These expose the names of indexes & constraints. They also make it easier to create automated scripts that anticipate the correct index name. And prevent the creation of multiple identical indexes. 
    Those whose database schema is in source code &/or a DB project, will need to import these new indexes into their project. 

  • User profile image
    vladiv

    Hi Davele, thanks for the suggestions!

    Providing the capability to name the indexes is something we're considering for future improvements to Index Advisor, exactly for the reasons you've stated above :) I don't have an ETA to offer at the moment, but we do have this on our backlog.

    The capability to add the UNIQUE constraint to recommended indexes (or, in general, "customize" the definition/options for the recommended indexes) is also an interesting idea - we may be able to provide some of these capabilities in the future.

    Great suggestions - keep them coming!

    Thanks,
    Vladimir

  • User profile image
    Stagename22

    Is there a way to de-activate the Index Advisor? We are unable to import the bacpac file into our local test db's now that it has been activated.

  • User profile image
    vladiv

    Stagename22

    Turning Index Advisor on doesn't make any changes to your database, so the issue you are experiencing is very likely not related to Index Advisor.

    Feel free to reach out to Azure Support on the bacpac issue, or follow up with me directly at vladiv AT microsoft.com if the issues persist.

    Vladimir

  • User profile image
    srinath Patil

    Who would receive the notification for indexing? Would it be owner, SQL contributor or both or any other role?

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.