Quick look at resumable indexing in SQL Server 2017 CTP2.0

Allow for index to be paused:
ALTER INDEX NCI_c1 on t1 REBUILD WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON);
 
View current state using:
SELECT * FROM sys.index_resumable_operations;
image1
Pause the index operation:
ALTER INDEX NCI_c1 on t1 PAUSE;
 
The state_desc now shows PAUSED:
SELECT * FROM sys.index_resumable_operations;
image2
To resume the index operation:
ALTER INDEX NCI_c1 on t1 RESUME;
 
To abort the index operation:
ALTER INDEX NCI_c1 on t1 ABORT;
 
Full command syntax and restrictions can now be found here

Leave a Reply