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
