小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

【SQL Server】クエリの実行プランとパフォーマンス情報の取得方法Part2

前回に引き続き実行プランとパフォーマンス情報の取得について説明します。今回はトレースとデータ蓄積による情報取得です。
前回記事はこちらを参照して下さい。
memorandom-nishi.hatenablog.jp

3.トレースして取得する

トレースを仕掛けて詳細情報を取得する方法は2つあります。
ただし後述するSQLプロファイラーは非常にトレース負荷が高く本番環境で使用することは皆無かと思います。
使用する場合は、ブラックボックスで内部でどのようなSQLが実行されているかわからないものを確認したり、そのSQL実行開始のコンパイルからロック状況など実行状況を一連の流れで確認するなどに使えますが、基本的には拡張イベントで事足りるでしょう。
拡張イベントもSQLプロファイラーに比べ軽量とはいえ、負荷がかかることに変わりはないため、使用の際は注意してください。

拡張イベント(XEvent)

拡張イベントを設定することで、3種類の実行プランをとることができます。特に下記③query_post_execution_showplanは、前回記事で説明したSET STATISTICS XML相当の実際の実行プランであるため、有益な情報になるかと思います。

設定手順は以下のとおりです。

オブジェクトエクスプローラーの「管理」-「拡張イベント」-「セッション」で右クリックを押下し、「新規セッションウィザード」を選択します。
f:id:utiowa:20170321011932p:plain

作成する拡張イベントのセッションに名前を付けます。今回はテンプレートなしで作成します。
f:id:utiowa:20170321012135p:plain
f:id:utiowa:20170321012138p:plain

取得したい実行プランのイベントを選択します。
f:id:utiowa:20170321012231p:plain
f:id:utiowa:20170321012317p:plain

選択したイベントでトレース対象をフィルタすることも可能です。
f:id:utiowa:20170321012410p:plain

作成が完了したら、オブジェクトエクスプローラーの「管理」-「セッション」に追加されていることを確認します。
f:id:utiowa:20170321012525p:plain

パフォーマンス情報によって取得するものを絞る場合は次のように追加でフィルタします。
f:id:utiowa:20170321012637p:plain
f:id:utiowa:20170321012643p:plain
f:id:utiowa:20170321012629p:plain
f:id:utiowa:20170321012632p:plain


準備ができたらセッションを開始します。
f:id:utiowa:20170321012714p:plain

今回はリアルタイムで監視したいため、「ライブデータの監視」を選択します。
f:id:utiowa:20170321012831p:plain

データが採取できたらセッションを停止します。
f:id:utiowa:20170321012925p:plain

結果以下のようにパフォーマンス情報と実行プランが取得できていることが確認できます。
f:id:utiowa:20170321013001p:plain


①クエリコンパイル後・クエリ実行前の推定の実行プラン(query_post_compilation_showplan)
f:id:utiowa:20170321013055p:plain

②クエリコンパイル後・クエリ最適化時(実行時)の推定の実行プラン(query_pre_execution_showplan)
f:id:utiowa:20170321013104p:plain

③クエリ実行後の実際の実行プラン(query_post_execution_showplan)
f:id:utiowa:20170321013113p:plain
f:id:utiowa:20170321013118p:plain


また、拡張イベントで取得できるイベントは、以下で確認できるので一度どのようなものが取得できるかざっと確認しておくとよいでしょう。

SELECT * FROM sys.dm_xe_objects
SQL Server Profiler

SQL Serverプロファイラーではプロファイラーを実行してから終了するまで指定したイベントをトレースできます。
非常に負荷が高いためあまり実用的ではありませんが、開発環境等で詳細情報を取得する場合等に使えないこともありません。

使い方としては以下のとおりです。

SQL Server Profilerを起動します。
f:id:utiowa:20170321013357p:plain

認証して、トレースのプロパティを開きます。
f:id:utiowa:20170321014853p:plain

「イベントの選択」タブで、「すべてのイベントを表示する」にチェックをいれます。
f:id:utiowa:20170321014914p:plain
f:id:utiowa:20170321014918p:plain


Events列のPerformanceをクリックします。
f:id:utiowa:20170321014935p:plain


取得する実行プランを選びます。
f:id:utiowa:20170321014945p:plain

「列フィルター」にてトレースする対象をフィルタすることができます。
f:id:utiowa:20170321014955p:plain
f:id:utiowa:20170321014956p:plain


実行すると対象のイベントについてトレースを開始します。
f:id:utiowa:20170321015016p:plain

以下のようにパフォーマンス情報と実行プランが取得できていることが確認できます。

クエリコンパイル後の推定の実行プラン(Showplan ALL For Query Cimpile、Showplan XML For Query Cimpile)
Showplan ALL For Query Cimpile
f:id:utiowa:20170321015031p:plain

Showplan XML For Query Cimpile
f:id:utiowa:20170321015042p:plain

クエリコンパイル後、クエリ実行前の推定の実行プラン(Showplan ALL、Showplan TEXT、Showplan XML
Showplan XML
f:id:utiowa:20170321015052p:plain

実際の実行プラン(Showplan Statistics Profile、Showplan XML Statistics Profile)

Showplan Statistics Profile
f:id:utiowa:20170321015104p:plain

Showplan XML Statistics Profile
f:id:utiowa:20170321015116p:plain

4.データを蓄積して分析する

データを蓄積していくことで変化や傾向を分析する方法は、大きく分けてシステムデータコレクション(MDW)とクエリストアが有用かと思います。

システムデータコレクション(MDW)

システムデータコレクション(以下MDW)については過去記事で確認方法を説明していますので、こちらを参照してください。
MDWでは、クエリの実行プランやパフォーマンス情報に合わせて、当該クエリの待機イベントの状況やブロッキングの情報などが確認できるため、チューニングの際にボトルネックの発見がしやすくなります。OracleのStatspackに似ているかと思います。
memorandom-nishi.hatenablog.jp

クエリストア

こちらも過去記事でざっと説明していますので参照して頂ければと思います。
SQLServer2016からしか使用できませんが、クエリストアを使うことでクエリの急な遅延等のパフォーマンス変化時の分析に役立つかと思います。
memorandom-nishi.hatenablog.jp