Resumable Index in SQL Server

INTRODUCTION: Resumable Index is a new feature in SQL Server 2017. A Running index rebuild operation can be paused and resume later.

Restriction:
Only Online Index Rebuild operations can be paused

DEMO:

I will use AdventureWorks2016CTP3 Database for demo. Please download the database backup from below link

https://www.microsoft.com/en-us/download/details.aspx?id=49502

 

Change the compatibility level to 140

USE [master]
GO
ALTER DATABASE [AdventureWorks2016CTP3] SET COMPATIBILITY_LEVEL = 140
GO

Create a new index

USE AdventureWorks2016CTP3
GO
CREATE NONCLUSTERED INDEX [IX_OrderTracking] ON [Sales].[OrderTracking]
([CarrierTrackingNumber] ,
[OrderTrackingID],
[SalesOrderID],
[TrackingEventID]

)

GO

 

Rebuild the Index

 

ALTER INDEX [IX_OrderTracking] ON [Sales].[OrderTracking] REBUILD WITH (ONLINE=ON,RESUMABLE=ON)

Pause the Index Rebuild

ALTER INDEX [IX_OrderTracking] ON [Sales].[OrderTracking] PAUSE

Check the status of Index Rebuild operation

SELECT name,total_execution_time, percent_complete, name,state_desc,last_pause_time,page_count
FROM sys.index_resumable_operations

Index Rebuild operation will throw the below error

 

Resume the paused index rebuild operation

ALTER INDEX [IX_OrderTracking] ON [Sales].[OrderTracking] RESUME

Check the status of Index Rebuild

The query did not return any result as index rebuild operation was completed after resume

You may also include MAX_DURATION= [ TIME IN MINUTES] With resumable=ON option. Minimum value is 1 min and maximum value is 1 week

MAX_DURATION is the number of minutes , the resumable index rebuild operation will execute before suspending.

 

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 7+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.