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’”

Resumable Online Index Rebuild Parameters

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.

Resumable Online Index Rebuild Parameters

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

Resumable Online Index Rebuild Parameters

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

Resumable Online Index Rebuild Parameters

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.

Resumable Online Index Rebuild Parameters

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.

Resumable Online Index Rebuild Parameters

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *