How to monitor progress of Offline Index Rebuild

Question: I’ve created a list of Non_clustered indexes , which were formerly disabled. I’ve used the script in this link.SQL Server Disable Indexes and Rebuild Indexes dynamically

The method I’ve used is for an Offline Index rebuild.

The table has 250 million rows with 17 non-clustered indexes, therefore they are taking a long time to rebuild.

What method(s) can I use to monitor the progress of the Index rebuild?

Answer: Offline Index rebuilds are slightly trickier than Online to monitor progress. In your circumstances there are a couple of methods to use.

As the non clustered indexes were disabled prior to the start of the build , then it is possible to use a simple method and regularly check on the is_disabled column

SELECT  sys.indexes.name,is_disabled
FROM    sys.indexes         JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id 
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'   
AND sys.objects.type_desc = 'USER_TABLE' 
AND sys.objects.name = 'MyTable'

The other method to use is to monitor the pages processed. It’s not a completely accurate method, but it will offer a general guide on the progress of an Offline Index Rebuild. The first step is to obtain the used pages of the index. Use this query and note the used_pages column

SELECT o.name AS table_name,p.index_id, au.used_pages,i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number
FROM sys.allocation_units AS au
    JOIN sys.partitions AS p ON au.container_id = p.partition_id
    JOIN sys.objects AS o ON p.object_id = o.object_id
    JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id
WHERE o.name = N'myTable' 

Once you’ve got the used pages , you can use sp_who2 ( or equivalent DMV) to monitor the Disk IO column , based on the session id.   The tricky bit and one of the reasons this method is a rough estimate is the DiskIO measurement is of all pages processed since the start of the session. Therefore being able to isolate one particular non clustered index within a session managing multiple index rebuilds is a frustrating task!

Read More on NonClustered Index management

SQL Server – Moving a Non Clustered Index to another filegroup ...

SQL Server - List all indexes of all tables - SQL Server DBA