SE の雑記

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

クエリ内での計算と CPU 利用について

leave a comment

近年、SSD や PCI-e 接続の RAM ディスクによりディスク I/O の性能が飛躍的に向上してきています。
この先、ディスクネックが少なくなってくると、私はどうやってご飯食べていこうかと戦々恐々としているのですが、今回の投稿ではストレージエンジンではなく、CPU 関連について少し書いてみたいと思います。

がりがり開発している人にとっては普通に認識されていることなのかなと思いますが、私はクエリ系あまり勉強していないもので。。。

■クエリ実行時の CPU 利用について


クエリを実行する場合の CPU 使用率の代表としてはコンパイルがあるかと思います。
初回のクエリ実行や統計情報、スキーマ定義の変更によりリコンパイルの必要性が発生した場合は実行プランを再生成するためにクエリをコンパイルするために CPU が使用されます。

文字列結合により作成された再利用される可能性の低いクエリが実行されるアドホッククエリの利用が推奨されないのも、クエリのコンパイル頻度を抑え CPU の利用を抑える目的があります。
# プランキャッシュの使用率を抑えるのもありますが。

CPU の使用率を上げる要因としては集計や計算も考えられます。

たとえば、以下のようなクエリを 8 スレッドで実行してみます。

DECLARE @i int
DECLARE @j int = 0
WHILE (@j < 5000000)
BEGIN
    SELECT @i = Col1 * 2 FROM Table_1
    SET @j += 1
END

このクエリを実行する前に、Table_1 の全件のデータはメモリ上にロードしてあり、ディスク I/O は発生しない状態にしています。
なお、Table_1 の定義は以下のようになっています。
image
また、クエリは過去に実行した状態としプランキャッシュにクエリのキャッシュが行われています。

このクエリを実行している最中の CPU の使用率がこちらになります。
image

クエリの実行中は CPU の使用率が張り付いているのが確認できます。

SQL Server のデータキャッシュはテーブルの実データがキャッシュされますので、クエリ内で計算しているものに関してはその結果は格納されず、結果を取得するために計算が行われます
これが CPU 使用率に表れてきているということですね。

JOIN も同じで、JOIN 後のデータが格納されるのではなく JOIN で使用している各テーブルのデータがテーブル単位で格納されますので、クエリ実行時に結合が必要になります。
結合も行のマッチングをする必要がありますので CPU が使われてきます。ハッシュ結合の場合はハッシュ値を求める必要がありますのでここでも CPU が使用されるかと。
# インデックス付きビューを使うと違ってくるはずですが。

SQL Server では計算列を作成することで、特定の計算式の結果を返す列を定義することができます。
image

上記のテーブル構造を使用して以下のようなクエリを実行してみます。
先ほどはクエリ内で計算をしていましたが、今回は計算列を使用していますのでクエリ内では計算は行わずに計算式を設定している計算列を取得するようにしています。

DECLARE @i int
DECLARE @j int = 0
WHILE (@j < 5000000)
BEGIN
    SELECT @i = Col3 FROM Table_2
    SET @j += 1
END

この時の CPU 使用率がこちらになります。

image

計算列のデフォルトの設定は [永続化をしない] 設定となっています。
これは、テーブルの定義として計算列の設定を持つが、データの実体としては計算結果を保持しないという設定になります。
Col3 として計算列を定義していますが、永続化しない場合はデータとしては格納されていないことが確認できます。
image
つまり、このパターンもクエリを実行するたびに結果を計算しているということになります。

永続化をするとこのようにデータが格納されます。
Col3 として計算結果が格納されています。
image

この場合、計算結果がテーブルに格納されているため、クエリ実行時に計算をする必要がなくなりますので、先ほどと比較して多少使用率が下がっているかと思います。
image

このように、永続化をすることで計算結果がテーブル内に格納されますのでディスク I/O が増えますが  CPU 使用率は下がってきます。
# ぐるぐるループさせているので効果がわかりづらくあまりいい例ではないですが。

このあたりの差は SSMS のクエリ統計でも確認することができます。
左から、計算列なし/永続化をしない計算列/永続化した計算列の結果になりますが、処理時間に差が出ていることが確認できます。
image

AP サーバーの CPU 使用率のよっては、AP 側で計算するまたは、計算結果を含む結果をキャッシュする等の対応をして、DB サーバーの CPU 使用率を下げるという方法もとることができます。
# AP サーバーはスケールアウトが柔軟にできると思いますので。JOIN 禁止などの考えも基本はどちらの CPU を使うか、結合結果をどのようにキャッシュするかなのでしょうね。

クエリの CPU 使用率の調査に対しては sys.dm_exec_query_stats の total_worker_time と execution_count から使用頻度が多く、CPU 使用率が高いものを調査して、その実行プランから CPU 使用率の高いイテレーターを見つけ、対応していくというアプローチが一般的だと思います。
# 頻度が低く CPU 使用率が高いものへの対応も無駄ではありませんが、全体的にみると効果が薄いかもしれませんので。

この辺は日々勉強して自分なりの調査方法や解決策をまとめていかないとな~と痛感しています。

Written by masayuki.ozawa

9月 25th, 2012 at 9:04 am

Posted in SQL Server

Tagged with

Leave a Reply

*