SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

クエリストアから特定のクエリの情報を取得する

leave a comment

imageIgnite のセッションを見ていたところ、クエリストアの活用方法がいろいろと紹介されていて、実践的な方法がかなり勉強になりました。

SQL Server 2016 にアップグレードした際に、

  • クエリストアを有効にする
  • アップブレード前の互換性レベルで、テスト用のワークロードを実行
  • 互換性レベル 130 に変更
  • 再度、テスト用のワークロードを実行
  • 実行効率が低下したクエリを確認し、必要に応じて、プランの強制を実施

という手順を行い、アップグレード後のクエリの実行効率の低下を防止するという手法についてはなるほどと思いました。

クエリストアでは、実行効率が低下したクエリや、リソースの消費量の高いクエリについては、SSMS から取得することができます。

image

特定のクエリの状態を取得したい場合は「追跡したクエリ」を使用することになるかと思いますが、この取得では「クエリ ID」を指定して情報の取得を行う必要があります。

image

特定のクエリについての情報を見たい場合のクエリ ID の特定について、少し考えてみました。

クエリの特定を実施する場合、sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL) を使用するというのが一つの手法としてあるかと思います。

この関数では、SQL ステートメントのテキストから、 SQL  ハンドルを取得することができます。
取得した情報を sys.query_store_query (Transact-SQL) から取得することで、該当の SQL のクエリ ID を取得し、追跡を行うことができるのですが、複雑なアドホックなクエリですと、ハンドルの取得が難しいことがありそうでした。

該当のクエリの推定実行プランや、キャッシュされているプランの表示ができるのであれば、実行プラン側の「QueryHas」や「StatementSqlHandle」を使用するのが簡単かもしれないですね。
image

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

類似なアドホックなクエリがいくつも実行されている場合は、さらに絞り込みを考える必要がありますが、クエリストアの取り込みモードを「自動」にしている場合、実行頻度の低いクエリに関しては取得を防止することができますので、ある程度は間引かれた状態になっているかと。

image

今回のケースであれば、このハッシュ値を持つクエリは、クエリ ID 27 または、89 ということが確認できますので、あとはこのクエリを追跡すればよいかと。

# 上記の結果の場合、MAXDOP = 1 で実行したものが ID 89、それ以外 (並列化されている) の場合は、ID 27 として記録されています。

runtime_stats 系の DMV を外せば、クエリ ID のみを追跡できるかと思います。

Share

Written by Masayuki.Ozawa

10月 10th, 2016 at 12:06 am

Leave a Reply