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.

Resumable Online Index Create

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

Resumable Online Index Create 1

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.

Resumable Online Index Create 2

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

Resumable Online Index Create 3

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

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 Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

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