SE の雑記

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

プランキャッシュから特定のインデックスをスキャンしているクエリを調べる

leave a comment

XML をどのようにパースすればいいかなと思って、勉強がてら書いたものなので、大量のプランキャッシュ (クエリキャッシュ) が存在している場合の動作確認まではできていませんが、サンプルとして。
(現状のクエリだと、大量のキャッシュがある状態だとかなり時間がかかります…。)

 

次のようなクエリで、プランキャッシュから特定のインデックスを全件スキャンしたクエリを検索することができます。

-- 実行プランを XML インデックスで検索するため、物理テーブルに格納
-- 検索性能が遅くてもよいのであれば、検索クエリの FROM 句に指定することも可能
DROP TABLE IF EXISTS #tmp
GO
SELECT
	ROW_NUMBER() OVER(ORDER BY query_hash ASC) AS No,
	query_hash,
	query_plan_hash,
	text,
	query_plan
INTO #tmp
FROM sys.dm_exec_query_stats
OUTER APPLY
	sys.dm_exec_query_plan(plan_handle)
OUTER APPLY
	sys.dm_exec_sql_text(sql_handle)
GO
ALTER TABLE #tmp ALTER COLUMN  No int NOT NULL
GO
ALTER TABLE #tmp ADD CONSTRAINT PK_Tmp_Query PRIMARY KEY CLUSTERED(No)
GO
CREATE PRIMARY XML INDEX PIdx_Tmp_Query ON #tmp(query_plan)
GO
-- 実行プランから特定のテーブルの全件検索を実施しているクエリを取得
DECLARE @TableName sysname = QUOTENAME('LINEITEM')
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' )
SELECT
	query_hash,
	query_plan_hash,
	query_plan,
	T2.Stmt.value('fn:local-name(.)', 'varchar(200)') AS local_name,
	CAST(T2.Stmt.query('data(parent::*/parent::RelOp/@PhysicalOp)') AS varchar(255)) AS PhysicalOp,
	CAST(T2.Stmt.query('data(./@Database)') AS varchar(255)) AS Database_Name,
	CAST(T2.Stmt.query('data(./@Table)') AS varchar(255)) AS Table_Name,
	CAST(T2.Stmt.query('data(./@Index)') AS varchar(255)) AS Index_Name
FROM
(
	SELECT * FROM #tmp
) AS T
	CROSS APPLY query_plan.nodes('//Object') AS T2(Stmt)
WHERE
	(
		Stmt.exist('parent::*/parent::RelOp[@PhysicalOp = "Index Scan"]') > 0
		OR
		Stmt.exist('parent::*/parent::RelOp[@PhysicalOp = "Clustered Index Scan"]') > 0
		OR
		Stmt.exist('parent::*/parent::RelOp[@PhysicalOp = "Table Scan"]') > 0
	) AND CAST(T2.Stmt.query('data(./@Table)') AS varchar(255)) = @TableName

 
実際に実行してみると、次のような結果を取得することができます。
image
この情報があると、どのクエリでスキャンしたかがわかるかと。

上記のクエリですが、プランキャッシュを細かくパースしようとすると、XML インデックスがないと検索の性能があまりくないので一度、一時テーブルに入れてから操作するようにしています。

(プランキャッシュの XML はインデックスが設定されていないため、取得項目が増えるとレスポンスが結構厳しいのですよね…。)
プランキャッシュの情報を使用すると次のようなこともできます。

-- 特定のクエリのステートメントを取得
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' )
SELECT
	T2.Stmt.query('data(./@StatementId)') AS StatementId,
	T2.Stmt.value('fn:local-name(.)', 'varchar(200)') AS local_name,
	T2.Stmt.query('data(./@StatementText)') AS StatementText,
	T2.Stmt.query('data(./QueryPlan/@CachedPlanSize)') AS CachedPlanSize,
	T2.Stmt.query('data(./QueryPlan/@CompileTime)') AS CompileTime,
	T.query_plan.value('(/ShowPlanXML/@Build)[1]', 'varchar(18)') AS Build,
	T.query_hash,
	T.query_plan_hash
FROM
(
	SELECT TOP 1
		query_hash,
		query_plan_hash,
		text,
		query_plan
	FROM sys.dm_exec_query_stats
	OUTER APPLY
		sys.dm_exec_query_plan(plan_handle)
	OUTER APPLY
		sys.dm_exec_sql_text(sql_handle)
) AS T
CROSS APPLY query_plan.nodes('//StmtSimple') AS T2(Stmt);
GO

この場合、プランキャッシュからステートメントのテキストを抽出し、次のような結果を得ることができます。
image
こちらは、インデックスの操作の取得の他のパターンになります。

-- 特定のクエリの操作を取得
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' )
SELECT
	T2.Stmt.query('data(./@NodeId)') AS NodeId,
	T2.Stmt.value('fn:local-name(.)', 'varchar(200)') AS local_name,
	T2.Stmt.query('data(./@PhysicalOp)') AS PhysicalOp,
	T2.Stmt.query('data(./@LogicalOp)') AS LogicalOp,
	T2.Stmt.query('data(./child::*/Object/@Table)') AS Table_Name,
	T2.Stmt.query('data(./child::*/Object/@Index)') AS Index_Name,
	T2.Stmt.query('data(./@EstimatedTotalSubtreeCost)') AS EstimatedTotalSubtreeCost,
	T2.Stmt.query('.')
FROM
(
	SELECT TOP 1
		query_hash,
		query_plan_hash,
		text,
		query_plan
	FROM sys.dm_exec_query_stats
	OUTER APPLY
		sys.dm_exec_query_plan(plan_handle)
	OUTER APPLY
		sys.dm_exec_sql_text(sql_handle)
) AS T
CROSS APPLY query_plan.nodes('//RelOp') AS T2(Stmt);
GO

冒頭のクエリでは、インデックスの特定の操作のみを取得していましたが、こちらのクエリでは他の操作についても行形式で取得を行っています。
image
プランキャッシュの XML の操作ができると、改善が必要なクエリを見つけるのにとても役に立ちますので、時間のある時にサンプルを増やしていこうかと。

Share

Written by Masayuki.Ozawa

11月 4th, 2018 at 10:26 pm

Posted in SQL Server

Tagged with

Leave a Reply