Sql-server – Online index rebuild snapshot location and impact of filling index drive

indexsnapshotsql serversql-server-2005

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index.

My question is where does the snapshot index reside?

Our databases have data, log and index file drives. My understanding is the snapshot should reside on the index file drive. Is this correct?

In addition what happens if the index drive runs out of space? Anyone had experience of this? It wont be an issue but I would like to know!

Version Info: SQL Server 2005 9.00.4266.00(x64) EE

Best Answer

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index.

Incorrect. An unfortunate overload of the term 'snapshot'... A snapshot read of the index is used, which means row-versioning see How Online Index Operations Work:

A snapshot of the table is defined. That is, row versioning is used to provide transaction-level read consistency.

With this correction, the rest of the question is moot. Row-versioning does not create a copy of the data until data is modified, and then the copy resides in tempdb. In other words, as you continue to modify the original index while OIB is running the row-versioning will have to preserve the pre-update image of the updated rows in tempdb.

@Shanky is right about SORT_IN_TEMPDB, but that refers to the index builder, a different stage, unrelated to the original 'snapshot', and an option that is independent of the 'online' nature of the OIB.

Obviously during the OIB you will slowly build up a copy of the data (the new index). This must be in the same location as the original index (including filegroups for partitions etc) as it has to be a valid replacement of the original index when the OIB is done.

Related Question