When you create an index, you can specify WITH SORT_IN_TEMPDB option, which directs the database engine to use tempdb to store the intermediate sort results used to build the index. Although this option increases the amount of disk space used to create an index, it reduces the time it takes to create an index when tempdb is on a different set of disks than the user database.
As the database engine builds an index, it goes through two phases:
If you create a clustered index on a table that has existing nonclustered indexes, the general process is:
When SORT_IN_TEMPDB is not specified, the sort runs are stored in the destination filegroup. During the first phase of creating the index, the alternating reads of the base table pages and writes of the sort runs move the disk read-write heads from one area of the disk to another. The heads are in the data page area as the data pages are scanned. They move to an area of free space when the sort buffers fill and the current sort run has to be written to disk, then move back to the data page area as the table page scan is resumed. The read-write head movement is higher in the second phase. At that time the sort process is typically alternating reads from each sort run area. Both the sort runs and the new index pages are built in the destination filegroup, meaning that at the same time the database engine is spreading reads across the sort runs, it has to periodically jump to the index extents to write new index pages as they are filled.
If the SORT_IN_TEMPDB option is specified and tempdb is on a separate set of disks from the destination filegroup, then during the first phase the reads of the data pages occur on a different disk than the writes to the sort work area in tempdb. This means the disk reads of the data keys tend to proceed more serially across the disk, and the writes to the tempdb disk also tend to be serial, as do the writes to build the final index. 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.
The SORT_IN_TEMPDB option may improve the contiguity of index extents, especially if the CREATE INDEX is not being processed in parallel. The sort work area extents are freed on a somewhat random basis with respect to their location in the database. If the sort work areas are contained in the destination filegroup, then as the sort work extents are freed, they can be acquired by the requests for extents to hold the index structure as it is built. This can randomize the locations of the index extents to a certain degree. If the sort extents are held separately in tempdb, the sequence in which they are freed has no bearing on the location of the index extents. Also, when the intermediate sort runs are stored in tempdb instead of the destination filegroup, there is more space available in the destination filegroup, which increases the chances that index extents will be contiguous.
The SORT_IN_TEMPDB option affects only the current statement. No meta data records that the index was or was not sorted in tempdb. For example, if you create a nonclustered index using the SORT_IN_TEMPDB option, and later create a clustered index without specifying the option, the database engine does not use the option when it re-creates the nonclustered index.
When you specify the SORT_IN_TEMPDB option, you must have sufficient free space available in tempdb to hold the intermediate sort runs, and enough free space in the destination filegroup to hold the new index. The CREATE INDEX statement fails if there is not enough free space and there is some reason the databases cannot autogrow to acquire more space (such as no space on the disk, or autogrow turned off).
If SORT_IN_TEMPDB is not specified, the available free space in the destination filegroup must be roughly the size of the final index. During the first phase, the sort runs are built and require about the same amount of space as the final index. During the second phase, each sort run extent is freed after it has been processed. This means that sort run extents are freed at about the same rate at which extents are acquired to hold the final index pages, so the overall space requirements do not greatly exceed the size of the final index. One side effect of this is that if the amount of free space is very close to the size of the final index, the database engine will tend to reuse the sort run extents very quickly after they are freed. Because the sort run extents are freed in a somewhat random manner, this reduces the continuity of the index extents in this scenario. If SORT_IN_TEMPDB is not specified, the continuity of the index extents is improved if there is enough free space available in the destination filegroup that the index extents can be allocated from a contiguous pool rather than from the freshly deallocated sort run extents.
At the time you execute the CREATE INDEX statement, you must have available as free space: