Resumable Index Rebuilding in SQL Server 2017

Index rebuild is one of the important tasks in Index Maintenance. As you know Index Rebuild run in a Transaction which means if you abort the Index Rebuild, you have to start all over again. Index Reorganizing is not under Transaction which means you can start from where you left. However, Index Rebuild will solve both Internal and External Index Fragmentation, Index Rebuild is the better option that you would like to go. 

As Index Rebuild will consume a lot of resources from the system, many users would like to perform Index Rebuild in a scheduled manner. This is now possible with SQL Server 2017 and SQL Server 2019. This feature is called the Resumable index. 

Let us see how we can demonstrate this feature. Let us create a table and populate it with the following code.

CREATE TABLE SampleData (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name CHAR(1000),
AddressI CHAR(100)
)


INSERT INTO SampleData
(Name, AddressI)
VALUES
('DINESH','AddressI')
GO 2000000

Now we can rebuild the index with the RESUMABLE option is set to ON.

ALTER INDEX [PK__SampleD] ON [dbo].[SampleData]

REBUILD WITH (ONLINE = ON, RESUMABLE = ON);

Now you can PAUSE and RESUME the index. 

ALTER INDEX [PK__SampleD] ON [SampleData]  PAUSE

ALTER INDEX [PK__SampleD] ON [SampleData]  RESUME

After pausing the index, you can see what percentage of index rebuilding is done with a few other details.

SELECT total_execution_time, percent_complete, 

   name,

   state_desc,last_pause_time,

   page_count

FROM sys.index_resumable_operations;

However, until you complete the index rebuild, the transaction log is utilised which is an important fact to remember.