Ignite のセッションを見ていたところ、クエリストアの活用方法がいろいろと紹介されていて、実践的な方法がかなり勉強になりました。
SQL Server 2016 にアップグレードした際に、
- クエリストアを有効にする
- アップブレード前の互換性レベルで、テスト用のワークロードを実行
- 互換性レベル 130 に変更
- 再度、テスト用のワークロードを実行
- 実行効率が低下したクエリを確認し、必要に応じて、プランの強制を実施
という手順を行い、アップグレード後のクエリの実行効率の低下を防止するという手法についてはなるほどと思いました。
クエリストアでは、実行効率が低下したクエリや、リソースの消費量の高いクエリについては、SSMS から取得することができます。
特定のクエリの状態を取得したい場合は「追跡したクエリ」を使用することになるかと思いますが、この取得では「クエリ ID」を指定して情報の取得を行う必要があります。
特定のクエリについての情報を見たい場合のクエリ ID の特定について、少し考えてみました。
クエリの特定を実施する場合、sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL) を使用するというのが一つの手法としてあるかと思います。
この関数では、SQL ステートメントのテキストから、 SQL ハンドルを取得することができます。
取得した情報を sys.query_store_query (Transact-SQL) から取得することで、該当の SQL のクエリ ID を取得し、追跡を行うことができるのですが、複雑なアドホックなクエリですと、ハンドルの取得が難しいことがありそうでした。
該当のクエリの推定実行プランや、キャッシュされているプランの表示ができるのであれば、実行プラン側の「QueryHas」や「StatementSqlHandle」を使用するのが簡単かもしれないですね。
UTC の日付の考慮をしていないので適当なクエリになってしまっていますが、ハッシュを使用した場合は、以下のようなクエリでしょうか。
DECLARE @query_hash binary(8) = 0xB4A24E1213D92EE3 SELECT i.start_time, i.end_time, q.query_id , q.query_text_id, q.initial_compile_start_time, q.last_compile_start_time, q.last_execution_time, p.compatibility_level, t.query_sql_text, CAST(p.query_plan AS xml) AS query_plan, r.* FROM sys.query_store_query q INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id INNER JOIN sys.query_store_query_text t ON t.query_text_id = q.query_text_id INNER JOIN sys.query_store_runtime_stats r ON r.plan_id =p.plan_id INNER JOIN sys.query_store_runtime_stats_interval i ON i.runtime_stats_interval_id = r.runtime_stats_interval_id WHERE q.query_hash = @query_hash AND i.start_time BETWEEN (DATEADD(dd, -7, GETDATE())) AND DATEADD(hh, 9, GETDATE()) ORDER BY i.start_time,q.query_id
類似なアドホックなクエリがいくつも実行されている場合は、さらに絞り込みを考える必要がありますが、クエリストアの取り込みモードを「自動」にしている場合、実行頻度の低いクエリに関しては取得を防止することができますので、ある程度は間引かれた状態になっているかと。
今回のケースであれば、このハッシュ値を持つクエリは、クエリ ID 27 または、89 ということが確認できますので、あとはこのクエリを追跡すればよいかと。
# 上記の結果の場合、MAXDOP = 1 で実行したものが ID 89、それ以外 (並列化されている) の場合は、ID 27 として記録されています。
runtime_stats 系の DMV を外せば、クエリ ID のみを追跡できるかと思います。