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