3

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

  • 1
    It resides on same file where data file resides unless you use Sort_in_tempdb option during rebuild – Shanky Jun 8 '15 at 14:43
  • @Shanky you should turn your comment to an answer – spaghettidba Jun 8 '15 at 15:59
3

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.

  • Thanks for this, great answer. So to confirm the new index will reside in tempdb regardless of whether sort_in_tempdb is on or off. Therefore if I have a 100GB Index which is scheduled for a rebuild online, then I need to ensure I have at least 100GB free in tempdb? – hpk89 Jun 9 '15 at 9:43
  • Not exactly. The tempdb space needed depends whether a sort is actually needed, read SORT_IN_TEMPDB Option For Indexes. This is completely orthogonal to the index being online or offline built. – Remus Rusanu Jun 9 '15 at 9:50
  • That article is spot on. Just to confirm the destination filegroup = wherever the index file group is stored and not where the primary data file group is stored? – hpk89 Jun 9 '15 at 9:56
  • As I say, the destination must match the final index destination, otherwise the data would have to be moved after the index is built. – Remus Rusanu Jun 9 '15 at 10:03
1

My understanding is the snapshot should reside on the index file drive. Is this correct?

It resides on same file where Index file resides unless you use Sort_in_tempdb option during rebuild. From BOL

When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the destination filegroup.

If the SORT_IN_TEMPDB option is set to ON and tempdb is on a separate set of disks from the destination filegroup, during the first phase, the reads of the data pages occur on a different disk from the writes to the sort work area in tempdb.


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!

If drive on which index resides runs out of space the index rebuild operation will fail and changes will be rolled back. Index rebuild works like a transaction either complete index would be rebuild or it would go back to its previous state.

EDIT:

You said that the new index would reside where the data file resides not where the index resides. Can you confirm that this is correct

Your question was where would the snapshot Index reside which I assumed you were asking about where would intermediate sort operations happens.

  1. Please note that intermediate sort operation is different from row versioning what Remus pointed out which is only used with ONLINE index rebuild. If you use SORT_IN_TEMPDB option the intermediate sort result will be stored in tempdb database.

  2. If you read Disk Space Requirement for Index DDL operation it says for a clustered index is created, rebuilt, or dropped online, a temporary nonclustered index is created to map old bookmarks to new bookmarks. If the SORT_IN_TEMPDB option is set to ON, this temporary index is created in tempdb. If SORT_IN_TEMPDB is set to OFF, the same filegroup or partition scheme as the target index is used. The confusion originated because of below statement from Alter Index BOL Page.

The intermediate sort results are stored in the same database as the index. If a sort operation is not required, or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored

  • Thanks Shanky. You said that the new index would reside where the data file resides not where the index resides. Can you confirm that this is correct? The article regarding sorting suggests destination file group, which I would expect means the index file group? – hpk89 Jun 9 '15 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.