SQL Server 2019 CTP 2.0 – Resumable Online Index Create

Hello Friends,

Last Month Microsoft announced new version of SQL Server – SQL Server 2019. Currently this is a CTP 2.0 release for preview. There are lots of new features in this new SQL Server 2019. One of the major new feature is about Big Data Clusters. There were lots of new features introduced in SQL Server 2017 and Microsoft added some more members into those features with SQL Server 2019.

Resumable Online Index Rebuild is one of the feature that we already know from SQL Server 2017 where we can pause and resume online index rebuild operation. In SQL Server 2017, this feature was limited with Index rebuild operation. if you don’t know about Resumable online Index Rebuild then click here.  In SQL Server 2019, we can perform pause and resume for online index create operation as well and this feature is known as Resumable Online Index Create. In this blog post, you will see how to use this operation. For demo 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

After the execution of above code, now create a non clustered index as mention below:

  USE [WideWorldImportersDW]
  GO
  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]
  )WITH (RESUMABLE = ON)
  GO

Msg 11438, Level 15, State 1, Line 64

The RESUMABLE option cannot be set to ‘ON’ when the ONLINE option is set to ‘OFF’.

Execution of above TSQL code provide an error. This error clearly explain that resumable is only supported with online operation.

  USE [WideWorldImportersDW]
  GO
  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]
  )WITH (RESUMABLE = ON, ONLINE = ON)
  GO

Now I’ll execute pause operation in another window very quickly.

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

Pause Operation will execute successfully and for the resumable create online index operation in previous window, we will get the failure messages because index rebuild operation went into pause state.

We can check the completion percentage of index create 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 image, you can see that index create was almost 7.5 percent completed and is in pause state. Now, resume the index create operation:

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

Just after execution, This time I am going to cancel the query execution using SSMS query cancel button.

Let’s check the completion percentage of index create operation using dynamic management view sys.index_resumable_operations one more time:

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

Query Cancel also pause the operation. You can resume the Operation if you want to create the index successfully. To cancel the operation permanently, you can ABORT the operation using below T-SQL:

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

Now if you will check using dynamic management view sys.index_resumable_operations then there will not be pause operation record for this index.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook