拡張イベントを使用してシンプルな SELECT 文で見るクエリの実行にかかわる時間 at SE の雑記

DB Online で「待機時間」に向き合うパフォーマンスチューニングという記事が公開されました。
絵で見てわかるSQL Serverの内部構造を書かれている平山さんの記事でとても興味深く勉強になる内容です。
あと、Deep Dive って文字を見るとなんだかグッときますね。

とういうことで今日は単純な SELECT 文を実行した際のクエリの実行にかかわる時間を拡張イベントで取得するための方法について少しまとめてみたいと思います。

SQL Server 2012 では拡張イベントの使い勝手が大幅に強化されており、SSMS (SQL Server Management Studio) でウィザード形式で使用することができるようになっています。

今回は以下のイベントを取得する拡張イベントを設定してみました。

イベントとしては、

を取得しています。
これらのイベントに対して特定のセッション ID でフィルタリングするように設定をしています。

クエリを実行する際、データベースや行に対してロックが取得されます。
また、クエリがプランキャッシュ上に存在しない場合はコンパイルが発生します。
データがメモリ上に存在しない場合は、ディスクからのデータ読み取りが発生しますのでここで待ち事象が発生します。

厳密にはまだいろいろな要因が存在するとは思いますが、このような事象が SELECT をする際にはかかわってくるのではないでしょうか。

この拡張イベントを設定した状態で以下のクエリを実行してみます。

キャッシュをクリアしてデータを取得するというシンプルな SELECT 文ですね。
この時の拡張イベントで取得した情報がこちらです。

今回はコンパイルが発生するようにしていますので、

というような流れで処理が行われているのが確認できます。
それぞれの事象について待ち時間 (duration) を取得することができます。
今回はコンパイルのための待ち時間が多くかかっているようですね。
# 単純なクエリですので CPU 時間 (cpu_time) はほぼ無いようですが。

今回はデータのキャッシュもクリアしていますので、メモリ上にデータは存在していない状態にしています。
そのため PAGEIOLATCH_SH でも待ちが発生していますね。
# この待ちの内容については DB Online の記事で紹介されています。

今回のクエリは一つのセッションから実行している状態となります。
実際のアプリでは複数のセッションから検索だけでなく、更新系の処理も行われます。

このようなトランザクションミックスの状態になった場合は

などが発生することになります。

ロックやラッチはレコードやページの情報の整合性を保つために必要となりますし、CPU / メモリ / ネットワークは全セッションの共有リソースですので不足した場合は全体的に影響が発生してきます。

そのため、全体的な状況を把握し、各クエリでどのような待ちが発生するかを組み合わせることが重要になってきますよね。

時間があったら更新系についてもまとめてみたいと思います。

SE の雑記