SQL Server 2017 - How to Pause Rebuild an Index Using Resumable Online Index Rebuilds

Views: 636
Reading Time: 6 minutes

Hey guys!
All quiet with you?

In this post, I would like to demonstrate to you an innovative feature of SQL Server 2017 (Enterprise, Trial and Developer editions only), which is Resumable Online Index Rebuilds, which allows you to start an index rebuild process and be able to pause this operation in through processing and then continue where you left off, when you wish.

This new feature is very useful, especially when working in critical environments that have a short maintenance window. In many cases, it is not possible to rebuild certain indexes in this window and the maintenance process becomes too complex to reconcile pending activities and available windows.

Another very common day-to-day DBA situation is to start rebuilding an index, start having disk and / or CPU contention and being forced to stop rebuild, losing all the work you've done so far (and this is very frustrating).

Faced with these situations, Resumable Online Index Rebuilds turns out to be a very important solution in the life of DBA SQL Server, as this can dramatically improve index rebuild routines, which are of great importance for good database maintenance.

Note that due to this new feature of SQL Server 2017, DMV sys.dm_exec_requests has changed to include the is_resumable column, indicating whether the request in question can be summarized or not using the Resumable Online Index Rebuilds feature.

Result:

Creating the data mass

To start our tests, let's create a small mass of data.

Rebuilding Indexes

Now that we have created our test table, let's rebuild the index to allow the pause command, which is by adding the parameters ONLINE = ON, RESUMABLE = ON:

Where the parameters of the ALTER INDEX command are:

  • ONLINE: Defines whether rebuild will be done online (per page) or not. Note that Resumable index rebuild only supports rebuild online, so we should always use the ONLINE = ON parameter to use this feature.
  • RESUMABLE: Allows you to define whether rebuild will be done by supporting the Pause / Resume option or not.
  • MAX_DURATION: Very interesting parameter, which allows you to set in minutes the amount of time rebuild will take before it is automatically suspended. This value must be greater than 0 and less than or equal to 10080 (1 week).
  • PAUSE: Using this parameter, the rebuild operation will pause and wait for a new ALTER INDEX to continue the process or the ABORT command to stop rebuild.
  • ABORT: Parameter used to stop index rebuild.

If you try to rebuild the index with the parameter RESUMABLE = ON and ONLINE = OFF, you will encounter this error message:

Msg 11438, Level 15, State 1, Line 2
The RESUMABLE option cannot be set to 'ON' when the ONLINE option is set to 'OFF'

If the timeout set in the rebuild command is reached (in the example, I specified 1 min), rebuild will be immediately stopped, returning the error message below:

Msg 3643, Level 16, State 1, Line 20
The operation elapsed time exceeded the maximum time specified for this operation. The execution has been stopped.
Msg 596, Level 21, State 1, Line 19
Cannot continue execution because session is in kill state.
Msg 0, Level 20, State 0, Line 19
A severe error occurred on the current command. The results, if any, should be discarded.

Now, I am going to start rebuild the index and stop quickly in the middle of the process:

And using the view sys.index_resumable_operations, we can identify how is the progress of this operation, which was not completed and was pending:

Result:

How to summarize RESUMABLE index rebuild

To continue rebuilding the index, simply use the command:

When using the RESUME parameter, the session rebuilding will receive this warning message:
“Warning: An existing resumable operation with the same options was identified for the same index on 'Test'. The existing operation will be summarized instead. ”

The RESUME command can be used after a previous rebuild failure, such as lack of space or if the instance restarts. It is also especially useful for rebuild routines that take hours to complete and end up consuming a lot of log space because the rebuild routine can be paused, allowing log backup to decrease log file utilization.

You can also use the RESUME command to summarize the index rebuild and at the same time change the parameters initially used, such as MAXDOP:

You can still use the WAIT_AT_LOW_PRIORITY parameter to handle locks and blocks that may occur during the rebuild process, as shown below:

Where, in the example above, the WAIT_AT_LOW_PRIORITY parameter will cause rebuild to wait for a maximum of 10 minutes, until the block / lock issue is resolved. After this time, sessions that are blocking rebuild will be dropped thanks to the ABORT_AFTER_WAIT parameter.

The options available for the ABORT_AFTER_WAIT parameter are:
NONE: Still waiting for normal priority lock
SELF: Stops the current execution of index rebuild
BLOCKERS: Stops sessions that are preventing index rebuild from continuing. This option requires the running user to have the ALTER ANY CONNECTION permission.

How to pause RESUMABLE index rebuild

To pause a rebuild in progress, use the command below:

Once you pause rebuild an index, the session that was performing the rebuild operation will receive this error message:

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

Keep in mind that pausing a rebuild will give it a status of PAUSED:

At this stage, you cannot delete the index that was being updated. It should be summarized or aborted to allow it to be deleted. Otherwise, you will come across this error message:

Msg 10637, Level 16, State 1, Line 14
Cannot perform this operation on 'object' with 703341570 ID as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

How to stop the rebuild of RESUMABLE indexes

To stop the rebuild that is in PAUSE state, losing all progress already made, you must use the command:

Once you stop rebuilding an index, the session that was performing the rebuild operation will receive the same error message as when rebuild is paused:

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

Limitations of RESUMABLE Index Rebuild

Below, I will list the limitations of RESUMABLE index rebuild:

  • This feature is only supported for indexes in rowstore format.
  • Does not work with ALTER INDEX parameter SORT_IN_TEMPDB
  • Does not work with TIMESTAMP columns
  • Does not work with calculated (computed) columns
  • Cannot Use This Feature on Disabled Indexes
  • This feature cannot be used within a user transaction.

That's it, guys!
Regards and see you next post.