【SQL Server】クエリの実行プランとパフォーマンス情報の取得方法Part2
前回に引き続き実行プランとパフォーマンス情報の取得について説明します。今回はトレースとデータ蓄積による情報取得です。
前回記事はこちらを参照して下さい。
memorandom-nishi.hatenablog.jp
3.トレースして取得する
トレースを仕掛けて詳細情報を取得する方法は2つあります。
ただし後述するSQLプロファイラーは非常にトレース負荷が高く本番環境で使用することは皆無かと思います。
使用する場合は、ブラックボックスで内部でどのようなSQLが実行されているかわからないものを確認したり、そのSQL実行開始のコンパイルからロック状況など実行状況を一連の流れで確認するなどに使えますが、基本的には拡張イベントで事足りるでしょう。
拡張イベントもSQLプロファイラーに比べ軽量とはいえ、負荷がかかることに変わりはないため、使用の際は注意してください。
拡張イベント(XEvent)
拡張イベントを設定することで、3種類の実行プランをとることができます。特に下記③query_post_execution_showplanは、前回記事で説明したSET STATISTICS XML相当の実際の実行プランであるため、有益な情報になるかと思います。
設定手順は以下のとおりです。
オブジェクトエクスプローラーの「管理」-「拡張イベント」-「セッション」で右クリックを押下し、「新規セッションウィザード」を選択します。
作成する拡張イベントのセッションに名前を付けます。今回はテンプレートなしで作成します。
取得したい実行プランのイベントを選択します。
選択したイベントでトレース対象をフィルタすることも可能です。
作成が完了したら、オブジェクトエクスプローラーの「管理」-「セッション」に追加されていることを確認します。
パフォーマンス情報によって取得するものを絞る場合は次のように追加でフィルタします。
準備ができたらセッションを開始します。
今回はリアルタイムで監視したいため、「ライブデータの監視」を選択します。
データが採取できたらセッションを停止します。
結果以下のようにパフォーマンス情報と実行プランが取得できていることが確認できます。
①クエリコンパイル後・クエリ実行前の推定の実行プラン(query_post_compilation_showplan)
②クエリコンパイル後・クエリ最適化時(実行時)の推定の実行プラン(query_pre_execution_showplan)
③クエリ実行後の実際の実行プラン(query_post_execution_showplan)
また、拡張イベントで取得できるイベントは、以下で確認できるので一度どのようなものが取得できるかざっと確認しておくとよいでしょう。
SELECT * FROM sys.dm_xe_objects
SQL Server Profiler
SQL Serverプロファイラーではプロファイラーを実行してから終了するまで指定したイベントをトレースできます。
非常に負荷が高いためあまり実用的ではありませんが、開発環境等で詳細情報を取得する場合等に使えないこともありません。
使い方としては以下のとおりです。
SQL Server Profilerを起動します。
認証して、トレースのプロパティを開きます。
「イベントの選択」タブで、「すべてのイベントを表示する」にチェックをいれます。
Events列のPerformanceをクリックします。
取得する実行プランを選びます。
「列フィルター」にてトレースする対象をフィルタすることができます。
実行すると対象のイベントについてトレースを開始します。
以下のようにパフォーマンス情報と実行プランが取得できていることが確認できます。
クエリコンパイル後の推定の実行プラン(Showplan ALL For Query Cimpile、Showplan XML For Query Cimpile)
Showplan ALL For Query Cimpile
Showplan XML For Query Cimpile
クエリコンパイル後、クエリ実行前の推定の実行プラン(Showplan ALL、Showplan TEXT、Showplan XML)
Showplan XML
実際の実行プラン(Showplan Statistics Profile、Showplan XML Statistics Profile)
Showplan Statistics Profile
Showplan XML Statistics Profile
4.データを蓄積して分析する
データを蓄積していくことで変化や傾向を分析する方法は、大きく分けてシステムデータコレクション(MDW)とクエリストアが有用かと思います。
システムデータコレクション(MDW)
システムデータコレクション(以下MDW)については過去記事で確認方法を説明していますので、こちらを参照してください。
MDWでは、クエリの実行プランやパフォーマンス情報に合わせて、当該クエリの待機イベントの状況やブロッキングの情報などが確認できるため、チューニングの際にボトルネックの発見がしやすくなります。OracleのStatspackに似ているかと思います。
memorandom-nishi.hatenablog.jp
クエリストア
こちらも過去記事でざっと説明していますので参照して頂ければと思います。
SQLServer2016からしか使用できませんが、クエリストアを使うことでクエリの急な遅延等のパフォーマンス変化時の分析に役立つかと思います。
memorandom-nishi.hatenablog.jp