Azure SQL DB Index Advisor

Play Azure SQL DB Index Advisor

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.