When Will My SQL Server Online Resumable Index Rebuild or Create Operation Complete?

Have you ever wondered how much progress SQL Server has completed while rebuilding or creating an online resumable index?  If so, then there is SQL Server system view that will show you how much progress has been completed on your resumable online index rebuild or creation operation. The system view is named sys.index_resumable_operations.

This view will show the progress completed on your online resumable index rebuild operation, if you are running Azure SQL Database, or SQL Server 2017. If you are running the Community Technical Preview (CTP) of Azure SQL Database or SQL Server 2019, then this system view will also show you the progress of your online resumable index creation processes.

Suppose you have a session that is creating the following online resumable CREATE CLUSTERED INDEX statement:

CREATE CLUSTERED INDEX CI_Visits2_1 ON dbo.Visits2 (ID)
WITH (ONLINE = ON,RESUMABLE = ON);

While this index is being created in one session, you can open another session and run the following TSQL code:

SELECT 
name as index_name,
percent_complete, 
state_desc, 
start_time,
last_pause_time, 
total_execution_time AS ExecutionMin,
(total_execution_time / percent_complete) * (100-percent_complete) AS ApproxMinToComplete
FROM sys.index_resumable_operations;

When you run this code, you will get something that looks like this:

As you can see SQL Server has completed a little over 50% of the work to build this index, by looking at the percent_completed column.  By using the total_execution_time, and the precent_complete columns you can calculate an approximate time in minutes (ApproxMinToComplete)for when the CREATE CLUSTERED INDEX statement will complete.

# # #

See All Articles by Columnist Gregory A. Larsen