SQL 2019- Script for working with Resumable online indexes

Below is the script used in the video demonstrating Resumable online indexes feature in SQL 2019.

USE master

IF EXISTS (
        SELECT 1
        FROM sys.databases
        WHERE name = 'ResumableIX'
        )
BEGIN
    DROP DATABASE ResumableIX
END

CREATE DATABASE resumableix GO

USE ResumableIX GO

CREATE TABLE resumableIxtbl (
    Id INT identity(1, 1)
    ,customername VARCHAR(100)
    ,somerandomtext VARCHAR(1000)
    ) GO

SET NOCOUNT ON

INSERT INTO resumableIxtbl (
    customername
    ,somerandomtext
    )
SELECT c1.name
    ,c2.name
FROM sys.syscolumns c1
CROSS JOIN sys.syscolumns c2 GO 3

SELECT count(*)
FROM resumableIxtbl GO

-- create a clustered index without any resumable functionality
CREATE CLUSTERED INDEX ix_clustered_resumable ON resumableIxtbl (customername)
    WITH (
            SORT_IN_TEMPDB = OFF
            ,ONLINE = ON
            ) -- Rebuild previously created clustered index with resumable functionality --Rebuild: 

ALTER INDEX ix_clustered_resumable ON [dbo].[resumableIxtbl] REBUILD PARTITION = ALL
    WITH (
            SORT_IN_TEMPDB = OFF
            ,ONLINE = ON
            ,RESUMABLE = ON
            ,MAX_DURATION = 1
            ) GO -- Manually pausing the rebuild 

ALTER INDEX ix_clustered_resumable ON [dbo].[resumableIxtbl] RESUME -- Checking the state of the index while it is paused 

SELECT *
FROM sys.index_resumable_operations

-- Aborting the rebuild operation since we need to free up resources. 
ALTER INDEX ix_clustered_resumable ON [dbo].[resumableIxtbl] ABORT

-- Checking status after abort 
SELECT *
FROM sys.index_resumable_operations

-- Restarting the rebuild 
ALTER INDEX ix_clustered_resumable ON [dbo].[resumableIxtbl] REBUILD PARTITION = ALL
    WITH (
            SORT_IN_TEMPDB = OFF
            ,ONLINE = ON
            ,RESUMABLE = ON
            ,MAX_DURATION = 1
            )

-- Checking status after pause again 
SELECT *
FROM sys.index_resumable_operations

-- resuming the rebuild 
ALTER INDEX ix_clustered_resumable ON [dbo].[resumableIxtbl] RESUME -- Checking the status after resume 

SELECT *
FROM sys.index_resumable_operations

/********* WHATS NEWS IN SQL 2019 ********/
-- create a clustered index with resumable functionality 
DROP INDEX ix_clustered_resumable ON resumableIxtbl

--DROP STATISTICS resumableIxtbl.[ix_clustered_resumable] 
CREATE CLUSTERED INDEX ix_clustered_resumable ON resumableIxtbl (ID)
    WITH (
            SORT_IN_TEMPDB = OFF
            ,ONLINE = ON
            ,resumable = ON
            ,max_dURATION = 1
            )

--- CHECK STATUS AFTER ABORT 
ALTER INDEX ix_clustered_resumable ON resumableIxtbl RESUME

SELECT *
FROM SYS.index_resumable_operations

SELECT *
FROM resumableIxtbl -- RESUMING THE INDEX CREATE STATEMENT 

ALTER INDEX ix_clustered_resumable ON resumableIxtbl RESUME

ALTER DATABASE SCOPED CONFIGURATION

SET ELEVATE_ONLINE = WHEN_SUPPORTED

ALTER DATABASE SCOPED CONFIGURATION

SET ELEVATE_RESUMABLE = WHEN_SUPPORTED

Please Consider Subscribing

Subscribe