Modernize index maintenance with Resumable Online Index Rebuild
As we know, the index is one of the most powerful tools for optimizing database performance. As you insert, update, and delete rows, indexes become more fragmented and less effective. Because of this, DBAs regularly do index rebuild operations.
With the growing sizes of databases, index rebuilds can take a very long time. Combine that with the business needs for your applications to be always available and performant and this can be an issue. Big OLTP environments with busy workloads often have very short maintenance windows with some too short to execute large index rebuild operations.
Fit rebuild operations into limited maintenance windows
With ROIR (Resumable Online Index Rebuild), you can configure a rebuild to execute only during a maintenance window of defined length. Previously, if an index operation ran longer than the maintenance window the DBA would have to abort the index operation and run it again from the start later. If an index operation regularly took longer than the desired maintenance window there was no way to work around this. With ROIR, the user can simply define a max duration for the rebuild according to the maintenance window.
ALTER INDEX [ix_CustomerIDs] ON [ContosoSales].[ConstosoTransactionData] REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 30 MINUTES); GO
Pause an index rebuild to free up resources for another high priority task
Sometimes an unexpected high priority task may come up and require more of your resources. With ROIR you can pause your rebuild operation at any time to allow other higher priority tasks to execute. The pause functionality can also be used to pause the index operation at the end of the maintenance window if you didn’t define a max duration.
ALTER INDEX ix_CustomerIDs ON [ContosoSales].[ConstosoTransactionData] PAUSE
Resume an index rebuild after failure
Index rebuild failures can occur unexpectedly for several reasons including database failover or running out of disk space. Think about how much time can be wasted if an index operation fails after running for several hours. With ROIR, you will be able to resume an index rebuild from where the failure occurred. A failure will put the index into a paused state.
ALTER INDEX ix_CustomerIDs ON [ContosoSales].[ConstosoTransactionData] RESUME
Perform a large index rebuild without using large log space
Long running index operations can block other maintenance operations. With ROIR, you can perform a large index rebuild without using a large amount of log space. ROIR prevents log truncation and out-of-log errors.
Please consider that keeping an index in a paused state will have a performance impact since both the original and new index will require disk space and need to be updated during DML operations. You also cannot make schema changes on a table when one of its indexes is in a paused state.
For more information on ROIR please review the following documents
Monitor the Azure blog to see more great features we have coming in this space!
Source: Azure Blog Feed