none
When do Azure SQL Index Rebuilds occur on Existing Indexes? RRS feed

  • Question

  • Azure backups occur every few minutes per article sited here. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automated-backups

    When do the index rebuilds occur in Azure SQL, are they automatic or manually selected in a maintenance window?

    Note: I am talking about Indexes I created, when the Database schema was deployed,  I know Azure SQL Finds New Indexes which are missing, but I am talking about Existing indexes, when do they rebuild from fragmentation? Are they automatic, or does user have to select maintenance hours? Rebuilding indexes can cause tables to lock, so its crucial
    Monday, June 17, 2019 11:47 PM

All replies

  • SQL Database continuously monitors the queries that are running and identifies the indexes that could improve performance. After there's enough confidence that a certain index is missing, a new Create indexrecommendation is created.

    Azure SQL Database builds confidence by estimating the performance gain the index would bring through time. Depending on the estimated performance gain, recommendations are categorized as high, medium, or low.

    Indexes that are created by using recommendations are always flagged as auto-created indexes. You can see which indexes are auto-created by looking at the sys.indexes view. Auto-created indexes don’t block ALTER/RENAME commands.

    This will leave you the freedom to select the maintenance time for creating index. 

    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-advisor

    ____________________________________________________________
    Please remember to mark the replies as answers if they help.

    Tuesday, June 18, 2019 1:41 AM
  • This does not answer the question, I am talking about Indexes I created, when the Database schema was deployed,  I know Azure SQL Finds New Indexes which are missing, but I am talking about Existing indexes, when do they rebuild from fragmentation? Are they automatic, or does user have to select maintenance hours? Rebuilding indexes can cause tables to lock, so its crucial
    Tuesday, June 18, 2019 7:42 AM