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
(
SELECT
object_id,
index_id,
partition_number,
rows,
ROW_NUMBER() OVER(PARTITION BY object_id, index_id, partition_number ORDER BY partition_id) as rn
FROM sys.partitions
)
SELECT
   object_name(cur.object_id) as TableName,
   cur.index_id,
   cur.partition_number,
   PrecentDone =
      CASE
         WHEN pre.rows = 0 THEN 0
      ELSE
         ((cur.rows * 100.0) / pre.rows)
      END,
   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)
ORDER BY 4

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.