索引的 SORT_IN_TEMPDB 選項SORT_IN_TEMPDB Option For Indexes

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

當您建立或重建索引時,可將 SORT_IN_TEMPDB 選項設為 ON,以便指示 SQL Server Database EngineSQL 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 Database EngineSQL 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 EngineDatabase Engine 建立索引時,它將經歷下列階段:As the Database EngineDatabase Engine builds an index, it goes through the following phases:

  • Database EngineDatabase Engine 會先掃描基底資料表的資料頁來擷取索引鍵值,然後替每個資料列建立索引分葉資料列。The Database EngineDatabase 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 EngineDatabase Engine 將繼續進行資料頁掃描,直到排序緩衝區再次填滿為止。The Database EngineDatabase 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. 如需包含非索引鍵資料行的詳細資訊,請參閱 建立內含資料行的索引For more information about including nonkey columns, see Create Indexes with Included Columns.

  • Database EngineDatabase Engine 會將索引分葉資料列的排序結果,合併成一個排序好的資料流。The Database EngineDatabase Engine merges the sorted runs of index leaf rows into a single, sorted stream. Database EngineDatabase Engine 的排序合併元件將開始於每個排序結果的第一頁,找出所有頁面中的最低索引鍵,並將該分葉資料列傳送給索引建立元件。The sort merge component of the Database EngineDatabase 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 EngineDatabase Engine 也會建立索引的上層。As leaf pages are written, the Database EngineDatabase 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 EngineDatabase Engine 在將讀取分散到各排序結果的同時,還必須定期跳到索引範圍,以在索引頁面填滿時寫入新的索引頁面。This means that at the same time the Database EngineDatabase 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 EngineDatabase 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 EngineDatabase 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 EngineDatabase 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 EngineDatabase 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