SE の雑記

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

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

leave a comment

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

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

■拡張イベントを使用して SELECT 文の実行にかかわる時間を取得


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

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

イベントとしては、

  • ロックの要求
  • ロックの解放
  • クエリのコンパイル
  • クエリのリコンパイル
  • 待ち事象

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

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

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

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

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

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

  1. コンパイルをするためのロックの取得
  2. クエリのコンパイル
  3. ロックの解放
  4. データを取得するためのロックの取得
  5. ディスクからのデータ読み取り
  6. ロックの解放

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

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

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

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

  • ロックの競合
  • ラッチの競合
  • メモリの圧迫
  • CPU の高負荷
  • ネットワーク帯域の圧迫

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

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

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

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

Share

Written by Masayuki.Ozawa

7月 10th, 2012 at 8:42 am

Posted in SQL Server

Tagged with

Leave a Reply