用于索引的 SORT_IN_TEMPDB 选项SORT_IN_TEMPDB Option For Indexes

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

当创建或重新生成索引时,通过将 SORT_IN_TEMPDB 选项设置为 ON,可以指定 SQL Server 数据库引擎SQL Server Database Engine 使用 tempdb 来存储用于生成索引的中间排序结果。When you create or rebuild an index, by setting the SORT_IN_TEMPDB option to ON you can direct the SQL Server 数据库引擎SQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index. 虽然此选项会增加创建索引所用的临时磁盘空间量,但是当 tempdb 与用户数据库位于不同的磁盘集上时,该选项可减少创建或重新生成索引所需的时间。Although this option increases the amount of temporary disk space that is used to create an index, the option could reduce the time that is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database. 有关 tempdb的详细信息,请参阅 配置 index create memory 服务器配置选项For more information about tempdb, see Configure the index create memory Server Configuration Option.

索引生成阶段Phases of Index Building

数据库引擎Database Engine 生成索引时经历下面两个阶段:As the 数据库引擎Database Engine builds an index, it goes through the following phases:

  • 数据库引擎Database Engine 首先扫描基表的数据页以检索键值,并为每个数据行生成索引叶行。The 数据库引擎Database Engine first scans the data pages of the base table to retrieve key values and builds an 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. 然后, 数据库引擎Database Engine 继续扫描数据页,直到排序缓冲区再次被填满。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; therefore, the intermediate sort runs contain all the data rows. 在非聚集索引中,叶行可能包含非键列,但通常比聚集索引小。In a nonclustered index, the leaf rows may contain nonkey columns, but are generally smaller than a clustered index. 如果索引键很大,或者索引中包含多个非键列,则非聚集排序运行指令也可能很大。If the index keys are large, or there are several nonkey columns included in the index, a nonclustered sort run can be large. 有关包含非键列的详细信息,请参阅 Create Indexes with Included ColumnsFor more information about including nonkey columns, see Create Indexes with Included Columns.

  • 数据库引擎Database Engine 将排序的索引叶行进程合并为单个排序流。The 数据库引擎Database Engine merges the sorted runs of index leaf rows into a single, sorted stream. 数据库引擎Database Engine 的排序合并组件从每个排序运行指令的第一页开始,在所有页中找出最低键,并将该叶行传递到索引创建组件。The sort merge component of the 数据库引擎Database 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 processed, and 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 included in a leaf index page in the buffer. 每个叶页在填充时被写入。Each leaf page is written as it is filled. 当写入叶页时, 数据库引擎Database Engine 还生成该索引的上级。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.

SORT_IN_TEMPDB 选项SORT_IN_TEMPDB Option

如果 SORT_IN_TEMPDB 设置为 OFF(默认设置),则排序运行指令将存储在目标文件组中。When SORT_IN_TEMPDB is set to OFF, the default, 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, and then move back to the data page area as the table page scan is resumed. 在第二阶段,读/写磁头的移动频率较高。The read/write head movement is greater 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. 这意味着 数据库引擎Database Engine 在排序运行指令中分布读取的同时,还必须定期跳至索引区,以便在填充索引页时写入新的索引页。This means 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.

如果 SORT_IN_TEMPDB 选项设置为 ON,并且 tempdb 与目标文件组位于不同的磁盘集上,那么在第一阶段,对数据页的读取与对 tempdb中排序工作区的写入发生在不同的磁盘上。If the SORT_IN_TEMPDB option is set to ON and tempdb is on a separate set of disks from the destination filegroup, during the first phase, the reads of the data pages occur on a different disk from the writes to the sort work area in tempdb. 这意味着对数据键的磁盘读取在整个磁盘上通常较连续地进行,而对 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. 即使其他用户正在使用数据库且正在访问不同的磁盘地址,指定 SORT_IN_TEMPDB 选项时的总体读写模式的效率也比没有指定时要高。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.

SORT_IN_TEMPDB 选项可能会提高索引区的邻接,尤其当不是并行处理 CREATE INDEX 操作时。The SORT_IN_TEMPDB option may improve the contiguity of index extents, especially if the CREATE INDEX operation is not being processed in parallel. 排序工作区在数据库中的释放位置方面有些随机。The sort work area extents are freed on a somewhat random basis with regard to their location in the database. 如果排序工作区包含在目标文件组中,则释放排序工作区时,可通过请求来获取它们,以使区在生成时具有索引结构。If the sort work areas are contained in the destination filegroup, 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 degree. 如果排序区单独保存在 tempdb中,则释放排序区的顺序对索引区的位置没有影响。If the sort extents are held separately in tempdb, the sequence in which they are freed has no effect on the location of the index extents. 此外,当中间排序运行指令存储在 tempdb 中而不是目标文件组中时,目标文件组中的可用空间较多。Also, when the intermediate sort runs are stored in tempdb instead of the destination filegroup, there is more space available in the destination filegroup. 这增加了索引区连续的可能性。This increases the chances that index extents will be contiguous.

