[SQL] 遺漏索引建議

Turning 平行處理時發現前兩個 TSQL 執行次數特別高，特別抓出來處理

打開 xml missing index 發現特別之處，TSQL 語法內的欄位資訊以 missing index 內的 ColumnID 來示意

SELECT
    M.ColumnId5
   ,M.ColumnId11
   ,M.ColumnId12
   ,M.ColumnId27
FROM TableName AS M
	JOIN 
		(
			SELECT
				ColumnId5 ,
				MAX(ColumnId29) AS ColumnId29
			FROM TableName
			WHERE ColumnId5 IN ('資料1', '資料2', '資料3', '資料4')
				AND ColumnId23 > 0
			GROUP BY ColumnId5
		) AS T　ON M.ColumnId5 = T.ColumnId5
		AND M.ColumnId29 = T.ColumnId29
WHERE M.ColumnId23 > 0

重點欄位在於 where 條件

WHERE ColumnId5 IN ('資料1', '資料2', '資料3', '資料4')
    AND ColumnId23 > 0

missing index 建議

在舊文章 - Using Missing Index Information to Write CREATE INDEX Statements 內有條列這四點說明

List the equality columns first (leftmost in the column list). 

List the inequality columns after the equality columns (to the right of equality columns listed).

List the include columns in the INCLUDE clause of the CREATE INDEX statement. 

To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first.

在新文章 - Tune nonclustered indexes with missing index suggestions 內則是改為文字說明

Review the missing index recommendations for a table as a group, along with the definitions of existing indexes on the table. Remember that when defining indexes, generally equality columns should be put before the inequality columns, and together they should form the key of the index. To determine an effective order for the equality columns, order them based on their selectivity: list the most selective columns first (leftmost in the column list). Unique columns are most selective, while columns with many repeating values are less selective.

Included columns should be added to the CREATE INDEX statement using the INCLUDE clause. The order of included columns doesn't affect query performance. Therefore, when combining indexes, included columns may be combined without worrying about order

打開 XML 執行計畫找到最佳建議，基本上有依循上述原則建議，先是建議 equality 欄位後，才是 inequality 欄位

但該 case 的 ColumnId5 在商業邏輯上類似分類資料，ColumnId23 類似未結案資料，ColumnId23 欄位是比較 selective，確認另外兩個建議都是以 ColumnId5 為主，建議完全沒有幫助，最後單純建立 ColumnId23 Index 來消除平行處理

CREATE INDEX IX_TableName ON TableName( ColumnId5 )

這兩個 case 重點 table 剛好都是同一個，建立一支 index 解決兩個 case 的平行處理，該 case 效能前後比較

	改善前	改善後
Logical Read	11,766	2,098