SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Index Rebuild progress v2

I have actually written about this subject earlier on my Danish blog (http://www.performanceduo.com/post/Index-Rebuild-progress.aspx), but last week when working on a big SAP installation i discovered that my old script was not working. It did not support partitioned tables, and if you know just a little bit about SAP, you will know that it is an heavy used feature. I have made a few modifications to the script – and now I’m blogging about it again.

We have all been in a situation where an index rebuild is taking very long, how nice would it be to be able to see the progress of the operation somewhere – that is not possible I’m afraid, unless you use a little magic :) . The magic only works on ONLINE operations, so no support for standard edition here, sorry.

Let’s have a look at a little example, I have a table in my database called ‘PefCounterValues’ it contains approximately 4 million records. The following indexes are created on the table:


Now I want to rebuild one of the indexes, I achieve that with the following command:

ALTER INDEX [IDX_PerfCounterValues_PerfCounterId_SnapShotTime_INC_PerfCounterValue] ON [PerfCounterValues REBUILD WITH (ONLINE = ON);

When this is running I have no chance to follow the progress of the operation – unless we use a little magic. Because the index operation is done ONLINE, there is created a shadow version of the index, the progress of building this shadow version of the indexes can be monitored in sys.partitions – and as you can see in the script below that is what I do.

;WITH cte AS
ROW_NUMBER() OVER(PARTITION BY object_id, index_id, partition_number ORDER BY partition_id) as rn
FROM sys.partitions
   object_name(cur.object_id) as TableName,
   PrecentDone =
         WHEN pre.rows = 0 THEN 0
         ((cur.rows * 100.0) / pre.rows)
   pre.rows - cur.rows as MissingRows
FROM cte as cur
INNER JOIN cte as pre on (cur.object_id = pre.object_id) AND (cur.index_id = pre.index_id) AND (cur.partition_number = pre.partition_number) AND (cur.rn = pre.rn +1)

The code is executed, and here is the result


As you can see, the index that I’m currently rebuilding is 15% done and there is 3,5 millions rows left for the rebuild process.


Posted by jamesbron on 14 April 2014

Very useful. Thanks !

Posted by srinath.m on 15 August 2014

Found it finally..Thank you very much for sharing!!!!

Posted by maulin.thaker 37820 on 6 April 2015


This is very useful.

But when i am using the same on SQL server 2014.

This query is not working.

Any idea ?

Posted by jakev1273 on 21 April 2015

Thanks for sharing.

Leave a Comment

Please register or log in to leave a comment.