Tracking Progress of SQL Server Index Builds and Rebuilds

Let's say you build or rebuild an index in SQL Server. Your users and boss are anxious to know when the table will stop being blocked and when their precious queries will run fast. What can you tell them?

SQL Server allows tracking the progress of index build and rebuilds. For online index operations, it's pretty straightforward, while for offline index operations it's trickier. Let's start with the easy part:

Online Index Build Tracking

SQL Server allows tracking the progress of online index operations using Profiler or Extended Events. The event is called "Progress Report: Online Index Operation" In Profiler and "progress_report_online_index_operation" In Extended Events.

Let's see how it looks. First, let's rebuild an index with a single thread:

ALTER INDEX pk_MemberSessions_c_IdON Operation.MemberSessionsREBUILD WITH(ONLINE=ON, MAXDOP=1)

Profiler shows the following data:

The BigintData1 column shows the number of rows that were already processed.

The BigintData2 column is currently meaningless. However, it does have a meaning when we build the index with a few threads:

Now we can see the thread number in the BigintData2 column. In this example, we can see the index is built with 4 threads and an even distribution between them.

Extended Events exposes the same information, and starting  with SQL Server 2014, it also exposes two more interesting columns—Partition ID and Partition Number:

Offline Index Build Tracking

This part is harder, and requires us to know the amount of pages an index holds before the rebuild is started.

For that, we can use the following query:

SELECTa.used_pages AS IndexSizeInPagesFROM sys.indexes iINNER JOIN sys.partitions pON p.object_id = i.object_id AND p.index_id = i.index_idINNER JOIN sys.allocation_units aON a.container_id = p.partition_idWHERE i.name = 'pk_MemberSessions_c_Id'

This number will give us an indication as to the general size of the index, but it won't provide us the accuracy we can get with online index operations.

With this number in mind, we can start rebuilding the index and monitor the amount of pages processed by the session that builds it. The closer the amount of pages processed gets to the number we got in the query, the closer the process to finish. As stated earlier, the size of the old index will be an indication for the size of the new index, but it won't be totally accurate, and the inaccuracy will be bigger as the index is bigger.

The monitoring can be done with the good old sp_who2, while providing a parameter of the session id who's building the index. The DiskIO column shows the amount of pages the session has processed. Keep in mind though that this column shows the amount of pages processed since the session started, so it can contain other operations such as other index builds.

In addition, if we use this method for an online index operation, the amount of pages will be twice the size of the index, which makes sense since we maintain two index trees at the time of the index rebuild.