Printed 2014/07/28 04:52AM

Index Rebuild progress v2

By Geniiius, 2011/06/21

I have actually written about this subject earlier on my Danish blog (, 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.

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.