SQL Sever 2017 – Resumable Online Index Rebuild

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.

USE [WideWorldImportersDW]
GO
SELECT [Customer Key]
      ,[Bill To Customer Key]
      ,[Supplier Key]
      ,[Transaction Type Key]
      ,[Payment Method Key]
      ,[WWI Customer Transaction ID]
      ,[WWI Supplier Transaction ID]
      ,[WWI Invoice ID]
      ,[WWI Purchase Order ID]
      ,[Supplier Invoice Number]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Total Including Tax]
      ,[Outstanding Balance]
      ,[Is Finalized]
      ,[Lineage Key]
  INTO [WideWorldImportersDW].[dbo].[Transaction]
  FROM [WideWorldImportersDW].[Fact].[Transaction]
GO 
INSERT INTO [WideWorldImportersDW].[dbo].[Transaction]
SELECT [Customer Key]
      ,[Bill To Customer Key]
      ,[Supplier Key]
      ,[Transaction Type Key]
      ,[Payment Method Key]
      ,[WWI Customer Transaction ID]
      ,[WWI Supplier Transaction ID]
      ,[WWI Invoice ID]
      ,[WWI Purchase Order ID]
      ,[Supplier Invoice Number]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Total Including Tax]
      ,[Outstanding Balance]
      ,[Is Finalized]
      ,[Lineage Key]
  FROM [WideWorldImportersDW].[Fact].[Transaction]
  GO 100

  CREATE NonClustered INDEX NCI_dbo_Transaction
  ON [dbo].[Transaction]
  (    [Payment Method Key]
      ,[WWI Customer Transaction ID]
      ,[WWI Supplier Transaction ID]
      ,[WWI Invoice ID]
      ,[WWI Purchase Order ID]
      ,[Supplier Invoice Number]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Total Including Tax]
      ,[Outstanding Balance]
  )
  GO

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

USE [WideWorldImportersDW]
GO
ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction] 
REBUILD WITH (RESUMABLE = ON, ONLINE = ON)
GO

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

USE [WideWorldImportersDW]
GO
ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction] 
PAUSE
GO

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.

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

USE [WideWorldImportersDW]
GO
select 
name,
percent_complete, 
state_desc, 
start_time,last_pause_time
from sys.index_resumable_operations
GO

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:

USE [WideWorldImportersDW]
GO
ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction] 
RESUME
GO

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