習以為常的 Performance Tuning (效能調教) 方法,卻是不正確的!
試想,有一支龐大的 Stored Procedures 要執行 Performance Tuning
- 某段 SQL Query 可能是 Bottlenecks
- 如何使用 SSMS 對該 SQL Query 執行 Performance Tuning 呢?
請參考以下範例:
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEje6p_tr7cXnSvFplVn3Ds_b5th-vcUldnNsYCMCYnYFHWU-5mM59pYOOeoabOeOudw0TCZZ_igvRCb9lR65VH6E-uGjXxoVyqDINVk1fBIObC9o89AbgbGLbylbRUQh20PSNVJYd8CrjQ/s640/100_Trap+in+Local+Variables.png)
-- 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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEielaRQPbPk6EW52Sbf7xw6yiUbOcPV14bigmjglmGGyw5iifkQYOTuZQ75W2FCKoNRkLLrUDd3UGvhLRrJ5ms1N1PdbW76uItN8yM5l_lrZghIP-k4RPRmfVCTdxW5smd1TT7MSGKKutg/s640/01_Create+Stored+Procedure_up_PerformanceTuning_LocalVariables_Issue.png)
初步分析,以下 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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_9Dm9wsLLHuOKeiIxB0m0LhG_w8RnMEdfPUavp17ue23Dnw-QP1CxOHO-vkg_WyhZhWgmdDoM8Lg-iwXrpdXQHAlU3gLbRl2pe_1dT7-Py3_4qM1YjdYAuFimlXgW9lKFQ7sY-At4IBw/s640/02_Get+last+performance+statistics+for+cached+query.png)
02. 檢視此 Execution Plan。
-- figure 03_Show Execution Plan XML
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjP0IlqvsRYbg7fNXne4y8AOfPITEpiyNvwU4XgzkLi2uNdsRrS6XKo6wYiUH2Nkcn7mNftAHg3n2HRM8rqPF17D4YVcTkRAT930JchYiTVX29b12BDYDlDLTxRRNCrkeHYZ9yrGYjVgDk/s640/03_Show+Execution+Plan+XML.png)
02. 點選 "Show Execution Plan XML..."
- 觀察:ParameterList
- ParameterCompiledValue="(897)"
-- figure 04_Parameter Compiled Value
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjR-Eqc3v92f1bZ9GGhI6NJ13lRG8QvFjhRKL4DNq1i06JbrYvIF7VuF4bUNgwf3bEW0j04cGj8s3FSbo2flAKNHtypyBqQuwE_lh86WLuIKyJcxiz4yMCy5Ky1vyf65iGqNT7kFXrAHs0/s640/04_ParameterCompiledValue.png)
03. 若使用 SentryOne Plan Explorer,點選 "View with SentryOne Plan Explorer"
- Compiled Value : 897
-- figure 05_View with SentryOne Plan Explorer
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEqB67YozpK-6gx9C2vu-qIbeBTRRJqdym78OwZ0rUQ0FFTewyzWFp7YH8QHp9Rt9fM2szlUzEHxiRDgjS2ol5KUWtcpj26E6MYZ5c-GZU12ZP_WCGqhMnXuXPIsoPxjcdKDTEJBA7ktg/s640/05_View+with+SentryOne+Plan+Explorer.png)
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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_vfpiLcSvZbhtRcf30pfZLyTPkTFjvkY7K5NpEer6S68yr7Cl-IiB5LqKaz1sG1-jpobJ9-b2WKDwUL2gMGcKgTEW8O2wl62n-K8qSfKTM9tQfv3SnXywt89bGavRV8tlnZJvjA_TF6o/s640/11_Incorrect+Performance+Tuning+in+stored+procedure_Local+Variables+issue.png)
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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMAa8Zujx7X5FwLqycBj1N7yqrt_MmxtE2h35a6zdNK_FOECpbE3GlLKC8NfydZXQ2H_fVSv8KbAqDbHwbFgf7kh-mt_Az5Bd6etnPATzlYGLoTAV9U_CW5F3WQ3HyouMtdOHZ7acCVLY/s640/12_ExecutionPlan_Incorrect+Performance+Tuning.png)
原因是
- 踩到 "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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikZYBjnfuEmidTHA87k_P2i4XF6qP3MrIZYdJxusveDvHzfqCiGLpKBuk538cAZtKTogYodp1jzRi568IkHElxci2osNzPGOvU_VaRd_ac2co3-pxjBX5DMzh5tApUv2KqKQfN0BkYwKI/s640/21_Solution+01+Use+Literal+Values.png)
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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZiV3uTjIM2O3FHDgEa_IQe5NvrbVdHgOI_v8xmx9_f-AHpTFCV39QYYkAivdBt9cY-FQM-Z04HyxD8T2i94ku44EL4IniW2C_T5pmUrCjxPBZdxjLteq9SGnU1BMqu8BAVxYgQjO2vu0/s640/31_Solution+02+Use+Temporary+Stored+Procedures.png)
在執行 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
沒有留言:
張貼留言