I was eavesdropping on a conversation between SQL Server MVP Hilary Cotter and Microsoftee and uber-genius Stefano Stefani. Hilary was concerned that performing online indexing on a large table might performance degradation, consume significant amounts of ram in tempdb, bloat the transaction log, and so forth. In particular, Hilary was (rightfully) concerned about whether an on-line index rebuild could withstand a reboot or if the session were killed in mid-process. Unfortunately, online indexing is not restartable, Stefano pointed out. If the operation aborts for any reason, all the intermediate work will be lost. Stefano also had some more pointers about the amount of space consumed during online indexing. The amount of space consumed mostly depends on whether you are building or rebuilding, and whether the target is a clustered or nonclustered index. Index build or rebuild operations sometimes needs to sort the data, the main reason for space consumption. You can control where this space is consumed according to how you set the SORT_IN_TEMPDB option. Online index operations against the clustered index also need to allocate space for the mapping index, which is not the case with offline indexing. Stefano pointed out this list of typical indexing scenarios and what happens with them: a) Online clustered index build require both sort and mapping index. b) Online clustered index rebuild needs only the mapping index. c) Online nonclustered index build require only a sort index. d) Online nonclustered index rebuild needs nothing. Option D is actually better than an offline nonclustered index rebuild because the offline nonclustered index rebuild currently requires a temporary sort, while option D does not. Furthermore, Options A and B require more temporary space than the offline counterpart. In terms of temporary space, Option C is the same as offline, and (as mentioned earlier) Option D is better. For more information, there is a topic on this in BOL - subject "Index Disk Space Example".
