SQL 2019- Script for working with Resumable online indexes

By | July 30, 2019

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

https://youtu.be/xJTfyERfTkk

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

Leave a Reply