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 の操作ができると、改善が必要なクエリを見つけるのにとても役に立ちますので、時間のある時にサンプルを増やしていこうかと。