tempdb とインデックスの作成

[アーティクル]
12/16/2008

インデックスを作成または再構築する際には、SORT_IN_TEMPDB オプションを ON に設定することにより、インデックスの構築に使用する中間の並べ替え結果の格納場所としてSQL Server 2005 データベースエンジンが tempdb を使用するように指定できます。このオプションを使用すると、インデックスの作成に使用する一時ディスク領域は増えますが、tempdb がユーザーデータベースとは異なるディスクセットにある場合、インデックスの作成または再構築に必要な時間を短縮できることがあります。tempdb の詳細については、「tempdb データベース」を参照してください。

インデックス構築のフェーズ

データベースエンジンでインデックスを構築する際には、次のようなフェーズがあります。

データベースエンジンにより、最初にベーステーブルのデータページがスキャンされてキー値が取得されます。次に、データ行ごとにインデックスリーフ行が構築されます。内部の並べ替えバッファがリーフインデックスエントリでいっぱいになると、そのエントリは並べ替えられて中間の並べ替え実行結果としてディスクに書き込まれます。その後、データベースエンジンによりデータページのスキャンが再開され、並べ替えバッファがいっぱいになるまで続行されます。この複数のデータページをスキャンしてから、並べ替えと並べ替え実行結果の書き込みを行うという一連の操作は、ベーステーブルのすべての行が処理されるまで続きます。
クラスタ化インデックスでは、インデックスのリーフ行がテーブルのデータ行なので、中間の並べ替え実行結果にはすべてのデータ行が含まれます。非クラスタ化インデックスでは、リーフ行に非キー列が含まれることがありますが、通常はクラスタ化インデックスより小さくなります。インデックスキーが大きい場合、またはインデックスに複数の非キー列が含まれている場合は、非クラスタ化インデックスの並べ替え実行結果が大きくなることがあります。非キー列を含めることの詳細については、「付加列インデックスの作成」を参照してください。
データベースエンジンにより、インデックスリーフ行の実行結果が 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 オプションを使用して非クラスタ化インデックスを作成してから、このオプションを指定せずにクラスタ化インデックスを作成した場合、データベースエンジンでは、非クラスタ化インデックスを再作成するときにこのオプションが使用されません。

メモ :
並べ替え操作が必要ない場合、または並べ替えをメモリ内で実行できる場合、SORT_IN_TEMPDB オプションは無視されます。

必要なディスク領域

SORT_IN_TEMPDB オプションを ON に設定する場合は、中間の並べ替え実行結果を保持するのに十分な空きディスク領域が tempdb に必要です。また、新しいインデックスを保持するのに十分な空きディスク領域が出力先のファイルグループに必要です。空き領域が不足していて、何らかの理由 (ディスク領域不足や、自動拡張が無効になっているなど) でデータベースを自動拡張して空き領域を確保できない場合、CREATE INDEX ステートメントは失敗します。

SORT_IN_TEMPDB を OFF に設定する場合は、出力先のファイルグループに、最終インデックスとほぼ同じサイズの使用可能な空きディスク領域が必要です。最初のフェーズでは、並べ替えの実行結果が構築され、最終インデックスとほぼ同じ量の領域が必要になります。2 番目のフェーズでは、各並べ替え実行エクステントが、処理された後に解放されます。つまり、並べ替え実行エクステントは、最終インデックスページを保持するためにエクステントを獲得するのとほぼ同じ割合で解放されます。したがって、全体的に必要な領域が最終インデックスのサイズを大幅に超えることはありません。これに伴う影響として、空き領域の大きさが最終インデックスのサイズに非常に近い場合、並べ替え実行エクステントが解放された後、データベースエンジンにより通常はすぐに再利用されます。並べ替え実行エクステントは多少ランダムに解放されるので、この状況ではインデックスエクステントの連続性が低下します。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 に設定した場合は、最終テーブルを格納するのに十分な空き領域が出力先のファイルグループに必要です。最終テーブルには、すべてのインデックスの構造が含まれます。テーブルとインデックスのエクステントの連続性は空き領域が多いほど向上します。

参照

tempdb とインデックスの作成

インデックス構築のフェーズ

SORT_IN_TEMPDB オプション

必要なディスク領域

参照

概念

その他の技術情報

ヘルプおよび情報

その他のリソース