搜尋本站文章

2017-12-17

[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: 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.




WITH (FORCESEEK)

WITH (FORCESEEK) 是強制 Query Optimizer 只能使用 Index Seek。

  • 在某些特定情況中使用 WITH (FORCESEEK) ,有機會獲得更好的效能。
  • 隨著資料量、資料特性的改變,固定僅使用 Index Seek ,未必是好的做法。


01. 情境:Query Optimizer 判斷是 Index Scan,使用 WITH (FORCESEEK),強制只能使用 Index Seek。

當 StateProvinceID=9,建議的 Execution Plan 是:
  • Return: 4564 rows, Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
  • 自動產生建議的 Missing Index
  • logical reads 是 216

若強迫改用 WITH (FORCESEEK):
  • Return: 4564 rows, Index Seek: [IX_Address_StateProvinceID]
  • 自動產生建議的 Missing Index
  • logical reads 耗費高達 9433。比較起來,多耗費 43.67 倍。

-- figure 10_WITH_FORCESEEK




-- figure 11_WITH_FORCESEEK_Missing_Index






Create Missing Index

01. 依據 Query Optimizer 的 Missing Index 建議,手動建立所需的 Index。

-- figure 12_Create Missing Index



02. 執行建立 Index,觀察其 Execution Plan。

-- figure 13_Create Missing Index




-- 03. 使用更有效率的 Index.

當 StateProvinceID=9,原先的 Execution Plan 是:
  • Return: 4564 rows, Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
  • 自動產生建議的 Missing Index
  • logical reads 是 216

採用 Missing Index 的建議,更有效率的 Index:
  • Return: 4564 rows, Index Seek: [IX_StateProvinceID_City]。
  • logical reads 只需 24。比較起來,只需要約 1/10 的磁碟 IO 資源。

-- figure 14_Better_Execution_Plan




-- figure 15_Better_Execution_Plan




04. 刪除先前所建立的 Index。

-- figure 16_Drop_Index








範例程式碼

20171217_Look at Execution Plan
https://drive.google.com/drive/folders/1EwxcuAXrPjNUf3FZSY9yuJDw_TY3GDXq?usp=sharing




參考資料

What is Parameter Sniffing ?
https://blogs.technet.microsoft.com/mdegre/2011/11/06/what-is-parameter-sniffing/

Execution plan for stored procedures chosen by SQL Server Database Engine
https://blogs.technet.microsoft.com/beatrice_popa/2013/05/10/execution-plan-for-stored-procedures-chosen-by-sql-server-database-engine/

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
https://technet.microsoft.com/en-us/library/cc966425.aspx

ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql

Checklist for Analyzing Slow-Running Queries
https://technet.microsoft.com/en-us/library/ms177500(v=sql.105).aspx

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
https://technet.microsoft.com/library/cc966419

淺談與統計資料(Statistics) 相關的資料庫屬性:自動建立統計資料、自動更新統計資料與自動非同步更新統計資料。AUTO_CREATE_STATISTICS、AUTO_UPDATE_STATISTICS、AUTO_UPDATE_STATISTICS_ASYNC
http://sharedderrick.blogspot.tw/2010/03/statistics-autocreatestatisticsautoupda.html

[SQL Server] Plan Cache concepts - 認識 Plan Cache
http://sharedderrick.blogspot.tw/2017/11/sql-server-plan-cache-concepts-plan.html

[SQL Server] Ad hoc Query and Reuse - 重複使用
http://sharedderrick.blogspot.tw/2017/11/sql-server-ad-hoc-query-and-reuse.html

沒有留言:

張貼留言