dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted March 5, 2019

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

By Greg Larsen

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:

Tip 55

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



MS SQL Archives




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM