SE の雑記

SQL Server で ORDER BY を使用してソートを行った場合、一時結果セットを作成するために tempdb が使用されることがあります。

今回の投稿ではこの動作についてみていきたいと思います。

■ソートと tempdb の利用について

今回は以下のようなテーブルを作成しています。

Col1 にクラスタ化インデックスを作成しています。

このテーブルには 5MB 程度のデータが格納されています。

このデータを使用してソート字の処理を確認してみたいと思います。

最初はクラスター化インデックスを使用した (ソート順もクラスター化インデックスのソート順と同じ) を実行してみます。

インデックスは特定の列によってソートされた状態でデータが格納されますので結果セットがインデックスの列だけで賄え、ソートする必要がない場合は、全件取得はソートを行わない単純なデータ取得となります。

この際に、fn_virtualfilestas のデータを実行前後で取得してみました。

データベース ID = 2 は  tempdb になるのですが、Read / Write 共に増加していない = tempdb に I/O が行われていないことが確認できます。

次に、ソートをクラスター化インデックスに含まれていないデータに対して実行してみます。

先ほどは並列クエリで実行されてはいなかったので、今回のクエリは同じ CPU の使用状況にするため、並列度を制限しています。

先ほどとは異なり実行前後で tempdb の Read / Write が増加しているのが確認できます。
それでは、実行プランを確認してみます。

今回はインデックスに含まれていない列を使用してソートしていますので、ソートのプランが含まれているのが確認できます。
Sort ですが警告マークがついています。

プランのアイコンにカーソルを合わせると以下のような情報が表示されます。

ソート時に tempdb が使用されたことがプランから確認することができます。
今回、ソートに使用しているデータは 5MB 程度の少量のデータです。
使用している SQL Server には 16GB のメモリを搭載しているため、データはメモリ上に余裕をもって格納でき、ソート時に使用される Workspace Memory も十分に確保できる状態です。

この状態でもプランによっては tempdb が使用されることがあります。

先ほどのクエリを並列度を制限しないで実行した場合はこのようになります。

警告は表示されておらず、tempdb は使用されていない状態となっています。
並列クエリで実行した場合は tempdb が使用されないかというとそういうこともなく、データのサイズと並列度の次数によっては tempdb が使用されます。

今回は SQL Server 2012 の SSMS を使用しているのですが、2008 R2 までの SSMS でも警告マークは表示されていました。

ただし、アイコンの詳細を表示した際に、tempdb が使用されていることを示すメッセージは表示されていなかったようです。

ソート時やハッシュ時には tempdb が使用されることがありますので、メモリで処理がすべて完結していると思わずに、プランを見て使用されていないかを確認するのは重要になって来るかと思います。

SQL Server 2012 ではクラスターで tempdb にローカルのディスクを使用することができます。
サーバーにディスクスロットや PCI スロットに接続ができる SSD を搭載して、tempdb を配置することで、物理構成面で最適化ができるようになりますので、tempdb 負荷が高いクラスターの環境では tempdb をローカルの高速なディスクに配置するチューニングができるかと思います。

単純な動作でも調べてみると、意識していなかったことがいろいろと見えてきます。
DB は奥が深いですね～。