習以為常的 Performance Tuning (效能調教) 方法，卻是不正確的!
試想，有一支龐大的 Stored Procedures 要執行 Performance Tuning
- 某段 SQL Query 可能是 Bottlenecks
- 如何使用 SSMS 對該 SQL Query 執行 Performance Tuning 呢？
請參考以下範例：
-- figure 100_Trap in Local Variables
又是 踩到 "Local Variables / OPTIMIZE FOR UNKNOWN" 這個 雷區。
在執行 Stored Procedure 的 Performance Tuning 時，記得這項規則：
利用 SSMS 管理工具對 Stored Procedure 執行 測試 與 改寫 時，使用
- Literal Values (常值)
- Temporary Stored Procedures
- Stored Procedures
Incorrect Performance Tuning in stored procedure: use Local Variables issue
01. 建立 Stored Procedure: dbo.up_PerformanceTuning_LocalVariables_Issue
假設：此 Stored Procedure 有多行 SQL Query。
-- figure 01_Create Stored Procedure_up_PerformanceTuning_LocalVariables_Issue
初步分析，以下 SQL Query 可能是 Performance Bottlenecks (效能瓶頸)：
SELECT UnitPrice FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID
那該如何使用 SSMS 對此 SQL Query 執行 Performance Tuning 呢？
有一支龐大的 Stored Procedures，若要 Performance Tuning 某一段 SQL Query，流程是：
- Task 1: 先由 Plan Cache 找出 Execution Plan
- Task 2: 找出 Execution Plan 的 Parameter Compiled Value
- Task 3: 使用 Literal Values (常值) 或 Temporary Stored Procedures
Find out Parameter Compiled Value
How to find out Execution Plan of Parameter Compiled Value?
找出 Parameter Compiled Value
如何找出 Execution Plan 的 Compiled Value?
01. 先由 Plan Cache 中，找出此 SQL Query 現行的 Execution Plan。
請參考：[SQL Server] Look at parameter from execution plan
- return 2 row
- Index Seek: [IX_SalesOrderDetail_ProductID]
-- figure 02_Get last performance statistics for cached query
02. 檢視此 Execution Plan。
-- figure 03_Show Execution Plan XML
02. 點選 "Show Execution Plan XML..."
- 觀察：ParameterList
- ParameterCompiledValue="(897)"
-- figure 04_Parameter Compiled Value
03. 若使用 SentryOne Plan Explorer，點選 "View with SentryOne Plan Explorer"
- Compiled Value : 897
-- figure 05_View with SentryOne Plan Explorer
Incorrect Performance Tuning in stored procedure: use Local Variables issue
Trap in Local Variables
對 Stored Procedure 執行 不正確的 Performance Tuning
陷入 Local Variables 陷阱之中
01. 若習以為常的使用 Local Variables 來執行 Performance Tuning，將會是以下範例：
-- figure 11_Incorrect Performance Tuning in stored procedure_Local Variables issue
02. 陷入 Local Variables 陷阱之中，對 Stored Procedure 執行 不正確的 Performance Tuning
- Not match Execution Plan, Bad
- return 2 row
- Clustered Index Scan: [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
仔細留意：
- 此 Execution Plan 不僅是使用 執行效能差 的 Index Scan。
- 而且，竟然與先前由 Plan Cache 取得的 Execution Plan 不同!
-- figure 12_Execution Plan_Incorrect Performance Tuning
原因是
- 踩到 "Local Variables / OPTIMIZE FOR UNKNOWN" 這個 雷區!
請參考：[SQL Server] Parameter Sniffing: Local Variable
http://sharedderrick.blogspot.tw/2018/01/sql-server-parameter-sniffing-local.html
Correct Performance Tuning in stored procedure
Solution
- Use Literal Values
- Use Temporary Stored Procedures
Solution 01: Use Literal Values
01. 直接使用 Literal Values (常值)
- 將原本使用 Input Parameter ，直接使用 Literal Values 來取代。
觀察 Execution Plan
- 與先前由 Plan Cache 取得的 Execution Plan 相同。
- Index Seek: [IX_SalesOrderDetail_ProductID]
-- figure 21_Solution 01 Use Literal Values
Solution 02: Use Temporary Stored Procedures
01. 建立 Temporary Stored Procedures (暫存預存程序)
- 宣告以 # 或 ## 開頭的 Temporary Stored Procedures。
Temporary Stored Procedures 有 2 種：
- Local (區域): single number sign (#)
- Global (全域): two number signs (##)
觀察 Execution Plan
- 與先前由 Plan Cache 取得的 Execution Plan 相同。
- Index Seek: [IX_SalesOrderDetail_ProductID]
-- figure 31_Solution 02 Use Temporary Stored Procedures
在執行 Stored Procedure 的 Performance Tuning 時，記得這項規則：
利用 SSMS 管理工具對 Stored Procedure 執行 測試 與 改寫 時，使用
- Literal Values (常值)
- Temporary Stored Procedures
- Stored Procedures
Sample Code
20180122_IncorrectPerformanceTuning_LocalVariables
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing
Constants
常數也稱為常值或純量值，是一個代表特定資料值的符號。
A constant, also known as a literal or a scalar value, is a symbol that represents a specific data value.
- Constant 常數
- Literal 常值
- Scalar value 純量值
Temporary Stored Procedures
有兩種：Local 區域與 Global 全域。
它們在名稱、可見性和可用性方面有些差異。
Local Temporary Stored Procedures
- 是以單一數字符號 (#) 作為名稱的第一個字元；
- 只有目前連接的使用者才能看見它們，當使用者中斷與 SQL Server 執行個體的連接時，就會刪除它們。
Global Temporary Stored Procedures
- 是以兩個數字符號 (##) 作為名稱的前兩個字元；
- 只要一建立好，任何使用者都能看見它們，只有當所有參考這些 Stored Procedures 的使用者都中斷與 SQL Server 執行個體的連接時，才會刪除它們。
參考資料
[SQL Server] Ad hoc Query: Local Variable and Without Local Variable
http://sharedderrick.blogspot.tw/2018/01/sql-server-ad-hoc-query-local-variable.html
[SQL Server] Look at parameter from execution plan
http://sharedderrick.blogspot.tw/2017/12/sql-server-look-at-parameter-from.html
Constants (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql
沒有留言:
張貼留言