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?

migrated from stackoverflow.com Feb 13 '12 at 22:08

This question came from our site for professional and enthusiast programmers.


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.


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.


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.