この特集のトップページへ

「遅いクエリ」の識別

　上記の方法で採取したトレースデータを分析する場合にも，SQL Serverプロファイラを使用する。それ以外には，SQL Serverプロファイラで採取したトレースデータを元に，クエリアナライザを使用して「遅いクエリ」を識別する方法もある。

○SQL Serverプロファイラで「遅いクエリ」を識別する方法
　SQL Serverプロファイラで実行クエリのトレース情報を採集したら，次にSQL Serverプロファイラで「遅いクエリ」を探す。すなわち，処理時間の最もかかっているSQLステートメント，そしてそのSQLステートメントがアクセスしているデータベースとオブジェクトを確認する。SQL Serverプロファイラは，SQL Serverが稼働しているサーバー上でトレースデータを採取する。つまり，クライアントからクエリを受信してから応答を返すまでのイベントを記録している。このため，サーバー側の，実際に問題となっているクエリを明確にすることができる。

　具体的には，次のような手順を踏む。

(1)SQL Serverプロファイラのメニューから［ファイル］−［開く］−［トレースファイル］または［トレーステーブル］を選択し，分析対象となるトレース情報を格納したファイルまたはテーブルを開く。［イベントクラス］列から［Deadlock Chain］など必要なノードを展開する。新しい行を表示するには，SQL Serverプロファイラのメニューから［表示］− [最新］を選択すればよい。

Fig.9-21 トレース表示（クリックして画像を拡大可能）


(2)サーバー名，データベースID，オブジェクトIDからグループ化されているイベントを，［CPU］から最もCPUを消費しているイベントを，［期間］から最も時間のかかっているイベントを，［読み取り数］や［書き込み数］から最もI/O数の多いイベントを，それぞれ探す。単純に遅いクエリを見つけるのであれば，［期間］の長いイベントを探す。その原因を分析するのであれば，［CPU］，あるいは［読み取り数］および［書き込み数］を調べる。SQL Serverプロファイラのメニューから［編集］−［検索］を選択すると表示される［検索］ダイアログボックスを利用すると，トレース情報に含まれる任意の値を簡単に見つけることができる。該当すると思われるイベント情報を把握したら，［接続ID］と［開始時刻］の値を書き留める。

Fig.9-22 ［検索］ダイアログボックス


(3)SQL Serverプロファイラのメニューから［ファイル］−［プロパティ］を選択して［ファイルのプロパティ］ダイアログボックスを表示し，イベントクラスの代わりに［接続ID］をグループ化して関連する利用状況を発生順に表示させる。

Fig.9-23 ファイルのプロパティ（データ別）

(4)表示された各接続IDのなかから，先に2で書き留めたイベントの開始時刻と一致する値を探す。目で追ってもかまわないが，［検索］ダイアログボックスを利用すると間違いもなく楽だろう。［イベントクラス］列には，(3)で選択した接続IDに属するイベントが発生時刻の順番で表示される。接続IDと開始時刻をキーにイベントを探し，アプリケーション名，実行SQLステートメントなどを確認する。クエリが複数のSQLステートメントから構成されている場合は，前後の実行SQLステートメントの実行順序から，どのクエリであるかを識別する。

Fig.9-24 表示されたイベント情報（クリックすると拡大可能）


○クエリアナライザを利用してクエリでトレースデータを検索する方法
　遅いクエリを探すためにトレース情報を目で追いかけるよりも簡単に遅いクエリを見つけ出せる方法を紹介しよう。SQL Serverプロファイラで実行クエリのトレースをSQL Serverのテーブルに採集した場合は，クエリアナライザを使用し，最も多くのリソースを消費している，つまり実行時間の長いクエリを探すことができる。たとえば，SQL Serverプロファイラのトレース情報が格納されているテーブルに対し，次のようなクエリを実行すると，最も時間のかかっているクエリを上位3番目まで取得することができる。この例では，PROFILER_TABLEテーブルに格納されたトレースデータから接続ID（ConnectionID），アプリケーション名（ApplicationName），テキスト（TextData），CPU時間（CPU），読み込み回数（Reads），書き込み回数（Writes），期間（Duration）のデータを抽出し，それを期間値の大きい順でソートして上位3番目までを出力する。テキスト列に実行したSQLステートメントなどが格納されているので，どのステートメントの処理が遅いのかを容易に判断できるであろう。

SELECT TOP 3 ConnectionID, ApplicationName, TextData,
　　　 CPU, Reads, Writes, Duration
　　　 FROM PROFILER_TABLE
　　　 ORDER BY Duration

　あるいは，イベントクラスを条件に指定して，CPUが処理時間の40％以上を占めているイベントを抽出する場合には，次のようなSQLステートメントを実行するのもよいだろう。

SELECT ConnectionID, ApplicationName, TextData, Duration, CPU
　　　 FROM PROFILER_TABLE
　　　 WHERE EventClass = 12＊1
　　　 AND CPU > (.4 * Duration)

　この方法を用いれば，SQL Serverプロファイラを使用してすべてのイベントを分析して「遅いクエリ」を探し出すよりも，同じトレースデータを使用してもっと簡単に「遅いクエリ」を特定することができる。「遅いクエリ」を特定したあと，ほかのイベントや，ほかのクエリとの関係などをさらに詳細に分析する場合には，最初に説明した方法を利用するほうがよいだろう。

○クエリアナライザのODBCトレース機能を利用する方法
　SQL Serverプロファイラを利用する場合は，SQL Serverが稼働しているサーバー側でトレースデータを採取している。これに対して，クライアントからODBC接続でSQL Serverと接続している場合に，クライアント側でODBCレベルのトレースを採取する方法もある。ただしこの場合，採取されたデータにはネットワークとサーバーの両方の原因が含まれているため，さらにSQL Serverプロファイラを使用して分析し，原因を区別してゆくことが必要である。

　クエリアナライザのメニューから［クエリ］−［現在の接続オプション］を選択したときに表示される［現在の接続オプション］ダイアログボックスで［詳細設定］パネルを開き，［ODBCトレースとログ記録］で［実行時間が長いクエリをログファイルに保存する］を選択する。［ファイル名］に保存先のファイル名を，［実行時間が長いクエリ（ミリ秒）］に実行時間の閾値を，それぞれ指定する。デフォルトでは30000ミリ秒（30秒）が設定されているため，実行時間が30秒以上かかっているクエリの情報がファイルに保存される。この値に性能目標値の応答時間を設定し，「遅いクエリ」を識別することになる。

  1. イベントクラスIDが12のイベントはSQL:BatchCompleted（バッチの完了）イベントを示す。12以外にも，10はRPC:Completed（RPCの終了）を，11はRPC:Starting（RPCの起動）を，13はSQL:BatchStarting（Transact-SQLバッチの起動）を，14はConnect（クライアントからSQL Serverに対する接続要求）を，15はDisconnect（クライアントによる切断コマンドの実行など）を，16はAttention（クライアントの割り込み要求や中断されたクライアント接続など）を，18はServiceControl（サーバーの一時停止や再起動など）を，それぞれ表す。
前へ Chapter 9　11/46 次へ