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.