As you might have guessed, this feature allows you to pause and resume an index rebuild operation. Rebuilding an index on very large tables requires a lot of system resources and log space. Sometimes, a rebuild operation is still running when you are at the end of the maintenance window. That usually means, you have to cancel the operation and restart it later from the beginning. The same happens in case of a database failover or when you are running out of disk space.

In SQL Server 2017, you can solve these problems so that you can arbitrarily pause and resume index rebuild operations. To see how this feature works, open a connection to an SQL Server 2017 instance in SSMS and create an index in the WideWorldImporters sample database:

USE WideWorldImporters;
CREATE INDEX IX1 ON Sales.OrderLines (OrderId, StockItemId, UnitPrice);
GO

Now, put the following code in the same SSMS session, but do not execute it yet:

--Connection 1
ALTER INDEX IX ON Sales.OrderLines
REBUILD WITH (RESUMABLE = ON, ONLINE = ON)
GO

The RESUMABLE = ON option instructs SQL Server that the operation is intended to be resumable. Now, it is time to open another connection where, as you might have guessed, you will pause the rebuild operation. So, open another connection and put in the following code:

--Connection 2
USE WideWorldImporters;
ALTER INDEX IX1 ON Sales.OrderLines PAUSE;
GO

Now it is time to execute the commands. First, you need to execute the index rebuild command from the first window and a few seconds later execute from the second connection. Note that the index is not so large, therefore you have to execute the command from the second connection fast, before the rebuild is done. After you have executed the commands from both windows as described, in the first window, you will see the following message:

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

Because the rebuilding index operation is paused with a command from the second window, the session in the first window is disconnected. Although the error message indicates that something failed, in this case, everything is saved to the point of pause. You can check this by querying the sys.index_resumable_operations view:

SELECT name, sql_text, state_desc, percent_complete, start_time, last_pause_time
FROM sys.index_resumable_operations;

This query returns the following output:

name  state_desc   percent_complete   start_time      last_pause_time
----  ----------  ----------------  ---------------  -----------------
IX1   PAUSED      23,4927315783105   2017-12-11       2017-12-11                              
                                     13:08:14.223     13:08:16.203

To continue with the rebuild operation, you should execute the following command in the second connection:

USE WideWorldImporters;
ALTER INDEX IX1 ON Sales.OrderLines RESUME;
GO

After the rebuild operation is done, when you query the sys.index_resumable_operations view, there will be no entry for the index that you rebuilt.

Note than when you specify the RESUMABLE = ON option, you need to specify the ONLINE = ON option too, otherwise the command will fail:

ALTER INDEX IX1 ON Sales.Orderlines
REBUILD WITH (RESUMABLE = ON);
GO

Instead of starting to rebuild the index, you'll get this error message:

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

Resumable online index rebuild is a very useful feature; you can pause the index rebuild when you need to free up resources for something else and resume the rebuild process later from the saved point. You might also want to perform a rebuild of a very large index in several smaller transactions, instead of in long-running ones that could block other processes.