Resumable index operations allow you to fine-tune your index maintenance schedules by giving you the ability to pause index operations while saving the current state and resume them at a later date while preserving the work already completed. This also allows you to recover if an index operation fails or the server is restarted or fails over in the middle of an index operation. In addition to recoverability, resumable index operations use smaller transactions that are committed as the operations progresses which minimizes transaction log growth during the operation. SQL Server 2017 introduced resumable index rebuilds, and SQL Server 2019 introduces resumable index create.
The following example uses the AdventureWorksDW2016_EXT sample database to demonstrate how resumable index rebuilds work.
Open a new query window and execute the following script to start an index rebuild. Once the query begins executing, return to this window before the query completes to continue the exercise.
USE [AdventureWorksDW2016_EXT]
GO
ALTER INDEX [PK_FactResellerSalesXL_PageCompressed_SalesOrderNumber_SalesOrderLineNumber] ON [dbo].[FactResellerSalesXL_PageCompressed]
REBUILD PARTITION = ALL WITH (ONLINE = ON, RESUMABLE = ON, DATA_COMPRESSION = PAGE)
GO
While the index rebuild is running, the following query will return status information about the operation:
USE [AdventureWorksDW2016_EXT]
GO
SELECT name, state_desc, percent_complete, start_time, last_pause_time, total_execution_time AS total_execution_time_minutes
FROM sys.index_resumable_operations;
GO
| name | state_desc | percent_complete | start_time | last_pause_time | total_execution_time_minutes |
|---|---|---|---|---|---|
| PK_FactResellerSalesXL_PageCompressed_SalesOrderNumber_SalesOrderLineNumber | RUNNING | 4.59237038886725 | 2019-11-04 20:36:21.623 | NULL | 0 |
You can pause the index operation with the following command:
USE [AdventureWorksDW2016_EXT]
GO
ALTER INDEX [PK_FactResellerSalesXL_PageCompressed_SalesOrderNumber_SalesOrderLineNumber] ON [dbo].[FactResellerSalesXL_PageCompressed] PAUSE;
GO
Running the status query again will now show that the index operation has been paused.
USE [AdventureWorksDW2016_EXT]
GO
SELECT name, state_desc, percent_complete, start_time, last_pause_time, total_execution_time AS total_execution_time_minutes
FROM sys.index_resumable_operations;
GO
| name | state_desc | percent_complete | start_time | last_pause_time | total_execution_time_minutes |
|---|---|---|---|---|---|
| PK_FactResellerSalesXL_PageCompressed_SalesOrderNumber_SalesOrderLineNumber | PAUSED | 15.6121895126481 | 2019-11-04 20:36:21.623 | 2019-11-04 20:36:35.290 | 0 |
Go back to the same window where you ran the original command. You can either run the same command again, or you can run the following query to resume the index rebuild:
ALTER INDEX [PK_FactResellerSalesXL_PageCompressed_SalesOrderNumber_SalesOrderLineNumber] ON [dbo].[FactResellerSalesXL_PageCompressed] RESUME;
GO
Running the status command again should now show that the index operation is running again.
USE [AdventureWorksDW2016_EXT]
GO
SELECT name, state_desc, percent_complete, start_time, last_pause_time, total_execution_time AS total_execution_time_minutes
FROM sys.index_resumable_operations;
GO
| name | state_desc | percent_complete | start_time | last_pause_time | total_execution_time_minutes |
|---|---|---|---|---|---|
| PK_FactResellerSalesXL_PageCompressed_SalesOrderNumber_SalesOrderLineNumber | RUNNING | 19.8231684650372 | 2019-11-04 20:36:21.623 | 2019-11-04 20:36:35.290 | 0 |
To simulate a failure, start the index rebuild operation again, then run the following command in another window to shut down the SQL Server service:
SHUTDOWN WITH NOWAIT
GO
After restarting the server, the status query will show that the index operation is paused. Even though the server was restarted, the index operation progress is maintained in the database and the index rebuild can now be resumed rather than being rolled back upon startup.
USE [AdventureWorksDW2016_EXT]
GO
SELECT name, state_desc, percent_complete, start_time, last_pause_time, total_execution_time AS total_execution_time_minutes
FROM sys.index_resumable_operations;
GO
| name | state_desc | percent_complete | start_time | last_pause_time | total_execution_time_minutes |
|---|---|---|---|---|---|
| PK_FactResellerSalesXL_PageCompressed_SalesOrderNumber_SalesOrderLineNumber | PAUSED | 74.5052160144128 | 2019-11-04 20:36:21.623 | 2019-11-04 20:38:39.613 | 1 |