SYSK 195: Speeding Up Index Creation

In SQL Server 2000 and 2005 when you create or rebuild an index you can set SORT_IN_TEMPDB option to ON to use TempDB to store the intermediate sort results, which may reduce the time needed to create an index when TempDB is on a set of disks different from that of the user database.  

 

If the SORT_IN_TEMPDB option is set to ON and TempDB is on a separate set of disks from the destination file group, the reads of the data pages occur on a different disk from the writes to the sort work area in TempDB. This means the disk reads of the data keys generally continue more serially across the disk, and the writes to the TempDB disk also are generally 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.

 

Note:  If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

 

Example:

Create clustered index idx_MyTable on MyTable(c1) WITH (SORT_IN_TEMPDB = ON)

Go;

 

Note: This option increases the amount of temporary disk space that is used to create an index.

 

Special thanks to Saleem Hakani who has provided this information.