インデックスの SORT_IN_TEMPDB オプション
適用対象: 
SQL Server Azure SQL データベース Azure SQL Managed Instance
インデックスを作成または再構築する際には、SORT_IN_TEMPDB オプションを ON に設定することにより、インデックスの構築に使用する中間の並べ替え結果の格納場所として SQL Server データベース エンジンが tempdb を使用するように指定できます。 このオプションを使用すると、インデックスの作成に使用する一時ディスク領域は増えますが、 tempdb がユーザー データベースとは異なるディスク セットにある場合、インデックスの作成または再構築に必要な時間を短縮できることがあります。 tempdbの詳細については、「 index create memory サーバー構成オプションの構成」を参照してください。
インデックス構築のフェーズ
データベース エンジンでインデックスを構築する際には、次のようなフェーズがあります。
データベース エンジンにより、最初にベース テーブルのデータ ページがスキャンされてキー値が取得されます。次に、データ行ごとにインデックス リーフ行が構築されます。 内部の並べ替えバッファーがリーフ インデックス エントリでいっぱいになると、そのエントリは並べ替えられて中間の並べ替え実行結果としてディスクに書き込まれます。 その後、データベース エンジンにより、データ ページのスキャンが再開され、並べ替えバッファーがいっぱいになるまで続行されます。 この複数のデータ ページをスキャンしてから、並べ替えと並べ替え実行結果の書き込みを行うという一連の操作は、ベース テーブルのすべての行が処理されるまで続きます。
クラスター化インデックスでは、インデックスのリーフ行がテーブルのデータ行なので、中間の並べ替え実行結果にはすべてのデータ行が含まれます。 非クラスター化インデックスでは、リーフ行に非キー列が含まれることがありますが、通常はクラスター化インデックスより小さくなります。 インデックス キーが大きい場合、またはインデックスに複数の非キー列が含まれている場合は、非クラスター化インデックスの並べ替え実行結果が大きくなることがあります。 非キー列を含めることの詳細については、「 付加列インデックスの作成」を参照してください。
データベース エンジンにより、インデックス リーフ行の実行結果が 1 つの並べ替えられたストリームにマージされます。 データベース エンジンの並べ替えマージ コンポーネントにより、各並べ替え実行結果の最初のページから開始して、すべてのページ内で最下位のキーが検索され、そのリーフ行がインデックス作成コンポーネントに渡されます。 次に、最初に処理された最下位のキーの次に低いキーが処理され、この方法で順に処理が行われます。 並べ替え実行結果ページから最後のリーフ インデックス行が取り出されると、処理がその並べ替え実行結果から次のページに切り替わります。 並べ替え実行エクステントに含まれるすべてのページが処理されると、そのエクステントは解放されます。 インデックス作成コンポーネントに渡された各リーフ インデックス行は、バッファー内のリーフ インデックス ページに格納されます。 各リーフ ページは、いっぱいになったときに書き込まれます。 リーフ ページが書き込まれると、データベース エンジンによりインデックスの上位レベルも構築されます。 上位レベルの各インデックス ページは、いっぱいになったときに書き込まれます。
SORT_IN_TEMPDB オプション
SORT_IN_TEMPDB を OFF (既定値) に設定すると、並べ替え実行結果が出力先のファイル グループに格納されます。 インデックスを作成する最初のフェーズの間、ベース テーブルのページの読み取りと並べ替え実行結果の書き込みを交互に実行することにより、ディスクの読み書きヘッドがディスクの 1 つの領域から別の領域に移動します。 ヘッドは、データ ページのスキャン時はデータ ページ領域にあります。 並べ替えバッファーがいっぱいになり、現在の並べ替え実行結果をディスクに書き込む必要があるときに空き領域に移動し、テーブル ページのスキャンを再開するときにデータ ページ領域に戻ります。 読み書きヘッドの移動は、2 番目のフェーズではさらに頻繁に行われます。 そのとき並べ替え処理では、通常、各並べ替え実行結果領域からの読み取りを繰り返し行っています。 並べ替え実行結果と新しいインデックス ページは、どちらも出力先のファイル グループに構築されます。 つまり、データベース エンジンは、並べ替え実行結果の読み取りを次々に行っていくのと同時に、インデックス エクステントに定期的にジャンプして、新しいインデックス ページがいっぱいになったときに書き込みを行う必要があります。
SORT_IN_TEMPDB オプションが ON に設定され、 tempdb が出力先のファイル グループとは別のディスク セットにある場合は、最初のフェーズの間に、 tempdbにある並べ替えの作業領域への書き込みとは異なるディスクでデータ ページの読み取りが行われます。 つまり、最終インデックスを構築するときの書き込みと同様に、ディスクのデータ キーの読み取りはディスクに対して通常はどちらかといえば連続的に進行し、 tempdb ディスクへの書き込みも通常は連続的に行われます。 他のユーザーがデータベースを使用していて個別のディスク アドレスにアクセスしている場合でも、SORT_IN_TEMPDB を指定したときの方が、指定しないときよりも一連の読み取りと書き込みが全体的に効率よく実行されます。
特に CREATE INDEX 操作が並列に処理されていない場合、SORT_IN_TEMPDB オプションにより、インデックス エクステントの連続性が向上します。 並べ替えの作業領域のエクステントは、データベース内の位置という点で多少ランダムに解放されます。 並べ替えの作業領域が出力先のファイル グループに含まれている場合、並べ替えの作業エクステントが解放されたときに、構築時のインデックス構造をエクステントが保持するように要求することで、それらのエクステントを獲得できます。 この方法では、インデックス エクステントの位置がある程度ランダムになります。 並べ替えのエクステントが tempdb内で別々に保持される場合、それらを解放する順序はインデックス エクステントの位置には影響しません。 また、中間の並べ替え実行結果が出力先のファイル グループではなく tempdb に格納される場合、出力先のファイル グループの空き領域は増加します。 これにより、インデックス エクステントが連続的になる可能性が高くなります。
SORT_IN_TEMPDB オプションは、現在のステートメントだけに影響します。 インデックスが tempdbで並べ替えられたかどうかを記録するメタデータはありません。 たとえば、SORT_IN_TEMPDB オプションを使用して非クラスター化インデックスを作成してから、このオプションを指定せずにクラスター化インデックスを作成した場合、Database Engine では、非クラスター化インデックスを再作成するときにこのオプションが使用されません。
Note
並べ替え操作が必要ない場合、または並べ替えをメモリ内で実行できる場合、SORT_IN_TEMPDB オプションは無視されます。
必要なディスク領域
SORT_IN_TEMPDB オプションを ON に設定する場合は、中間の並べ替え実行結果を保持するのに十分な空きディスク領域が tempdb に必要です。また、新しいインデックスを保持するのに十分な空きディスク領域が出力先のファイル グループに必要です。 空き領域が不足していて、何らかの理由 (ディスク領域不足や、自動拡張が無効になっているなど) でデータベースを自動拡張して空き領域を確保できない場合、CREATE INDEX ステートメントは失敗します。
SORT_IN_TEMPDB を OFF に設定する場合は、出力先のファイル グループに、最終インデックスとほぼ同じサイズの使用可能な空きディスク領域が必要です。 最初のフェーズでは、並べ替えの実行結果が構築され、最終インデックスとほぼ同じ量の領域が必要になります。 2 番目のフェーズでは、各並べ替え実行エクステントが、処理された後に解放されます。 つまり、並べ替え実行エクステントは、最終インデックス ページを保持するためにエクステントを獲得するのとほぼ同じ割合で解放されます。したがって、全体的に必要な領域が最終インデックスのサイズを大幅に超えることはありません。 これに伴う影響として、空き領域の大きさが最終インデックスのサイズに非常に近い場合、並べ替え実行エクステントが解放された後、Database Engine により通常はすぐに再利用されます。 並べ替え実行エクステントは多少ランダムに解放されるので、この状況ではインデックス エクステントの連続性が低下します。 SORT_IN_TEMPDB を OFF に設定した場合、出力先のファイル グループに十分な空き領域があり、新たに割り当て解除された並べ替え実行エクステントからではなく連続的なプールからインデックス エクステントを割り当てることができれば、インデックス エクステントの連続性が向上します。
非クラスター化インデックスを作成する場合、次のような状況に応じた空き領域が必要になります。
SORT_IN_TEMPDB を ON に設定した場合は、並べ替え実行結果を格納するのに十分な空き領域が tempdb に必要で、最終インデックス構造を格納するのに十分な空き領域が出力先のファイル グループに必要です。 並べ替え実行結果には、インデックスのリーフ行が含まれます。
SORT_IN_TEMPDB を OFF に設定した場合は、最終インデックス構造を格納するのに十分な空き領域が出力先のファイル グループに必要です。 インデックス エクステントの連続性は、空き領域が多いほど向上します。
非クラスター化インデックスが格納されていないテーブルにクラスター化インデックスを作成する場合、次のような状況に応じた空き領域が必要になります。
SORT_IN_TEMPDB を ON に設定した場合、並べ替え実行結果を格納するのに十分な空き領域が tempdb に必要です。 これらの並べ替え実行結果には、テーブルのデータ行が含まれます。 また、最終インデックス構造を格納するのに十分な空き領域が出力先のファイル グループに必要です。 最終インデックス構造には、テーブルとインデックス B-Tree のデータ行が含まれます。 キー サイズが大きくなることや FILL FACTOR の値が小さくなることなどの要因を考慮して、必要な空き領域の概算値を調整することが必要になる場合があります。
SORT_IN_TEMPDB を OFF に設定した場合は、最終テーブルを格納するのに十分な空き領域が出力先のファイル グループに必要です。 最終テーブルには、インデックス構造が含まれます。 テーブルとインデックスのエクステントの連続性は空き領域が多いほど向上します。
非クラスター化インデックスが格納されているテーブルにクラスター化インデックスを作成する場合、次のような状況に応じた空き領域が必要になります。
SORT_IN_TEMPDB を ON に設定した場合は、最も大きいインデックス (通常はクラスター化インデックス) の並べ替え実行結果のコレクションを格納するのに十分な空き領域が tempdb に必要であり、すべてのインデックスの最終構造を格納するのに十分な空き領域が出力先のファイル グループに必要です。 これには、テーブルのデータ行を格納しているクラスター化インデックスも含まれます。
SORT_IN_TEMPDB を OFF に設定した場合は、最終テーブルを格納するのに十分な空き領域が出力先のファイル グループに必要です。 最終テーブルには、すべてのインデックスの構造が含まれます。 テーブルとインデックスのエクステントの連続性は空き領域が多いほど向上します。