SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

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

one comment

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

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

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


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

image

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

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

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

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

image

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Share

Written by Masayuki.Ozawa

2月 21st, 2012 at 8:05 am

Posted in SQL Server

Tagged with

One Response to 'ソートと tempdb の利用について'

Subscribe to comments with RSS or TrackBack to 'ソートと tempdb の利用について'.

  1. ソートと tempdb の利用について ≪ SE の雑記…
    素敵なエントリーの登録ありがとうございます – .NET Clipsからのトラックバック…

    .NET Clips

    21 2月 12 at 12:39

Leave a Reply