Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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

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

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.