インデックスの SORT_IN_TEMPDB オプションSORT_IN_TEMPDB Option For Indexes

適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database

インデックスを作成または再構築する際には、SORT_IN_TEMPDB オプションを ON に設定することにより、インデックスの構築に使用する中間の並べ替え結果の格納場所として SQL Server データベース エンジンSQL Server Database Enginetempdb を使用するように指定できます。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. 非キー列を含めることの詳細については、「 付加列インデックスの作成」を参照してください。For more information about including nonkey columns, see Create Indexes with Included Columns.

  • データベース エンジンDatabase Engine により、インデックス リーフ行の実行結果が 1 つの並べ替えられたストリームにマージされます。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. インデックスを作成する最初のフェーズの間、ベース テーブルのページの読み取りと並べ替え実行結果の書き込みを交互に実行することにより、ディスクの読み書きヘッドがディスクの 1 つの領域から別の領域に移動します。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. 読み書きヘッドの移動は、2 番目のフェーズではさらに頻繁に行われます。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.

特に CREATE INDEX 操作が並列に処理されていない場合、SORT_IN_TEMPDB オプションにより、インデックス エクステントの連続性が向上します。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. 2 番目のフェーズでは、各並べ替え実行エクステントが、処理された後に解放されます。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-Tree のデータ行が含まれます。This includes the data rows of the table and the index B-tree. キー サイズが大きくなることや FILL FACTOR の値が小さくなることなどの要因を考慮して、必要な空き領域の概算値を調整することが必要になる場合があります。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