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

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

New Sessionからイベントを作成します。

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

eventの中からquery_plan_profileを選択します。

Advancedの項目はMultiple event loss にします。これはeventを保存するため割り当てたメモリが不足した際に、どこまでイベントのlossを許容するかを決めるオプションです。実際の運用になるべく影響がない形で実施するのが望ましいため、Multiple event lossにしています。

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

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

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

option(use hint('query_plan_profile'))

を付ければOKです。

モニタリング方法

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

Activity Monitorの画面でActive Expensive Queriesの中にコストが高いクエリが出てくるので、調べたい項目を右クリックして、Show Live Execution Planを選びます。

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

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

他にもManagement Studioから直接クエリを実行する時に利用する方法については下記ページで触れられています。
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

見所

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

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

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

[クエリプランの画面]

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

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

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