My question is related to index rebuilding, mainly the SORT_IN_TEMPDB option.

BOL states that:

Even if other users are using the database and accessing separate disk addresses, the overall pattern of reads and writes are more efficient when SORT_IN_TEMPDB is specified than when it is not.

On the other hand, one of the users states:

When rebuilding an index you would need twice the space of the index + 20% for the sorting. So in general to rebuild every index in your db you only need 120% of your biggest index in your DB. If you use SORT_IN_TEMPDB, you only win 20%, you still need an aditional 100% in your data file. Further more, using sort in tempdb increases your IO load drastically, since instead of Writing the index one time to the datafile, you now write it one time to the tempdb and then write it to the data file. So that is not always ideal.

Would you like to share your own experience about this option? Have you ever had to use this option while rebuilding indexes? What was the performance result?

  • 1
    see my answer - When to use sort_in_tempdb when rebuilding indexes? – Kin Shah Oct 11 '19 at 13:36
  • Thank you. As I understand, if I have correctly configured tempdb and that tempdb is located on the different disk then setting SORT_IN_TEMPDB option can indeed boost the performance, right? – RaufDBA Oct 11 '19 at 13:41
  • There are a lot of factors that could impact this. For example, if you have user database on slow RAID 5 and tempdb on fast SSD, it may be faster to sort in tempdb. It's simply a matter of running it both ways on a system to see if it's better. – Tony Hinkle Oct 11 '19 at 13:41
  • Thank you @TonyHinkle as well. I see. – RaufDBA Oct 11 '19 at 13:43
  • @RaufDBA - yes. See my answer. Plus, recovery for tempdb is simple, so it benefits from minimal logging. – Kin Shah Oct 11 '19 at 14:53