インデックスの作成や再構築の際にソート領域がメモリでは足りなくなった場合にインデックスを操作するデータベースのデータ領域ではなく、tempdb をソート領域として使用するためのオプションに SORT_IN_TEMPDB があります。
CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)
この SORT_IN_TEMPDB の挙動を少し確認してみたいと思います。
インデックスの作成ですが ALTER TABLE で [ADD CONSTRAINT <プライマリキー> PRIMARY KEY CLUSTERED] を使用してクラスター化インデックスとして設定されたプライマリキーを設定する際に作成することもできますが、この場合には SORT_IN_TEMPDB は使えなかったはずです。
ALTER TABLE (Transact-SQL)
SORT_IN_TEMPDB はメモリ内でソートが行えなかった場合に tempdb でソートを行うためのオプションとなります。
以下のようなサイズのテーブルがあります。 
この中でサイズが大きい [LINEITEM] にインデックスを作成してみたいと思います。
インデックスを作成する際には以下のようなクエリを実行しています。
|
DBCC DROPCLEANBUFFERS SELECT CREATE UNIQUE CLUSTERED INDEX [CIX_LINEITEM] ON [dbo].[LINEITEM] CHECKPOINT SELECT |
インデックスの作成前後に tempd のアクセス状況を確認するためにデータベースのファイル I/O を出力するようにしていますのでインデックスの作成で tempdb が使われたかを確認することができます。
最初に [SORT_IN_TEMPDB=ON] の状態でインデックスを作成してみます。 
SORT_IN_TEMPDB はディスクでソートが必要になった際に tempdb を使用してソート行います。
実行前は [17,555,841,024] だった Bytes Written が実行後は [35,103,670,272] になっていますのでインデックスの差作成時には、[17,547,829,248] (17.5GB) の書き込みが tempdb に対して行われています。
それでは、[SORT_IN_TEMPDB=OFF] でインデックスを作成した場合を見てみます。 
実行前は [35,103,678,464]、実行後は [35,114,049,536] ですので [10,371,072] (10MB) tempdb が使用されたことになります。
SORT_IN_TEMPDB=ON の場合と比較して tempdb の利用率が大幅に減っていますね。
データを格納している DB の書き込み状況に関しては ON の場合は [13,808,132,096] (14GB)、OFF の場合は [31,371,403,264] (31GB) ですので、OFF にすることでテーブルを格納しているデータ領域が多く使用されていることが確認できます。
SORT_IN_TEMPDB を使用するメリットとしては、
- tempdb を高速なストレージに配置している場合、ソートの操作を高速化できる
- データ領域とは別の領域でソートすることにより、作成されたインデックスを連続した領域で配置することができる
というようなことが挙げられます。
シーケンシャルに読み込む場合には連続した領域でデータを配置することが重要になってきますので SORT_IN_TEMPDB を使用してデータを連続した配置にできるようにすることがポイントになることがあります。
それでは、インデックスの再構築の際に SORT_IN_TEMPDB を使用するとどうなるかを見ていきたいと思います。
先ほど使用したクエリの CREATE INDEX の部分を以下のように変更して実行してみます。
|
ALTER INDEX [CIX_LINEITEM] ON [dbo].[LINEITEM] REBUILD PARTITION = ALL |
まずは SORT_IN_TEMPDB を OFF にして実行してみます。 
データを格納しているデータベースの書き込みバイト数が、実行前が [74,119,766,016]、実行後が [60,311,945,216] ですので、[13,807,820,800] の書き込みが行われていることが確認できます。
なお、読み取りに関しては [13,813,088,256] ですので読み取りと書き込みのバイト数はほぼ同じぐらい行われていることが確認できますね。
インデックスの再構築は既存のインデックスを新規の領域に再作成する動作になりますので、
- 既存データを読む
- 新規データとして書き込む
の操作が行われていることがここから確認できますね。
それでは、SORT_IN_TEMPDB を ON にしてインデックスの再構築を実行してみます。 
データを格納しているデータベースの書き込みに関しては [13,807,558,656]、読み取りに関しては [13,814,693,888] 行われていることが確認できます。
それでは tempdb のアクセス数はどうでしょう。
書き込み回数 (Nuber Writes) は上昇していないため、tempdb にデータが書き込まれていないことが確認できます。
インデックスが作成されているデータは既にソートが行われているデータになります。
そのため、ソートは行われないため SORT_IN_TEMPDB の有無による tempdb の利用状況の差は発生していません。
# オプションが使えるので何かの場合には tempdb でソートがされるのかもしれませんが、基本的にはソート済みのデータを再構築するため、tempdb の利用はないかと思いますが。
今回はインデックスの作成や再構築の際に tempdb が使われるかを調べてみましたが、他の操作でも上記のようなクエリを使用することで tempdb がどのくらい使用されたかを確認することができますので、気になるときには汎用的に使えるかと思います。