2018-01-22

[SQL Server] Incorrect Performance Tuning in stored procedure: Local Variables issue



習以為常的 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
不要額外使用 Local Variables 改寫 SQL Query 。



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
不要額外使用 Local Variables 改寫 SQL Query 。



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
於

沒有留言:

張貼留言