Resumable Online Index Rebuild- SQL Server 2017

Resumable online index rebuild is one of the very useful feature of SQL Server 2017 for DBA’s. Now it is possible to pause an Index Rebuild. It allows to resume an online index rebuild operation from where it is stopped or paused. Below are some scenarios where you may need to pause your index rebuild operation.

• If your index rebuild fails due database failure or after running out of disk space. Now you need not required to start the index rebuild from the beginning.
• You wish to pause an index rebuild due to some other important task where you need more system resources.

Resumable online index rebuild does not require significant log space, which allows you to perform log truncation while the resumable rebuild operation is running. As far as performance is concerned there is not much difference between resumable and non-resumable online index rebuild. There is no difference in defragmentation quality as well.

Now let us see how it works.

--Create a table and an index 
DROP TABLE IF EXISTS [dbo].IndexDemo
CREATE TABLE IndexDemo( id int, col2 char(600))
GO
CREATE INDEX IX_IND1 ON IndexDemo(ID);

--Populate some rows 

Declare @count int = 0

While @count <100000
BEGIN
INSERT into IndexDemo(id,col2) values (@count, 'A'+ cast(@count as char))
SET @count = @count+1
END

As the name suggest you can resume an index if you rebuild using online option only. You cannot do it offline.

Now let’s rebuild the index with resumable option ON.

ALTER INDEX IX_IND1 ON IndexDemo REBUILD WITH (RESUMABLE = ON, ONLINE = ON, MAX_DURATION = 1)

While index rebuild operation is running, open a new query window and pause the index operation using below command.

ALTER INDEX IX_IND1 ON IndexDemo PAUSE

When you pause an index rebuild operation SQL Server will throw below error.

It means it kills the session under which the index rebuild was running.  So in your maintenance plan if you wanted to use resumable online index rebuild and if you pause an index rebuild then it will kill the maintenance job. This is something I was expecting that when you pause it should pause only that index rebuild for that specific index and will move to the next step of maintenance job but it doesn’t so Make sure to run your maintenance plan again.

sys.index_resumable_operations is a system view that monitors and checks the current execution status for resumable Index rebuild. Let see what is status of our index rebuild operation. As I have only one table in my database I am not using any filter in my query.

select name,total_execution_time, 
percent_complete, 
page_count,state_desc, 
start_time,last_pause_time
from sys.index_resumable_operations

You can see 56.42 percent of index rebuild is complete and it is in pause state.

Suppose after pausing the index you decided that we no longer required that index or table. When your index is at paused state and you wanted to drop the index or  table. SQL Server will not allow you to drop the object.

So when you need not required the index, it is not advisable to resume the index, rather SQL server provides you an option to abort the paused index rebuild operation. Once you abort you can able to drop the index.

Now let’s resume the index rebuild.

ALTER INDEX IX_IND1 ON IndexDemo RESUME

If you try to run a fresh rebuild, SQL Server will throw a warning message.

Warning: An existing resumable operation with the same options was identified for the same index on ‘IndexDemo’. The existing operation will be resumed instead.

Once the rebuild is over you will not find any entry in the sys.index_resumable_operations for this index.

This is very useful for indexes on big tables which take more time to rebuild.