When the rebuild index was being rebuilt on Saturday, everyone was coming up with their own version why it was taking so long to complete. Ultimately it turned out to be the issue with the disk subsystems, nothing else. One of the Application Team member pointed out to an interesting observation.
We use Idera Diagnostic Manager to monitor our Production instances. When we were rebuilding the 13th index on that table, they pointed out that in Idera they could see an SQL statement which looked strange.
insert [DatabaseName].[dbo].[TheActualTableName] select *, %%bmk%% from [DatabaseName].[dbo].[TheActualTableName]
Everyone started asking who is running this insert and what is %%bmk%% ? I remembered reading about bmk sometime back on this whitepaper at TechNet. Even though this whitepaper talks about Online Reindex, it talks about how an Index is rebuilt in general. During the Build Phase of rebuilding the index, the new index is built by Bulk Inserting data from the old index. During the bulk insertion, the above insert statement is issued. Since the table in question was 50 GB in size and the disk subsystem was slow, this query ran for a long time and it was captured by Idera Diagnostic Manager.
Even though I knew this on the tip of my tongue, customer needs the “official” documentation to accept it. It took a while to google for this document and pass on the snip to him. It was a nice revisit to the re-index internals.