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

[codesyntax lang=”tsql”]

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

[/codesyntax]

Leave a Reply