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.