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.
| 1 2 3 4 5 6 7 8 9 10 11 | SELECT session_id, start_time, [status], wait_type, wait_time, is_resumable FROM sys.dm_exec_requests WHERE session_id > 50 |
Result:
Creating the data mass
To start our tests, let's create a small mass of data.
| 1 2 3 4 5 6 7 8 9 10 11 12 | IF (OBJECT_ID('dbo.Teste') IS NOT NULL) DROP TABLE dbo.Teste CREATE TABLE dbo.Teste ( Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Nome VARCHAR(50) NOT NULL ) WITH(DATA_COMPRESSION=PAGE) GO INSERT INTO dbo.Teste (Nome) SELECT NEWID() GO 50000 CREATE NONCLUSTERED INDEX SK01_Teste ON dbo.Teste(Nome) WITH(DATA_COMPRESSION=PAGE) |
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:
| 1 2 | ALTER INDEX SK01_Teste ON dbo.Teste REBUILD WITH(ONLINE=ON, MAXDOP=4, MAX_DURATION=1, 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:
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:
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:
| 1 2 | SELECT * FROM sys.index_resumable_operations |
Result:
How to summarize RESUMABLE index rebuild
To continue rebuilding the index, simply use the command:
| 1 | ALTER INDEX SK01_Teste ON dbo.Teste RESUME |
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 resumed 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:
| 1 | ALTER INDEX SK01_Teste ON dbo.Teste RESUME WITH(MAXDOP=6, MAX_DURATION=2) |
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:
| 1 | ALTER INDEX SK01_Teste ON dbo.Teste RESUME WITH(MAXDOP=4, MAX_DURATION=1, WAIT_AT_LOW_PRIORITY(MAX_DURATION=10, ABORT_AFTER_WAIT=BLOCKERS)) |
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:
| 1 | ALTER INDEX SK01_Teste ON dbo.Teste PAUSE |
Once you pause rebuild an index, the session that was performing the rebuild operation will receive this error message:
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:
Cannot perform this operation on 'object' with ID 703341570 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:
| 1 | ALTER INDEX SK01_Teste ON dbo.Teste ABORT |
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:
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.
