SQL Server 2019 CTP 2.0 – Elevate Resumable Configuration

Hello Friends,

There are so many SQL Server Instance level settings those we can configure at individual database level by using Database Scoped Configurations like maxdop, Clear Procedure Cache etc. In SQL Server 2019 CTP 2.0, There is one more setting that we can configure at individual database level – Elevate Resumable Operations. By using this database scoped configuration, we can enable or disable resumable supported statement at database level.

In this blog post, we will see the impact of this database scoped configuration practically. In one of my previous blog post, I already explained about Resumable Online Index Create operation. If you want to know more about Resumable Online Index Create operation then click here. I am going to create one table using WideWorldImportersDW database.

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

Now, lets create a non clustered index. If we want to create index with resumable option then we have to specify resumable option ON. in my previous blog post, I have created the same index using same T-SQL code:

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

If you want to rebuild the index then again you have to specify resumable option ON as mention below:

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

In SQL Server 2019, we can configure resumable setting at database level using database scope configuration option. After configuring this database scoped setting, we don’t need to specify resumable at statement level all the time. let’s enable this database scoped configuration using below T-SQL:

USE [WideWorldImportersDW]
GO
ALTER DATABASE SCOPED CONFIGURATION 
SET ELEVATE_RESUMABLE=WHEN_SUPPORTED ;
GO

After this you can run your resumable supported operation without specifying resumable at statement level. You can also do the pause and resume operation as well.

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 (Online = ON)
  GO

After hit the execution for above code, immediately open an another query window and Pause index create operation (Another options: you can also cancel the query execution or kill the session).

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

You can check the Create Index execution percent complete status using below dmv:

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

You can see that our create index operation automatically elevated to resumable because of database level configuration. If we want to rebuild the index then also we don’t need to specify resumable option at statement level.

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

If you want to disable this database scoped configuration then you have to set this configuration OFF (which is the default setting).

USE [WideWorldImportersDW]
GO
ALTER DATABASE SCOPED CONFIGURATION 
SET ELEVATE_RESUMABLE=OFF ;
GO

Database Scoped Configuration setting can be overwrite by specifying resumable option at statement level.

Note: You can also get rid of specifying ONLINE = ON at statement level every time by configuring ELEVATE_ONLINE option at database level using database scoped configuration.

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