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
実際に実行してみると、次のような結果を取得することができます。
この情報があると、どのクエリでスキャンしたかがわかるかと。
上記のクエリですが、プランキャッシュを細かくパースしようとすると、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
この場合、プランキャッシュからステートメントのテキストを抽出し、次のような結果を得ることができます。
こちらは、インデックスの操作の取得の他のパターンになります。
-- 特定のクエリの操作を取得
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
冒頭のクエリでは、インデックスの特定の操作のみを取得していましたが、こちらのクエリでは他の操作についても行形式で取得を行っています。
プランキャッシュの XML の操作ができると、改善が必要なクエリを見つけるのにとても役に立ちますので、時間のある時にサンプルを増やしていこうかと。