Hello Friends,

As a Database Administrator for big size databases, we generally face the issue of small maintenance window for performing index maintenance operations. We keep trying to our business management for allowing us more maintenance window time but sometimes Business/management does not allow us for a longer maintenance window. Finally somehow, we do the compromise with performance and prefer reorganize over rebuild index operation. To overcome from such scenario Microsoft provided a new feature in SQL Server 2017 i.e. Resumable Online Index Rebuild.

Resumable Online Index Rebuild allow us to pause our index rebuild operation in between and we can resume that later. Yes, you read it correct. Think about the scenario of performing rebuild operation for a very large size index and we know rebuild can not happen within one maintenance window. In such a scenario we can start the index rebuild and pause the rebuild operation at the end of maintenance window (if rebuild operation is still running). We can resume the index rebuild operation in the next maintenance window. Index rebuild operation will start from the same place where we stop the rebuild operation in previous maintenance window.

Let me show you the same practically. For test purpose I am using WideWorldImportersDW database here. In below code, I am going to create a copy table of Fact.Transaction. I’ll insert 101 times rows as we have in Fact.Transaction. After that, I’ll create a non clustered index.

After the execution of above code, now I am going to perform index rebuild for this newly created index in one SSMS window.

While index rebuild code is executing, I’ll execute pause operation in another window quickly.

Above pause operation for index rebuild will execute successfully and will also give you a message of successful command completion. But if you will go to the window, in which we were executing the index rebuild operation, you will see the failure messages because index rebuild operation went into pause state.

Resumable Online Index Rebuild

We can check the completion percentage of index rebuild operation using dynamic management view sys.index_resumable_operations as mention below:

Resumable Online Index Rebuild

From the above output, you can see that index rebuild was almost 5 percent completed and currently is in pause state. Now, lets try to resume the index rebuild operation:

This will resume the index rebuild operation and will give you a successful command completion message (if rebuild will completed successfully). You can check the result of dynamic management view as well.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook