SQL Sever 2017 – Resumable Online Index Rebuild Parameters

Hello Friends,

In my previous blog post, we have learned about the importance and use of Resumable Online Index Rebuild Operation. In today post we will learn about the various parameters those we can use or we should consider while using this feature. First let me show you the syntax for Alter Index Rebuild operation in SQL Server 2017.

ALTER INDEX { index_name | ALL } ON <object>  
{     REBUILD WITH ( <rebuild_index_option> [ ,...n ] )  
    | RESUME [WITH (<resumable_index_options>,[…n])]
    | PAUSE
    | ABORT
}  
[ ; ] 

<rebuild_index_option > ::=  
{   ..
    | ONLINE = {   
          ON [ ( <low_priority_lock_wait> ) ]   
        | OFF } 
    | RESUMABLE = { ON | OFF } 
}

<resumable_index_option> ::=
 { 
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION =<time> [MINUTES]
    | <low_priority_lock_wait>  
 }

<low_priority_lock_wait>::=  
{  
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,   
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )  
}

First thing first, If you want to use Resumable index rebuild operation then use RESUMABLE = ON with ONLINE = ON. If you will use RESUMABLE = ON without ONLINE = ON then you will get error as mention below: “The RESUMABLE option cannot be set to ‘ON’ when the ONLINE option is set to ‘OFF’”

Second thing, you can specify the time limit to execute this rebuild operation and after that time this operation will be paused automatically. This is a great feature that will surely help DBA people. Let say, we have a maintenance window for doing this alter index rebuild operation for 2 hours start at 10 PM. I can schedule a job/execute manually the rebuild command and if command completed within 2 hours window time and all good else this command will be paused and we we continue the same operation in next (day) maintenance window.

Third, You can resume the same operation later as mention below RESUME option:

Fourth point, If you want to discard any paused operation then you can do that by using ABORT option:

Fifth point, if you want to change MAXDOP value while resuming any paused index rebuild operation then you can do that by using MAXDOP option.

Sixth point is about handling blocking operation. Using WAIT_AT_LOW_PRIORITY, we can handle the blocking situation. Lets think about a situation, There is already one Index rebuild operation in paused state. I want to resume that now but some other operation is currently running over that object. In this situation, I can resume the index rebuild operation with WAIT_AT_LOW_PRIORITY option. With this option I have to specify, MAX_DURATION in minutes and ABORT_AFTER_WAIT option. Where MAX_DURATION is the duration, till that time resume operation will wait for other transaction to complete. If other transaction will not be completed till the specified MAX_DURATION then action specified under ABORT_AFTER_WAIT will be executed. ABORT_AFTER_WAIT = SELF means transaction will kill itself while ABORT_AFTER_WAIT = BLOCKERS means all the blocker will be killed.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook