德瑞克：SQL Server 學習筆記: [SQL Server] Look at Execution Plan and index: use Index Hint, WITH(FORCESEEK), Missing Index

若想開發出高效能的 SQL 陳述式，就必須瞭解 Execution Plan，並善用之。

「Execution Plan(執行計畫)」

Execution Plan 是 SQL Server Query Optimizer 選取資料的擷取方式。

在 SQL Server 中執行任何 SQL 陳述式時，關聯式引擎會先尋找整個 Plan Cache，用以確認相同 SQL 陳述式，是否已經有存在 Execution Plan 。

如果 SQL Server 找到現有的 Execution Plan ，就會重複使用它，如此可省下 recompile重新編譯 SQL 陳述式的負擔。
如果沒有現有的 Execution Plan ，SQL Server 會為查詢建立新 Execution Plan 。

SQL Server 使用有效率的演算法，為任何 SQL 陳述式比對搜尋現有的 Execution Plan。

在大部分的情況下，系統使用少量的資源來執行掃描比對，找出能夠重複使用 Execution Plan，藉此節省了要 recompile SQL 陳述式所耗用的資源。

要建立一份有高效能的 Execution Plan，這包含許多層面，包含：系統組態、資料特性、Index、統計值等。

例如：統計值(Statistics)若未更新，造成誤判，因而使用到不適當的索引，導致建立出效率不佳 Execution Plan。

但若要能有效地重複使用同一份 Execution Plan，在開發 SQL 陳述式上有許多注意事項，少部分的開發習慣、寫法將導致 Query Optimizer 無法使用到正確的索引，或是誤用 Execution Plan，筆者將在後續主題討論。

本文討論:

觀察 Execution Plan 內所採用的 Index、STATISTICS IO(所產生的磁碟活動量) 等。
使用 Index Hint, WITH(FORCESEEK), Missing Index 等方式來自訂使用的 Index。

Look at Execution Plan and index: use Index Hint, WITH(FORCESEEK), Missing Index

示範環境：SQL Server 2017 Enterprise Edition

01. 使用資料表: Person.Address，觀察資料特性：

StateProvinceID RowCount
9, 4564
32, 1
41, 1

已知 Good Execution Plan

StateProvinceID=32, return: 1 row, Index Seek: [IX_Address_StateProvinceID], logical reads 4.
StateProvinceID=9, return: 4564 rows, Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode], logical reads 216.
StateProvinceID=41, return: 1 row, Index Seek: [IX_Address_StateProvinceID], logical reads 5.

-- figure 01_Table_Person_Address

02. 觀察資料表的 Index:

IX_Address_StateProvinceID

Column: StateProvinceID

IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode

Column: AddressLine1, AddressLine2, City, StateProvinceID, PostalCode

-- figure 02_Index Information on Person_Address

03. 啟用 Actual Execution Plan

執行 SQL 陳述式後，觀察其 Execution Plan

-- figure 03_Use_Include Actual Execution Plan

04. 啟用觀察 STATISTICS IO ，與執行 SQL 陳述式。

當 StateProvinceID=32 與 41，都是 Return: 1 rows，使用 Index Seek: [IX_Address_StateProvinceID]。Logical reads 只需 4 或 5。

01. StateProvinceID=32, return: 1 rows, Index Seek: [IX_Address_StateProvinceID]

Table 'Address'. Scan count 1, logical reads 4, physical reads 1

02. StateProvinceID=41, return: 1 rows, Index Seek: [IX_Address_StateProvinceID]

Table 'Address'. Scan count 1, logical reads 5, physical reads 1

-- figure 04_Look_at_Statistics_IO

-- figure 05_Look_at_Execution_Plan

Index Hint

Index Hint 僅指定使用哪一個 Index，並非指使用 Seek，仍有可能是 Scan。

01. 情境：Query Optimizer 判斷是 Index Scan，利用 Index Hint 改為自訂的 Index

當 StateProvinceID=9，建議的 Execution Plan 是：

Return: 4564 rows, Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
自動產生建議的 Missing Index
logical reads 是 216

若強迫改用 WITH (INDEX (IX_Address_StateProvinceID)):

Return: 4564 rows, Index Seek: [IX_Address_StateProvinceID]
自動產生建議的 Missing Index
logical reads 耗費高達 9433。比較起來，多耗費 43.67 倍。

-- figure 06_Index_Scan_Index_hint_worse

-- figure 07_Index_Scan_Missing_Index

02. 情境：Query Optimizer 判斷是 Index Seek，利用 Index Hint 改為自訂的 Index

當 StateProvinceID=32，建議的 Execution Plan 是：

Return: 1 rows, Index Seek:
[IX_Address_StateProvinceID]
logical reads 是 4

若強迫改用 WITH (INDEX (IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode)):

Return: 1 rows, Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
自動產生建議的 Missing Index
logical reads 耗費高達 216。比較起來，多耗費 54 倍。

-- figure 08_Index_hint_worse

-- figure 09_Index_Scan_worse

已知 Good Execution Plan

StateProvinceID=32, return: 1 row, Index Seek: [IX_Address_StateProvinceID], logical reads 4.
StateProvinceID=9, return: 4564 rows, Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode], logical reads 216.
StateProvinceID=41, return: 1 row, Index Seek: [IX_Address_StateProvinceID], logical reads 5.