若想開發出高效能的 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
- 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
沒有留言:
張貼留言