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.

Resumable index - 1

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.

Resumable index - 2

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

Resumable index - 3

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.

Resumable index - 4

Resumable index - 5

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.

Resumable index - 6

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

2 Comments on “Resumable Online Index Rebuild- SQL Server 2017”

  1. Is there a maximum limit of delay between PAUSE and RESUME? In other words: If I PAUSE a REBUILD at 7:30 am, Can I RESUME this rebuild at 5:00 pm ?

    1. There is no limit of delay between PAUSE and RESUME. but Having a long pause for an index operation may impact the DML performance on a specific table as well as the database disk capacity since both indexes the original one and the newly created one require disk space and need to be updated during DML operations

Leave a Reply

Your email address will not be published. Required fields are marked *