Resumable Online Index Rebuild option in SQL Server 2017 / Azure SQL DB

   

     This new functionality is available in SQL Server 2017 and Azure SQL DB, It allows you to resume an online index rebuild operation from where it stopped after some kind of failure. Also you can pause an ongoing index rebuild operation if you need to temporarily free up systems resources in order to execute something high priority, later you can resume the index rebuild where it stopped. Stopping and resuming the index rebuild operation does not require significant log space.  Let's see how it works. First I am going to create an index in one of my tables.

CREATE INDEX IX_ProductName ON Products(ProductName)
SELECT * FROM Products

    Now I am going to make my index resumable, Index must be ONLINE to make it resumable. Otherwise you will get an error.

ALTER INDEX IX_ProductName ON [dbo].[Products] 
 REBUILD WITH ( RESUMABLE = ON, ONLINE = ON );

     My index supports the resumable option now. Let's say we are rebuilding this index right now, and I want to suspend / pause the rebuilding operation.

ALTER INDEX IX_ProductName ON [dbo].[Products] 
 PAUSE;

 
     When you execute this command, you will receive bunch of error messages. No worries, that's what you supposed to see. I think "Okay, I paused it" message might be more user friendly but Microsoft decided to throw errors to tell us operation successfully paused. Here is the error messages you will get when you pause an ongoing rebuild operation

Msg 1219, Level 16, State 1, Line 3
Your session has been disconnected because of a high priority DDL operation.
Msg 1219, Level 16, State 1, Line 3
Your session has been disconnected because of a high priority DDL operation.
Msg 596, Level 21, State 1, Line 2
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 2
A severe error occurred on the current command.  The results, if any, 

should be discarded.

      Also we can check the new sys.index.resumable_operations table to see the status of this operation. This table has all kind of information about all paused resumable indexes. When index rebuild is completed, Index information will not be in this table anymore.

SELECT total_execution_time, percent_complete, page_count
 FROM sys.index_resumable_operations;

     Now to resume the rebuilding this index from where it stopped, we can run the following ALTER command.

ALTER INDEX IX_ProductName ON [dbo].[Products]
       RESUME;

SQL Server will resume rebuilding this index. When it completes it, you will not be able to see information about this index in the sys.index_resumable_operations table. This is a great addition and I am sure all DBAs are going to love this new addition.