tempdb and Index Creation

tempdb and Index Creation

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:

• The database engine first scans the data pages to retrieve key values and builds a index leaf row for each data row. When the internal sort buffers have been filled with leaf index entries, the entries are sorted and written to disk as an intermediate sort run. The database engine then resumes the data page scan until the sort buffers are again filled. This pattern of scanning multiple data pages followed by sorting and writing a sort run continues until all the rows of the base table have been processed. In a clustered index, the leaf rows of the index are the data rows of the table, so the intermediate sort runs contain all the data rows. In a nonclustered index, the leaf rows do not contain values from nonkey columns, so are generally smaller. A nonclustered sort run can be large, however, if the index keys are large.
  
  
• The database engine merges the sorted runs of index leaf rows into a single, sorted stream. The sort merge component of the engine starts with the first page of each sort run, finds the lowest key in all the pages, and passes that leaf row to the index create component. The next lowest key is then processed, then the next, and so on. When the last leaf index row is extracted from a sort run page, the process shifts to the next page from that sort run. When all the pages in a sort run extent have been processed, the extent is freed. As each leaf index row is passed to the index create component, it is placed in a leaf index page in the buffer. Each leaf page is written as it is filled. As leaf pages are written, the database engine also builds the upper levels of the index. Each upper level index page is written when it is filled.

If you create a clustered index on a table that has existing nonclustered indexes, the general process is:

• The nonclustered indexes are deallocated, but the definitions of the indexes are retained. The space is not available for use until the end of the transaction containing the CREATE INDEX statement, so that the old index pages are still available if they have to be restored during a rollback of the transaction.
  
  
• The clustered index is created.
  
  
• The nonclustered indexes are re-created.

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.

Free Space Requirements

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:

• When you create a nonclustered index:
  • If SORT_IN_TEMPDB is specified, there must be enough free space in tempdb to store the sort runs, and enough free space in the destination filegroup to store the final index structure. The sort runs contain the leaf rows of the index.
    
    
  • If SORT_IN_TEMPDB is not specified, the free space in the destination filegroup must be large enough to store the final index structure. The continuity of the index extends may be improved if more free space is available.
• When you create a clustered index on a table that does not have nonclustered indexes:
  • If SORT_IN_TEMPDB is specified, there must be enough free space in tempdb to store the sort runs, which include the data rows of the table. There must be enough free space in the destination filegroup to store the final index structure, including the data rows of the table and the index B-tree. A rough estimate is 1.2 times the size of the original table, although you may need to adjust the estimate for factors such as having a large key size or a fillfactor with a low value.
    
    
  • If SORT_IN_TEMPDB is not specified, the free space in the destination filegroup must be large enough to store the final table, including the index structure. The continuity of the table and index extents may be improved if more free space is available.
• When you create a clustered index on a table that has nonclustered indexes:
  • If SORT_IN_TEMPDB is specified, there must be enough free space in tempdb to store the collection of sort runs for the largest index (typically the clustered index), and enough free space in the destination filegroup to store the final structures of all the indexes, including the clustered index that contains the data rows of the table.
    
    
  • If SORT_IN_TEMPDB is not specified, the free space in the destination filegroup must be large enough to store the final table, including the structures of all the indexes. The continuity of the table and index extents may be improved if more free space is available.

See Also

CREATE INDEX