系統內存在不必要的INDEX或是INCLUDE欄位太多了想要減肥
如果只是要找出使用率低的INDEX可以使用sys.dm_db_index_usage_stats這DMV來篩選
但如果今天是INDEX內的KEY值甚至是INCLUDE欄位要瘦身 , 好像就沒有專門計算欄位使用次數的動態系統表了吧?

和各位分享透過sys.dm_exec_procedure_stats紀錄sp的DMV找出每筆執行計畫的XML
透過XML的內文 , 找出執行計畫每個節點使用到的INDEX以及輸出欄位 , 把執行計畫表格化可以再做進一步的資料分析 , 進而找出沒有使用到的INCLUDE欄位或是INDEX KEY值。

使用Northwind範例資料庫英文版本
連結 : https://sdwh.dev/posts/2021/12/SQL-Server-Sample-Databases/#

我們先挑一隻sp來爬他的XML , 執行以下範例資料庫內的sp

EXEC CustOrderHist 'ALFKI'

執行以下可以撈出 CustOrderHist 的XML , 後面接著就是把XML的節點資料展開成表格

SELECT 
		DB_NAME(dest.dbid) AS dbName,
		OBJECT_NAME(dest.objectid, dest.dbid) AS spName, 
		deps.execution_count,
		deqp.query_plan AS QueryPlan
FROM sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_sql_text(deps.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp
WHERE OBJECT_NAME(dest.objectid, dest.dbid) = 'CustOrderHist'

這段會比較複雜 , 總共4段逐段解釋

第1段 : 把sp進入快取的XML寫入暫存表 , 切記如果sp有使用到重新編譯則快取都不會被記錄
第2段 : 用while迴圈逐筆爬出每筆XML內文的節點資料 , 從每一個RelOp底下開始找 , 找出/IndexScan/Object以及/OutputList/ColumnReference
, 重點 ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 一定要加上去 , 簡單說明就是要先定義命名空間才能爬這個空間內的節點 , 從舊到新的命名空間基本上都是這一行以維持每個版本的相容性
第3段 : 把爬出來的XML做資料分類 , 等有餘力再了解這段就好 , 重點再第4段
第4段 : 最終結果 , 主要觀察IndexName、OutputColumnName這兩個欄位 , 已經把用到的所有欄位組再一起 , 可以快速的看出該支sp使用到那些INDEX以及INDEX內的哪些欄位

DROP TABLE IF EXISTS #QueryPlan, #result, #FINALTABLE;

--1
SELECT 
		DB_NAME(dest.dbid) AS dbName,
		OBJECT_NAME(dest.objectid, dest.dbid) AS spName, 
		deps.execution_count,
		deqp.query_plan AS QueryPlan,
		ROW_NUMBER()OVER(ORDER BY deps.execution_count DESC) AS rw
INTO #QueryPlan
FROM sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_sql_text(deps.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp
ORDER BY deps.execution_count DESC

CREATE TABLE #result (
	RW INT IDENTITY,
	ObjectName VARCHAR(64),
	Execution_count INT,
	NodeId       INT,
	PhysicalOp   NVARCHAR(128),
	LogicalOp    NVARCHAR(128),
	DatabaseName NVARCHAR(128),
	SchemaName   NVARCHAR(128),
	TableName    NVARCHAR(128),
	IndexName    NVARCHAR(128), 
	ColumnName   NVARCHAR(128)
)

DECLARE @xml XML, @rw INT = 1, @ObjectName VARCHAR(128), @execution_count INT

WHILE (1=1)
BEGIN

	SELECT @xml = QueryPlan, @ObjectName = spName, @execution_count = execution_count
	FROM #QueryPlan
	WHERE rw = @rw

	IF @@ROWCOUNT = 0
	BREAK

	--2
	;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	INSERT INTO #result (ObjectName, Execution_count, NodeId, PhysicalOp, LogicalOp, DatabaseName, SchemaName, TableName, IndexName, ColumnName)
	SELECT
		@ObjectName,
		@execution_count,
		R.value('@NodeId', 'INT') AS NodeId,
		R.value('@PhysicalOp', 'NVARCHAR(128)') AS PhysicalOp,
		R.value('@LogicalOp', 'NVARCHAR(128)') AS LogicalOp,
		O.value('@Database', 'NVARCHAR(128)') AS DatabaseName,
		O.value('@Schema', 'NVARCHAR(128)') AS SchemaName,
		O.value('@Table', 'NVARCHAR(128)') AS TableName,
		O.value('@Index', 'NVARCHAR(128)') AS IndexName,
		C.value('@Column', 'NVARCHAR(128)') AS ColumnName
	FROM @xml.nodes('//RelOp') AS X(R)
	OUTER APPLY R.nodes('./IndexScan/Object') AS OBJ(O)
	OUTER APPLY R.nodes('./OutputList/ColumnReference') AS COL(C)
	
	SET @rw += 1
END

;WITH DATARANK AS (
	SELECT RW, CASE WHEN NodeId < LAG(NodeId, 1, 0)OVER(ORDER BY RW) THEN 1 WHEN RW = 1 THEN 1 ELSE 0 END AS [GroupStart]
	FROM #result
)

--3
SELECT R.ObjectName, R.Execution_count, R.NodeId, R.PhysicalOp, R.LogicalOp, R.DatabaseName, R.SchemaName, R.TableName, R.IndexName, R.ColumnName
		, SUM(GroupStart)OVER(ORDER BY D.RW ROWS UNBOUNDED PRECEDING) AS GroupID
INTO #FINALTABLE
FROM DATARANK D
JOIN #result R ON D.RW = R.RW

--4
SELECT ObjectName, NodeId, PhysicalOp, LogicalOp, DatabaseName, SchemaName, TableName, IndexName,
		 '['+STRING_AGG(ColumnName, '], [')+']' [OutputColumnName], Execution_count
FROM #FINALTABLE
WHERE 1 = 1
	AND ObjectName = 'CustOrderHist'
GROUP BY GroupID, ObjectName, Execution_count, NodeId, PhysicalOp, LogicalOp, DatabaseName, SchemaName, TableName, IndexName

結論 : 建立INDEX很快 , 但如果要移除INDEX內的欄位確實是要花一點功夫 , 針對頻繁使用的sp用這個方法來分析真心覺得超快 , 一眼就可以看出哪些欄位需要留哪些不需要 , 如果哪邊有錯誤請直接指正謝謝~