SORT_IN_TEMPDB 选项仅影响当前的语句。The SORT_IN_TEMPDB option affects only the current statement. 没有任何元数据记录索引是否存储在 tempdb中。No metadata records that the index was or was not sorted in tempdb. 例如,如果使用 SORT_IN_TEMPDB 选项创建一个非聚集索引,然后在不指定该选项的情况下创建一个聚集索引,则 数据库引擎Database Engine 在重新创建该聚集索引时不使用该选项。For example, if you create a nonclustered index using the SORT_IN_TEMPDB option, and at a later time create a clustered index without specifying the option, the 数据库引擎Database Engine does not use the option when it re-creates the nonclustered index.

备注

如果不需要排序操作或可以在内存中执行排序,则忽略 SORT_IN_TEMPDB 选项。If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

磁盘空间要求Disk Space Requirements

如果将 SORT_IN_TEMPDB 选项设置为 ON,则 tempdb 中必须有足够的可用空间容纳中间排序运行指令,且目标文件组中必须有足够的可用空间容纳新的索引。When you set the SORT_IN_TEMPDB option to ON, you must have sufficient free disk space available in tempdb to hold the intermediate sort runs, and enough free disk space in the destination filegroup to hold the new index. 如果可用空间不足,并且由于某些原因数据库无法自动增长以获得更多空间(如磁盘上无空间或自动增长设置为关闭),则 CREATE INDEX 语句将失败。The CREATE INDEX statement fails if there is insufficient free space and there is some reason the databases cannot autogrow to acquire more space, such as no space on the disk or autogrow is set to off.

如果 SORT_IN_TEMPDB 设置为 OFF,目标文件组中的可用磁盘空间必须大约等于最终索引的大小。If SORT_IN_TEMPDB is set to OFF, the available free disk 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; therefore, the overall space requirements do not greatly exceed the size of the final index. 这样的一个副作用是如果可用空间量非常接近最终索引的大小,则 数据库引擎Database Engine 通常会在释放排序运行指令区后立即重新使用它们。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 generally 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. SORT_IN_TEMPDB 设置为 OFF 时,如果目标文件组中有足够的可用空间,则可以从邻接的池而不是从刚刚释放的排序段区中分配索引区,这将提高索引区的连续性。If SORT_IN_TEMPDB is set to OFF, the continuity of the index extents is improved if there is sufficient free space available in the destination filegroup that the index extents can be allocated from a contiguous pool instead of from the freshly deallocated sort run extents.

创建非聚集索引时,必须有足够的可用空间:When you create a nonclustered index, you must have available as free space:

  • 如果 SORT_IN_TEMPDB 设置为 ON,则 tempdb 中必须有足够的可用空间来存储排序运行指令,且目标文件组中必须有足够的可用空间来存储最终索引结构。If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs, and sufficient free space in the destination filegroup to store the final index structure. 排序运行指令包含索引的叶行。The sort runs contain the leaf rows of the index.

  • 如果 SORT_IN_TEMPDB 设置为 OFF,则目标文件组中必须有足够的可用空间来存储最终索引结构。If SORT_IN_TEMPDB is set to OFF, 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, you must have available as free space:

  • 如果 SORT_IN_TEMPDB 设置为 ON,则 tempdb 中必须有足够的可用空间来存储排序运行指令。If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs. 其中包括表的数据行。These include the data rows of the table. 目标文件组中必须有足够的可用空间来存储最终索引结构。There must be sufficient free space in the destination filegroup to store the final index structure. 包括表的数据行和索引 B 树。This includes the data rows of the table and the index B-tree. 您可能需要根据不同的因素调整估计值,如键的大小很大或填充因子的值很低。You may have to adjust the estimate for factors such as having a large key size or a fill factor with a low value.

  • 如果 SORT_IN_TEMPDB 设置为 OFF,目标文件组中必须有足够的可用空间来存储最终表。If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. 包括索引结构。This includes 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, you must have available as free space:

  • 如果 SORT_IN_TEMPDB 设置为 ON,则 tempdb 中必须有足够的可用空间来存储最大索引(通常是聚集索引)的排序运行指令的集合,且目标文件组中必须有足够的可用空间来存储所有索引的最终结构。If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the collection of sort runs for the largest index, typically the clustered index, and sufficient free space in the destination filegroup to store the final structures of all the indexes. 包括包含表的数据行的聚集索引。This includes the clustered index that contains the data rows of the table.

  • 如果 SORT_IN_TEMPDB 设置为 OFF,目标文件组中必须有足够的可用空间来存储最终表。If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. 包括所有索引的结构。This includes the structures of all the indexes. 如果具有更多的可用空间,则可以提高表和索引区的连续性。The continuity of the table and index extents may be improved if more free space is available.

CREATE INDEX (Transact-SQL)CREATE INDEX (Transact-SQL)

重新组织和重新生成索引Reorganize and Rebuild Indexes

ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)

配置 index create memory 服务器配置选项Configure the index create memory Server Configuration Option

索引 DDL 操作的磁盘空间要求Disk Space Requirements for Index DDL Operations