Rebuilding Index - sort in temp db

Question

What is the significance of SORT_IN_TEMPDB when working with indexes on a table of significant size (20GB plus)?

Where else does sorting take place?

Answer 1

It depends.

The options are -

• Sort in the destination file group (normally PRIMARY on the DB the index is on)
• Sort in TempDB

If your TempDB is on a different disk array and not active, you could see a performance increase. There are also space considerations.

There's a pretty good article on MSDN about this topic here.

Answer 2

What it does is instead of utilizing the target database to do the intermediate sorts for creating and rebuilding the index, it uses tempdb for this task. You will most likely see performance gains as far as duration of the time it would normally take, but it will also increase the amount of disk space used.

Reference.