How to Use Resumable Online Index Rebuild Operation in SQL Server?

Resumable Online Index Rebuild is a new feature introduced in SQL Server 2017. Index Rebuild operation is very expensive task that takes lot of system resources and log spaces during execution. Sometimes, we need to cancel index rebuild operation in middle of its execution to reclaim the space as well as system resources to avoid any performance issue, space issue or blocking issues. In previous versions of SQL Server, we rerun index rebuild operation again if we cancel it during its execution to make sure the database maintenance activity should be done. Re-execution of this job will start rebuilding indexes again from scratch that will take lot of time and resources again. Microsoft has given a solution named Resumable Index Rebuild to overcome of this scenario. Resumable Online Index Rebuild option is an extension of feature Online Index Operation in SQL Server. Read below article if you want to understand the disk and performance impact of using online index rebuild operation.

• What would be Disk and Performance Impact if we rebuild indexes using ONLINE option?

Resumable Online Index Rebuild

This feature allows us to pause index rebuild operation during its execution at any point and resume it later from the point it was paused. You can use this feature in case you get any higher priority tasks or your server is running out of disk space or facing any performance issue. In addition, this feature rebuilds indexes using only a small amount of log space.

This feature allows us to resume an index rebuild operation after an index rebuild failure, such as after a database failover or after running out of disk space. We can also pause an ongoing index rebuild operation and resume it later. Resumable Online Index Rebuild allows us to rebuild large indexes without using a lot of log space.

Read More

• SQL Server Interview Questions & Answers on Indexes
• What should be Best Value for fill factor in SQL Server?
• How to Avoid Page Split in SQL Server?

Points to Consider

Below are the points you must keep in mind if you are choosing resumable online index rebuild operation.

1. When an index operation is paused, both the original index and the the newly created one require disk space and need to be updated during DML operations.
2. Enables truncation of transaction logs during an index rebuild operation (this operation cannot be performed for a regular online index operation).
3. Resumable Online Index Rebuild feature can be used only with Online Index Rebuild operation. You cannot use it for offline index rebuild operation.
4. We cannot rebuild an index that is disabled.
5. We cannot use ALL option to rebuild existing indexes.
6. Resumable Index Rebuild operation cannot be used to rebuild an existing index that has either computed column or timestamp column as part of the index key.
7. We cannot use SORT_IN_TEMPDB=ON option if we choose to use resumable index rebuild option.

How to use Resumable Online Index Rebuild?

I have given multiple examples to demonstrate the different use cases of resumable online index rebuild operation in this section. If we want to use the resumable index rebuild functionality, we need to execute the online index rebuild with RESUMABLE keyword because Resumable index rebuild supports only during online index rebuild, so we must specify ONLINE = ON along with RESUMABLE = ON option. You cannot use this feature until you mention RESUMABLE = ON in your code because RESUMABLE=OFF is the default value. Run below command to rebuild index online as resumable option.

ALTER INDEX INDEX_Name on Table_Name
REBUILD WITH (ONLINE=ON, RESUMABLE=ON) ;

We can also specify MAXDOP settings during online index rebuild as resumable option to use parallelism. Execute an online index rebuild as resumable operation with MAXDOP=1 by running below command.

ALTER INDEX INDEX_Name on Table_Name 
REBUILD WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON) ;

We can also control execution or processing time of resumable online index rebuild by specifying MAX_DURATION option. It specifies the number of minutes that the resumable online index operation will be executed, before being paused. Execute an online index rebuild as resumable operation with MAX_DURATION set to 30 minutes.

ALTER INDEX INDEX_Name on Table_Name 
REBUILD WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=30) ;

Next, we will discuss about pausing these index rebuild operations that are running as resumable option in next section.

Pause Online Index Rebuild running with Resumable Option

If you want to manually pause a running resumable online index rebuild then you just need to use PAUSE clause in above ALTER INDEX statements. Run below command to pause a resumable index rebuild operation.

ALTER INDEX INDEX_Name on Table_Name PAUSE ;

Now we will discuss about how to resume a paused online index rebuild operation in next section.

Resume an Online Index Rebuild running with Resumable Option

As I have mentioned above, you can pause online index rebuild due to any reason. Let’s say suppose you pause it because it is taking lot of time to complete and consuming lot of system resources then you can either resume it later when system has enough resource to run this operation or you can resume it specifying a new value for MAXDOP setting that will allow this operation to use parallelism to finish this task.

Suppose you want to resume an online index rebuild for an index rebuild that was executed as resumable operation specifying a new value for MAXDOP set to 8.

ALTER INDEX INDEX_Name on Table_Name 
RESUME WITH (MAXDOP=8) ;

If you want to resume an online index rebuild for an index rebuild that was executed as resumable operation with the same values then you can remove the MAXDOP options from above command and execute the simple command given below.

ALTER INDEX INDEX_Name on Table_Name RESUME;

You can also resume an online index rebuild operation for an index rebuild that was executed as resumable with different parameters like you can set MAXDOP to 2, set the execution time for the index being running as resumable to 60 minutes and in case of an index being blocked on the lock wait 10 minutes and after that kill all blockers. Run below command to rebuild your indexes with above criteria.

ALTER INDEX INDEX_Name on Table_Name
RESUME WITH (MAXDOP=2, MAX_DURATION= 60 MINUTES,
WAIT_AT_LOW_PRIORITY (MAX_DURATION=10, ABORT_AFTER_WAIT=BLOCKERS)) ;

You can test it in your lower life cycle with multiple options and keywords to get in depth knowledge about this feature.

Abort a Resumable Index Rebuild operation

We can also abort resumable index rebuild operation which is running or paused by executing below T-SQL statement.

ALTER INDEX INDEX_Name on Table_Name ABORT ;

Once you will run above command, your index rebuild operation will be aborted.

Monitor Resumable Index Rebuild Operation

Microsoft has given a system view sys.index_resumable_operations that monitors and checks the current execution status for resumable Index rebuild. You can also get the progress details in percentage of resumable index rebuild operation using this system view.

There is a column named state in this system view that will let us know the state of the resumable index rebuild operation whether it is paused or still running. You can query this system view to see all the resumable index rebuild operations that are currently paused or running.

Run below command to list all resumable index rebuild operations that are in the PAUSE state. State column value zero means online index rebuild is running and value 1 means they are paused. You can also check the percent completion of all resumable index rebuild operations from column PERCENT_COMPLETE from the output received by below command.

SELECT * FROM  sys.index_resumable_operations WHERE STATE = 1;

Related Articles

• Performing Online Index Rebuild Operation
• Understanding SQL Server Indexes and difference between Clustered and Nonclustered Indexes
• Difference between Index Rebuild and Index Reorganize Operation

I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.