SQLServerで実行計画をリアルタイムにトレースする

SQLServerでクエリの実行計画をリアルタイムにモニタリングする機能の使い方を紹介します。
注意: **SQLServer2016以上じゃないと使えない機能**です

# query_plan_profile

SQLServer2016から有効になった機能で、リアルタイムにクエリの実行プランの進捗を監視する機能です。

**ヒント句**と**拡張イベント**を用いることで**Activity Monitor**から閲覧することができます。

[https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/query-profiling-infrastructure?view=sql-server-2016](https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/query-profiling-infrastructure?view=sql-server-2016)

# query_plan_profileを有効にするための拡張イベントを作成する

New Sessionからイベントを作成します。
<img width="341" alt="スクリーンショット 2019-02-12 11.20.56.png" src="https://qiita-image-store.s3.amazonaws.com/0/13766/0a94e6d7-48d8-3e79-bd00-a794b2a72325.png">

イベント名は何かしらわかるものにしておきます。予期せぬタイミングで自動起動しないように、起動オプションはオフにしています。

<img width="652" alt="スクリーンショット 2019-02-12 11.22.08.png" src="https://qiita-image-store.s3.amazonaws.com/0/13766/668fa47e-bf7e-a325-7f81-0089dba5082d.png">


eventの中からquery_plan_profileを選択します。
<img width="946" alt="スクリーンショット 2019-02-12 11.22.19.png" src="https://qiita-image-store.s3.amazonaws.com/0/13766/e42253dc-5a71-3329-0574-4f7c10b89cd0.png">


Advancedの項目はMultiple event loss にします。これはeventを保存するため割り当てたメモリが不足した際に、どこまでイベントのlossを許容するかを決めるオプションです。実際の運用になるべく影響がない形で実施するのが望ましいため、Multiple event lossにしています。
<img width="600" alt="スクリーンショット 2019-02-12 11.47.34.png" src="https://qiita-image-store.s3.amazonaws.com/0/13766/98c69fe8-ec97-3c91-2cc8-10d65a4a1936.png">


# ヒント句でquery_plan_profileを有効化する

有効にするためには実行するクエリにヒント句をつける必要があります。

有効化したいクエリの末尾に

    option(use hint('query_plan_profile'))

を付ければOKです。

# モニタリング方法

イベントを有効化し、ヒント句付きでクエリが実行される状況になれば利用可能です。

Activity Monitorの画面でActive Expensive Queriesの中にコストが高いクエリが出てくるので、調べたい項目を右クリックして、Show Live Execution Planを選びます。
![スクリーンショット 2019-02-12 11.36.20.png](https://qiita-image-store.s3.amazonaws.com/0/13766/d33f2ba9-0d5e-9c94-fead-54583b0d6d8c.png)


これで実行計画の経過をリアルタイムでトレースすることができます。

処理に長時間かかるようなクエリじゃない限り活躍の機会は少ないですが、集計クエリなどでやたらと時間がかかるクエリが生じたときに有効です。

他にもManagement Studioから直接クエリを実行する時に利用する方法については下記ページで触れられています。
[https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/live-query-statistics?view=sql-server-2017](https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/live-query-statistics?view=sql-server-2017)

また、ライブクエリ統計には内部的に

    sys.dm_exec_query_profiles

が用いられているので、詳しく知りたければここの仕様を見ると良さそうです。
参考: https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/live-query-statistics?view=sql-server-2017

# 見所
一つ見たほうがいいポイントとして<b>推定結果と実際に用いられた結果のずれの大きさ</b>があります。

オプティマイザはクエリ実行時にデータを何行用いるかを推測して、それを元に実行計画を立てます。その推測と実際に利用した結果が異なっていた場合、適切な実行プランにならずパフォーマンスの低下が予想されます。
参考: https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-2017

そのため、予測と実際の値のずれが大きくなり始めるポイントを見つけて、適切に推測できるように対処すると改善される可能性があります(サブクエリで使っていた箇所を抜き出して一時表にするなど)。

[クエリプランの画面]
![スクリーンショット 2019-02-12 11.05.30.png](https://qiita-image-store.s3.amazonaws.com/0/13766/37d85760-8e84-fb0d-d3a3-c87d97531afb.png)

画像中に A of B という表記がありますが
A が 実際の値で B が 推測値です。

13528行の予想だったけど、実際は442051行だった。
という表示になっています。
※*実際の*と言っていますがこれも厳密には事実と一致しないため、ずれることがあります。

あくまで参考値として認識しておいた方が良さそうですが、ボトルネックを探すツールとしては便利だと思います。