Starting from SQL Server 2017, a great feature was introduced, called Resumable Online Index Operation, which allows online index rebuild can be paused and resumed. And SQL Server 2019 enhanced this by allowing online index creation resumable. In this blogpost, let's have a deep dive into how to use this new indexing options, and attentions you need to take when using it. Here I use the SQL Server 2019 Developer edition and StackOverflow2010 database to do the demo.
Create an new index with RESUMABLE = ON
CREATE INDEX IX_Votes_UserId ON Votes (UserId) WITH (ONLINE = ON, RESUMABLE = ON);
Check the current execution status for resumable index operation
A new DMV sys.index_resumable_operations let you to check the status and progress for resumable online operations in the current database.
Pause the index operation
ALTER INDEX IX_Votes_UserId ON Votes PAUSE;
Can log space be reclaimed by log backup when resumable index operation paused?
YES!
Resume the paused index operation
ALTER INDEX IX_Votes_UserId ON Votes RESUME;
What happened if the index operation being KILL?
It will become PAUSED if the index operation being killed.
How to cancel the index operation
ALTER INDEX IX_Posts_ParentId ON Posts ABORT;
From the above experiment, we can see that being able to pause and resume your online index operations allows you a way to do these operations in a piecemeal
approach, and more importantly allow you to free up transaction log space during the index operation still undergoing.
