As the DBA, have you ever had to cancel an online rebuild or create index process on a very large table? Perhaps it didn't finish during your maintenance window or consumed valuable resources, causing other queries to run slowly? It is a headache for all database administrators to manage massive index operations.

The worst part about canceling the rebuild or index creation process was that the entire process was reversed and all previous work was lost. It would be preferable if you could resume at the point where your canceled online rebuild or create index statement left off, rather than starting the index rebuild or creation process from scratch.

 We no longer need to be concerned. Beginning with SQL Server 2017, Microsoft added the ability to pause and then resume index rebuilds. This was a fantastic feature, and with the release of SQL Server 2019, we can now replicate it on the actual create index process. Many of the scheduling issues that DBAs frequently face are addressed by these newer features.

 A new system view, ‘sys.index_resumable_operations’, was also introduced with the release of SQL Server 2017. This system view monitors and verifies the status of a resumable index rebuild or creation. You can use this view to determine which tables have pending resumable operations.

 Resumable Index Operation

 The ALTER or CREATE Index operation in previous versions of SQL Server can be performed with the ONLNE=ON option. However, for a large table, the operation can take many hours and consume a large amount of resources. Failures or interruptions are also possible during such executions. Microsoft added resumable capabilities to ALTER and CREATE Index, allowing users to pause the operation during a maintenance window or restart it from where it was interrupted during an execution failure rather than starting from the beginning.

The ability to pause online index operations allows SQL Server to resume rebuilding or creating index operations where it left off. The ability to retain work done while pausing an online index operation allows you to perform the index operation in stages. Restarting to create or rebuild an index on a very large table by pausing and resuming the indexing process allows you to work around short maintenance windows and other resource-intensive queries.

Let's create the Resumable Index

To use this feature, we must first Select SQL Server 150 (SQL Server 2019) or higher as the database compatibility mode.

I have a 'test' database with a table called 'RandomTestData' that is filled with random data. Let's make a non-clustered index on the ID and Date of Birth columns in this table.

USE [test]
GO
CREATE NONCLUSTERED INDEX [NC] ON [dbo].[RandomTestData]
(
[ID] ASC,
[DateOfBirth] ASC
)WITH (ONLINE = ON,RESUMABLE = ON)
GO

When we start this indexing in another session, we will use the following command to pause it.

USE [test]
GO
ALTER INDEX [NC] ON [dbo].[RandomTestData]
PAUSE;
GO

You will notice that as soon as you run the Pause command, the create index operation will be halted, with the following message displayed.

After pausing this operation, we can check its status with ‘sys.index_resumable_operations’. To check the status, we will execute the following command.

SELECT 
   name, 
   percent_complete,
   state_desc,
   last_pause_time,
   page_count
FROM sys.index_resumable_operations;

We can see the results below:

Here, the index operation state is PAUSED, and the current percent complete is 10.8333. It means that the index operation stopped when the task reached 10.83% completion.

Let us resume this operation with the RESUME command.

USE [test]
GO
ALTER INDEX [NC] ON [dbo].[RandomTestData]
RESUME;
GO

You can see that the RESUME operation is now in progress, and we can check its status again using sys.index_resumable_operations.

SELECT 
   name, 
   percent_complete,
   state_desc,
   last_pause_time,
   page_count
FROM sys.index_resumable_operations;

The value in the 'State_desc' column has been changed from PAUESD to RUNNING, and the 'percent_complete' has been increased to 18.55%, indicating that the operation is in progress.

A few things to consider about this option:

• The RESUMABLE option isn't persisted in the metadata for a given index and applies only to the duration of a current DDL statement. Therefore, the RESUMABLE = ON clause must be specified explicitly to enable resumability.
• The RESUMABLE = ON option and the low_priority_lock_wait option both support MAX_DURATION.
• Re-executing the original ALTER INDEX REBUILD statement with the same parameters resumes a paused index rebuild operation. You can also resume a paused index rebuild operation by using the ALTER INDEX RESUME statement.
• The SORT_IN_TEMPDB = ON option is not supported for resumable indexes.
• The RESUMABLE = ON DDL command cannot be executed within an explicit transaction (it cannot be part of the BEGIN TRAN... COMMIT block).
• Only paused index operations can be resumed.
• When resuming a paused index operation, you can change the MAXDOP value to a new value. If MAXDOP is not specified when resuming a paused index operation, the last MAXDOP value is used. If the MAXDOP option is not specified, the default value is used for index rebuilding.
• To pause the index operation immediately, stop the running command (Ctrl-C), or use the ALTER INDEX PAUSE or KILL session_id> commands. Once the command has been paused, it can be resumed by using the RESUME option.
• The ABORT command terminates the session that hosted the original index rebuild and aborts the index operation.
• No additional resources are required for resumable index rebuilding, with the exception of
• o Additional space required to keep the index being built, including the time when the index is paused
• o A DDL state preventing any DDL modification
• The ghost cleanup will be running during the index pause phase, but it will be paused during index run. The following functionality is disabled for resumable index rebuild operations
• Rebuilding an index that is disabled isn't supported with RESUMABLE = ON
• ALTER INDEX REBUILD ALL command
• ALTER TABLE using index rebuild
• DDL command with RESUMABLE = ON can't be executed inside an explicit transaction (can't be part of BEGIN TRAN ... COMMIT block)
• Rebuild an index that has computed or TIMESTAMP column(s) as key columns.
• In case the base table contains LOB column(s) resumable clustered index rebuild requires a Sch-M lock in the Starting of this operation

Resumable index creation or rebuild does not require you to keep a long-running transaction open, allowing log truncation and better log space management. The ability to manage indexes at a granular level reduces the database administrator's workload for index maintenance. You can pause and restart an index create or rebuild operation multiple times to fit your maintenance windows

When an index operation is paused, both the original and newly created indexes consume disc space and must be updated during DML operations.

Although the online operation is advised, it is critical to analyse the underlying infrastructure and understand the ramifications before proceeding.