ScottPletcher (5/9/2014)
Jeff Moden (5/7/2014)
On the SORT IN TEMPDB thing... It'll only affect TEMPDB by 10 or 20% of what the largest index is.
I don't understand the rationale for that statement. I don't see how the total tempdb space used can be any smaller than the largest index size.
I agree that the documentation that MS provides states that you must have enough space available to TEMPDB to hold the leaf level of the entire index if the SORT_IN_TEMPDB option is used. I've never seen that happen, though, despite rebuilding some rather large clustered indexes. In fact, the TEMPDB space used for an index rebuild with the SORT_IN_TEMPDB option turned on can be 0. From http://technet.microsoft.com/en-us/library/ms188281.aspx...
Note
If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.
--Jeff Moden
Change is inevitable... Change for the better is not.