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_Id
ON Operation.MemberSessions
REBUILD WITH(ONLINE=ON, MAXDOP=1)

Profiler shows the following data:

Index Build Tracking with Profiler – Single Threaded

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:

Index Build Tracking with Profiler – Multi Threaded

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 SQL Server 2014, it also exposes two more interesting columns – Partition ID and Partition Number:

Index Build Tracking with Extended Events

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:

SELECT
a.used_pages AS IndexSizeInPages
FROM sys.indexes i
INNER JOIN sys.partitions p
ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.allocation_units a
ON a.container_id = p.partition_id
WHERE 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.

The post Tracking Progress of SQL Server Index Builds and Rebuilds appeared first on .