搜尋本站文章

2018-08-31

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (4)


延續前一篇文章:[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (3)


使用 UNION ALL 或 IF ELSE 改寫 Stored Procedure


  • 仍使用 Parameter Sniffing 機制,Reuse Execution Plan。
  • 讓 Query Optimizer 優化使用到更佳的索引,例如: Clustered Index Seek。
  • 使用 IF ELSE,經判斷後, 執行滿足 條件式 的 SQL Statement。
  • 但 UNION ALL 卻是執行 全部 的 SQL Statement,再組合起來回傳。





繼續討論 Parameter Sniffing 的 因應之道:


  1. 使用 EXEC ... WITH RECOMPILE
  2. 使用 sp_recompile
  3. 修改 Stored Procedure,使用 Local Variables
  4. 修改 Stored Procedure,使用 WITH RECOMPILE
  5. 修改 Stored Procedure,使用 OPTION RECOMPILE
  6. 改寫 Stored Procedure,使用 UNION ALL
  7. 改寫 Stored Procedure,使用 IF ELSE
  8. 改寫 Stored Procedure,使用 Dynamic SQL: EXEC
  9. 修改 Stored Procedure,使用 Dynamic SQL: sp_executesql
  10. 因應需求,分別建立 Stored Procedure






Parameter Sniffing with OR Operator issue,
當 Parameter Sniffing 遇上 OR 運算子(4)


回顧

  • 條件式:  WHERE (@SalesOrderID IS NULL OR sd.SalesOrderID = @SalesOrderID)




以 Index 使用策略來看,已知
  • @SalesOrderID IS NULL,使用 Index Scan。
  • sd.SalesOrderID = @SalesOrderID,使用 Index Seek。

今藉由 OR 運算子,整合在同一個 Stored Procedure。
若硬是要找出一個 Index 可以符合這兩者的效能需求,這該怎麼辦呢?

燃起 程式魂

跳脫框架,改寫 條件式邏輯!


6. 改寫 Stored Procedure,使用 UNION ALL


此處使用 改寫,是因為要對 條件式 進行改寫,而非先前僅是修改 編譯參數。



說明:
  • 使用 Parameter Sniffing 機制,Reuse Execution Plan。
  • UNION ALL
    • 每次執行 Stored Procedure,都必須要分別執行全部 SQL Statement 的 WHERE 條件式之判斷。
    • 再利用 UNION ALL 將結果組合起來回傳。這勢必多耗用系統資源。
    • Execution Plan 相對複雜,Subtree Cost 已失真。

  • 對比 Local Variables,改寫為 UNION ALL:
    • 讓 Query Optimizer 優化使用到更佳的索引,例如: Clustered Index Seek。
    • 沒有使用到效率差的 Clustered Index Scan。

  • 對比 WITH RECOMPILE 或 OPTION RECOMPILE,改寫為 UNION ALL:
    • 使用 Parameter Sniffing 機制,Reuse Execution Plan,沒有額外重新編譯。
    • 相形之下,會比 WITH RECOMPILE 或 OPTION RECOMPILE 更適合於 每秒 都要大量執行 或 同時間會大量執行 的程式 等情境。
    • 但若有大量 UNION ALL 卻會耗用過多資源,需要進一步的測試與評估。


注意事項:
  • 修改 Stored Procedure 程式碼,牽扯版本問題。
    • 需要與 SQL Developer 人員溝通討論。

01. 建立使用 UNION ALL 的 Stored Procedure。

  • 必須修改原來的程式碼。
  • 將 條件式 OR 運算子,改寫為 多段 SELECT statement,再額外使用 UNION ALL 結合起來。
  • 換句話說
    • 輸入 任何 參數值,都必須要分別執行全部 SQL Statement 的 WHERE 條件式之判斷。
    • 再利用 UNION ALL 將結果組合起來回傳。
    • 增加執行成本。
  • 若有多段 SELECT statement,會增加維護的複雜度。


-- figure 61_ALTER Proc UNION ALL




02. 執行 使用 UNION ALL 的 Stored Procedure。

分別輸入 NULL 與 43659。

  • 當輸入參數是 NULL:
    • 取回全部資料,回傳 118,744 rows,使用 Clustered Index Scan。

  • 當輸入參數是 43659:
    • 取回指定 SalesOrderID 的資料,回傳 12 rows。
    • 經 Query Optimizer 執行優化後,使用 Clustered Index Seek
      • 沒有使用到效率差的 Clustered Index Scan。
    • 這是有提升執行效能的 Execution Plan。

似乎美好,進一步來檢視 Execution Plan。

-- figure 62_EXEC_Proc UNION ALL






03. 觀察 Execution Plan:

  • 複雜的 Execution Plan
    • 使用 Concatenation 運算子,結合多段 SQL Statement 的 Execution Plan。
  • 將 輸入參數為 NULL 與 輸入參數 43659 的兩份 Execution Plan,結合成一大份 Execution Plan。
    • 換句話說,輸入 任何 參數值,都必須要分別執行全部 SQL Statement 的 WHERE 條件式之判斷。
    • 再利用 UNION ALL 將結果組合起來回傳。

採用 Parameter Sniffing 機制

由於是各自獨立的 SQL statement,再使用 UNION ALL 結合再一起,因此,Query Optimizer 可以為各個 SQL statement 選擇更加合適的 Index。
  • 但仍然採取 Parameter Sniffing 機制,重複使用 Execution Plan。
  • 潛藏著誤用 Execution Plan,導致效能更糟的問題。 
-- figure 63_Complex Execution Plan





04. 觀察 Execution Plan:
  • 整體的 Estimated Subtree Cost是:5.03934。
  • 已失真的 Subtree Cost
    • 結合多份 SQL statement,所產生的 Execution Plan。

-- figure 64_Complex Execution Plan





05. 綜合觀察 1

當輸入參數是 NULL

  • 使用 Clustered Index Scan。
  • Actual Rows: 118,744。
  • Est Rows: 116,323,這是 Estimated Number of Rows。
  • Reads: 2,249,這是 Logical Reads。


-- figure 65_Plan_Explorer





05. 綜合觀察 2

當輸入參數是 43659

  • 使用 Clustered Index Seek。
  • Actual Rows: 12。
  • Est Rows: 116,323,這是 Estimated Number of Rows。
    • 評估失真。
  • Reads: 372,這是 Logical Reads。

-- figure 66_Plan_Explorer







7. 改寫 Stored Procedure,使用 IF ELSE


此處使用 改寫,是因為要對 條件式 進行改寫,而非先前僅是修改 編譯參數。



說明:

  • 使用 Parameter Sniffing 機制,Reuse Execution Plan。
  • 使用 IF ELSE 
    • 經判斷後,僅執行 滿足 條件式 的 Transact-SQL 陳述式。
  • 對比 Local Variables,改寫為 IF ELSE :
    • 讓 Query Optimizer 優化使用到更佳的索引,例如: Clustered Index Seek。
    • 沒有使用到效率差的 Clustered Index Scan。
  • 對比 WITH RECOMPILE 或 OPTION RECOMPILE,改寫為 IF ELSE:
    • 使用 Parameter Sniffing 機制,Reuse Execution Plan,沒有額外重新編譯。
    • 相形之下,會比 WITH RECOMPILE 或 OPTION RECOMPILE 更適合於 每秒 都要大量執行 或 同時間會大量執行 的程式 等情境。
    • 但若 IF ELSE 是複雜邏輯運算,則應評估與測試。
  • 對比 UNION ALL,改寫為 IF ELSE:
    • 使用 IF ELSE 流程控制語言,經判斷後, 執行滿足 條件式 的 Transact-SQL 陳述式;
    • 但 UNION ALL 卻是執行全部的 Transact-SQL 陳述式,再組合起來回傳。
    • 若有多段 SELECT statement 需要使用到 IF ELSE 結合,則會增加維護的複雜度。

說明:
  • 修改 Stored Procedure 程式碼,牽扯版本問題。
    • 需要與 SQL Developer 人員溝通討論。


01. 建立使用 IF ELSE 的 Stored Procedure。

  • 必須修改原來的程式碼。

使用 IF ELSE 流程控制語言,改寫為:

  • 如果 IF 關鍵字的條件獲得滿足,就會執行在 IF 關鍵字及其條件之後的 Transact-SQL 陳述式:布林運算式會傳回 TRUE。
  • 選擇性的 ELSE 關鍵字導入了另一個在 IF 條件未獲滿足時所執行的 Transact-SQL 陳述式:布林運算式會傳回 FALSE。

與 UNION ALL 不同在於

  • 使用 IF ELSE 流程控制語言,經判斷後, 僅執行滿足 條件式 的 Transact-SQL 陳述式;
  • 但 UNION ALL 卻是執行全部的 Transact-SQL 陳述式,再組合起來回傳。
  • 若有多段 SELECT statement 需要使用到 UNION ALL 結合,則會增加維護的複雜度。

-- figure 71_ALTER Proc IF ELSE






02. 執行 使用 IF ELSE 的 Stored Procedure。

分別輸入 NULL 與 43659。

當輸入參數是 NULL:

  • 取回全部資料,回傳 118,744 rows,使用 Clustered Index Scan。


當輸入參數是 43659:

  • 取回指定 SalesOrderID 的資料,回傳 12 rows。
  • 經 Query Optimizer 執行優化後,使用 Clustered Index Seek
  • 沒有使用到效率差的 Clustered Index Scan。
  • 這是有提升執行效能的 Execution Plan。

似乎美好,進一步來檢視 Execution Plan。

-- figure 72_EXEC_Proc IF ELSE





03. 觀察 Execution Plan:


以 EXEC GetSalesList_IF 43659 為例

  • 經判斷後,僅執行該 SQL Query 的 Execution Plan
    • 與 UNION ALL 比較起來, 使用 IF ELSE 的 Execution Plan 是簡潔的。
  • 使用 IF ELSE 流程控制語言,經判斷後, 僅執行滿足 條件式 的 Transact-SQL 陳述式。

採用 Parameter Sniffing 機制

  • 使用 IF ELSE 流程控制語言 僅執行滿足 條件式 的 Transact-SQL 陳述式,因此,Query Optimizer 可以為各個 SQL statement 選擇更加合適的 Index。
  • 但仍然採取 Parameter Sniffing 機制,重複使用 Execution Plan。
  • 潛藏著誤用 Execution Plan,導致效能更糟的問題。 


當輸入參數是 43659

  • 使用 Clustered Index Seek。
  • Actual Rows: 12。
  • Est Rows: 1,這是 Estimated Number of Rows。
  • Reads: 10,這是 Logical Reads。
    • 對比使用 UNION ALL,Reads 是 372,UNION ALL 使用更多的 Logical Reads。


-- figure 73_Plan_Explorer





使用到 Compute Scalar 運算子,這是因為有使用 Computed Column(計算資料行): LineTotal。

[[AdventureWorks2014].[Sales].[SalesOrderDetail].LineTotal] = Scalar Operator(isnull(CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPrice] as [sd].[UnitPrice],0)*((1.0)-CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPriceDiscount] as [sd].[UnitPriceDiscount],0))*CONVERT_IMPLICIT(numeric(5,0),[AdventureWorks2014].[Sales].[SalesOrderDetail].[OrderQty] as [sd].[OrderQty],0),(0.000000)))

-- figure 111_Computed Column_LineTotal




-- figure 112_Computed Column_LineTotal





-- figure 113_Computed Column_LineTotal





04. 觀察 Execution Plan:

  • Actual Rows: 12
  • Estimated Rows: 1

些微的落差。

-- figure 74_Plan_Explorer






Sample Code

20180821_Parameter_Sniffing_with_OR_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing




Reference

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or.html

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (2)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or_23.html

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (3)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or_24.html

[Parameter Sniffing] Reuse bad execution plan and Recompile stored procedure, 重複使用效能差的 執行計劃 與 重新編譯 預存程序
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-reuse-bad-execution.html

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與 動態 SQL)
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-dynamic-sql.html

Compare "WITH RECOMPILE / sp_recompile" with "OPTION RECOMPILE"
http://sharedderrick.blogspot.com/2018/02/sql-server-compare-with-recompile-with.html

Parameter Sniffing: Local Variable
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-local.html

Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-option_7.html

Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-option_14.html

Showplan Operator of the Week – Compute Scalar
https://www.red-gate.com/simple-talk/sql/learn-sql-server/showplan-operator-of-the-week-compute-scalar/

Showplan Operator of the Week – Concatenation
https://www.red-gate.com/simple-talk/sql/learn-sql-server/showplan-operator-of-the-week---concatenation/

2018-08-26

[Parameter Sniffing] Reuse bad execution plan and Recompile stored procedure, 重複使用效能差的 執行計劃 與 重新編譯 預存程序


曾經,遇見過 Parameter Sniffing 副作用的處理方式:

  • 只能採用  "Local Variables / OPTIMIZE FOR UNKNOWN"。

強調 重新編譯 Stored Procedure 對於 資源耗用與影響。

這是不合理的作法,缺少了對於 資料特性、存取資料量、執行頻率、Index、Statistics等角度考量,也忽視改寫 T-SQL 優化效能的可行性。

一昧使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制,或許,節省了開發時程 或 管控方便,但卻落得 Stored Procedure 執行效能不佳的問題!

本案例,將以 "重複使用 效能差的 Execution Plan" 與 "重新編譯 Stored Procedure" 的整體執行效能差異來對照。







[Parameter Sniffing] Reuse bad execution plan and Recompile stored procedure. 
重複使用效能差的 執行計劃 與 重新編譯 預存程序



01. 使用範例 Parameter Sniffing 與 OR 運算子,請參考以下文章:

Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)


條件式:  WHERE (@SalesOrderID IS NULL OR sd.SalesOrderID = @SalesOrderID)

  • @SalesOrderID IS NULL: 當 @SalesOrderID 是 NULL 時,取回全部的資料,回傳 118,744 rows。
  • sd.SalesOrderID = @SalesOrderID: 取回 指定的 SalesOrderID 的 資料,回傳 12 rows。

以 Index 使用策略來看:
  • 這兩個條件式,應該是使用不同的 Index 存取方式,例如:Index Scan、Index Seek 等。


-- figure 00_Parameter Sniffing with OR issue





02. 執行此 Stored Procedure: dbo.GetSalesList。

  • 1st
    • 使用 sp_recompile,重新編譯。
    • 輸入參數 NULL。
  • 2nd
    • 因 Parameter Sniffing 發揮功效,重複使用 Execution Plan。
    • 誤用到效能差的 Execution Plan: Clustered Index Scan
    • 輸入參數 43659。
  • 3rd
    • 再度 使用 sp_recompile,重新編譯。
    • 使用效能佳的 Execution Plan: Clustered Index Seek
    • 輸入 NULL 與 43659。

-- figure 11_Parameter Sniffing_sp_recompile_Exec_Proc





03. 使用 DMV 查詢 記憶體 Cache 區,檢視 Performance Data.


-- figure 22_Compare_ComileTime_CPU





SubTreeCost
CompileTime(ms)
LastElapsedTime(sec) 
LastCPUTime(sec) 
LastLogicalReads
LastRows
1st
5.0630900
17
1.345748000
0.541942000
2,249
118,744
2nd
5.0630900
170.1817640000.066875000156412
3rd0.562674060.1239310000.01033300039812



-- 1st: Parameter Sniffing with OR issue
-- Clustered Index Scan, return 118,744 rows

  • StatementSubTreeCost 5.0630900
  • CompileTime(ms) 17
  • CompileCPU(ms) 17
  • CompileMemory(KB) 1216
  • LastElapsedTime(sec) 1.345748000
  • LastCPUTime(sec) 0.541942000
  • LastLogicalReads 2,249
  • LastRows 118,744

-- 2nd: Bad: Clustered Index Scan, return 12 rows
-- Reuse bad execution plan

  • StatementSubTreeCost 5.0630900
  • CompileTime(ms) 17
  • CompileCPU(ms) 17
  • CompileMemory(KB) 1216
  • LastElapsedTime(sec) 0.181764000
  • LastCPUTime(sec) 0.066875000
  • LastLogicalReads 1564
  • LastRows 12

-- 3rd: "sp_recompile"
-- Good: Index Scan + Clustered Index Seek, return 12 rows

  • StatementSubTreeCost 0.5626740
  • CompileTime(ms) 6
  • CompileCPU(ms) 6
  • CompileMemory(KB) 1000
  • LastElapsedTime(sec) 0.123931000
  • LastCPUTime(sec) 0.010333000
  • LastLogicalReads 398
  • LastRows 12


比較

  • 編譯 Compile 時間的最大值: 0.017 sec。
  • 每次執行時間的差距(0.181764 - 0.12393) : 0.057834 sec
  • 每次 Logical Read 的差距(1564 - 398):1166。
  • 加入 編譯 Compile 時間的耗損(0.057834 - 0.017):0.040834 sec

若執行 100 次,Reuse bad execution plan 與 Recompile stored procedure 來比較:

  • 執行時間差距 ( (0.181764)*100 - (0.12393 + 0.017)*100 ) ,多耗用: 4.0834 sec。
  • Logical Read 差距(1166 *100) ,多耗用:116,600。


由上,隨著執行次數越頻繁,因故重複使用 效能差的 Execution Plan,卻是耗費更多的資源。


可以得知:

  • 非 每秒 都要大量執行 或 同時間會大量執行 的程式,改用重新編譯 Stored Procedure,獲取更佳的執行效能。這筆支出,是值得的。



-- Get execution plan for cached query: [CompileTime(ms)], [StatementSubTreeCost], [LastElapsedTime(sec)], LastLogicalReads, LastRows etc.
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns)

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 TRY_CAST(qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@StatementSubTreeCost)[1]', 'float') 
 AS decimal(38,7)) [StatementSubTreeCost],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileTime)[1]', 'int') [CompileTime(ms)], 
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileCPU)[1]', 'int') [CompileCPU(ms)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileMemory)[1]', 'int') [CompileMemory(KB)],
 qs.last_elapsed_time/1000000.0 [LastElapsedTime(sec)],
 qs.last_worker_time/1000000.0 [LastCPUTime(sec)],
 qs.last_logical_reads LastLogicalReads,
 qs.last_physical_reads LastPhysicalRead,
 qs.last_logical_writes LastlogicalWrites,
 qs.last_rows LastRows,
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014') 
-- AND cp.objtype = 'Proc' CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY StatementSubTreeCost DESC;
GO

-- figure 23_Get execution plan for cached query








Sample Code

20180825_Get_parse_compile_execute_time_SQL
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing





Reference


[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or.html

[SQL Server] Get SubTreeCost, CompileTime, CompileMemory, Execution Count, Plan Recompile from Plan Cache - 子樹成本

Get parse, compile, and execute time for SQL statement. 取得 剖析、編譯 和 執行 SQL 查詢 所耗用的時間



若取得 剖析、編譯 和 執行 SQL 查詢 所耗用的時間,可以使用以下方式:

  • 使用 SET STATISTICS IO, TIME ON 取得。
  • 檢視 Execution Plan 取得。
  • 使用 DMV 直接上 記憶體 Cache 區取得。







Get parse, compile, and execute time for SQL statement. 
取得 剖析、編譯 和 執行 SQL 查詢 所耗用的時間



01. 依序執行以下 SQL 陳述式:

  • SET STATISTICS IO, TIME ON: 是用於取得 剖析、編譯和執行的 時間與 Disk IO 資訊。 
  • sp_recompile: 是用於要求 SQL Server 重新編譯 Stored Procedure。

以下是各個的功用說明。

SET STATISTICS IO, TIME ON: 

  • TIME: 
    • 顯示剖析、編譯和執行每個陳述式所需要的毫秒數。 
    • Displays the number of milliseconds required to parse, compile, and execute each statement.
  • IO: 
    • 顯示 Transact-SQL 陳述式所產生之磁碟活動量的相關資訊。 
    • Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.


sp_recompile: 

  • 在下次執行預存程序、觸發程序以及使用者定義函數時,重新編譯它們。 
  • 其運作方式是從程序快取中卸除現有的計畫,以便強制在下次執行程序或觸發程序時建立新的計畫。


-- figure 01_SET STATISTICS IO TIME ON





02. 執行 Stored Procedure。

  • EXEC dbo.GetSalesList 43659


-- figure 02_Execute_Stored_Procedure




03. 檢視輸出,可以看到 parse and compile time:

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 15 ms, elapsed time = 43 ms.

-- figure 03_parse and compile time






04. 也可以由 Execution Plan 取得相關的資訊:


  • CompileCPU: 12
  • CompileMemory: 1008
  • CompileTime: 35


-- figure 04_Actual_Execution_Plan_Complie_Time_CPU





05. 想要知道 已經執行過 SQL Statement 的 編譯相關資源嗎?

使用 DMV 直接上 記憶體 Cache 區,自行取用 :)

  • CompileTime(ms): 35
  • CompileCPU(ms): 12
  • CompileMemory(KB): 1008

-- figure 05_Get execution plan for cached query




-- Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns)

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 TRY_CAST(qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@StatementSubTreeCost)[1]', 'float') 
 AS decimal(38,7)) [StatementSubTreeCost],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileTime)[1]', 'int') [CompileTime(ms)], 
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileCPU)[1]', 'int') [CompileCPU(ms)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileMemory)[1]', 'int') [CompileMemory(KB)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@RetrievedFromCache)[1]', 'nvarchar(50)') [RetrievedFromCache],
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014') 
-- AND cp.objtype = 'Proc' CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY StatementSubTreeCost DESC;
GO






Sample Code

20180825_Get_parse_compile_execute_time_SQL
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing





Reference


[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or.html

[SQL Server] Get SubTreeCost, CompileTime, CompileMemory, Execution Count, Plan Recompile from Plan Cache - 子樹成本
http://sharedderrick.blogspot.com/2017/12/sql-server-get-subtreecost-compiletime.html



2018-08-24

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (3)


延續前一篇文章:[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (2)


繼續討論 Parameter Sniffing 的 因應之道:
  1. 使用 EXEC ... WITH RECOMPILE
  2. 使用 sp_recompile
  3. 修改 Stored Procedure,使用 Local Variables
  4. 修改 Stored Procedure,使用 WITH RECOMPILE
  5. 修改 Stored Procedure,使用 OPTION RECOMPILE
  6. 改寫 Stored Procedure,使用 UNION ALL
  7. 改寫 Stored Procedure,使用 IF ELSE
  8. 改寫 Stored Procedure,使用 Dynamic SQL: EXEC
  9. 改寫 Stored Procedure,使用 Dynamic SQL: sp_executesql
  10. 因應需求,分別建立 Stored Procedure




Parameter Sniffing with OR Operator issue,
當 Parameter Sniffing 遇上 OR 運算子(3)



4. 修改 Stored Procedure,使用 WITH RECOMPILE



說明

WITH RECOMPILE
  • 停用 Reuse Execution Plan 功能
    • 每次執行 Stored Procedure,都要 Recompile SQL 陳述式 ,並執行 Parameter Sniffing,這勢必多耗用系統資源。


注意事項

  • 修改 Stored Procedure 程式碼,牽扯版本問題。
    • 需要與 SQL Developer 人員溝通討論。
  • 停用 Reuse Execution Plan 功能
  • 每次執行 Stored Procedure,都要 Recompile SQL 陳述式,這勢必多耗用系統資源。
  • 以 Execute Frequency(執行頻率) 來考量
    • 不適用於 every second(每秒) 都要執行 或 同時間會大量執行 等情境。
      • 每次都重新編譯,將過度耗用系統資源。
    • 適用於: 年報、季報、月報、周報、日報、小時報或數分鐘報等
      • 如能因重新編譯 Stored Procedure,獲取更佳的執行效能。這筆支出,值得。


01. 建立使用 WITH RECOMPILE 的 Stored Procedure。

  • 必須修改原來的程式碼。
  • 在 Stored Procedure 內,額外再宣告 WITH RECOMPILE 來使用。

-- figure 41_ALTER Proc WITH RECOMPILE




02. 執行 使用 WITH RECOMPILE 的 Stored Procedure。

分別輸入 NULL 與 43659。


  • 當輸入 NULL:
    • 取回全部資料,回傳 118,744 rows,使用 Clustered Index Scan。

  • 當輸入 43659: 
    • 取回指定 SalesOrderID 的資料,回傳 12 rows。
    • 經 Query Optimizer 執行優化後,使用 Index Scan + Clustered Index Seek
      • 沒有使用到效率差的 Clustered Index Scan。
    • 這是有提升執行效能的 Execution Plan


若非 每秒 都要執行 或 同時間會大量執行 的程式,能因 WITH RECOMPILE 重新編譯 Stored Procedure,獲取更佳的執行效能。這筆支出,是值得的。

-- figure 42_EXEC_Proc WITH RECOMPILE




03. 觀察 Execution Plan:


EXEC GetSalesList_WITH_RECOMPILE NULL

  • 使用 Clustered Index Scan。
  • Actual Rows: 118,744。
  • Est Rows: 116,322,這是 Estimated Number of Rows。
  • Reads: 2,315,這是 Logical Reads。



EXEC GetSalesList_WITH_RECOMPILE 43659


  • 重新編譯 ,獲取執行效能更佳的 Execution Plan。
  • 使用 Index Scan + Clustered Index Seek。
  • Actual Rows: 12
  • Est Rows: 12,這是 Estimated Number of Rows。
  • Reads: 464,這是 Logical Reads。

-- figure 43_Plan_Explorer





04. 進一步觀察 Execution Plan

Operation caused residual IO.
The actual number of rows read was 121,317, but the number of rows returned was 12.

雖然已經不用 Clustered Index Scan,Query Optimizer 判斷改用 Index Scan,但實際僅取回 12 rows,卻仍須讀取 121,317 rows,仍是耗用過多的系統資源。

讓我們繼續設計出更有效率的執行方式與 Execution Plan!

-- figure 44_Plan_Explorer






5. 修改 Stored Procedure,使用 OPTION RECOMPILE


說明

使用 OPTION (RECOMPILE)

  • 針對特定的 SQL Statement,停用其 Reuse Execution Plan 功能。
  • 僅 Recompile 指定的 SQL Statement,並且可使用到 Local Variable 的內容值
  • 每次執行 Stored Procedure,僅需 Recompile 指定的 SQL 陳述式,但仍有耗用過多資源的顧慮。

注意事項


  • 修改 Stored Procedure 程式碼,牽扯版本問題。
    • 需要與 SQL Developer 人員溝通討論。
  • 每次執行 Stored Procedure,僅需 Recompile 指定的 SQL 陳述式,仍有耗用過多資源的顧慮。
  • 以 Execute Frequency(執行頻率) 來考量
      • 不適用於 every second(每秒) 都要執行 或 同時間會大量執行 等情境。
        • 每次都重新編譯,將過度耗用系統資源。
      • 適用於: 年報、季報、月報、周報、日報、小時報或數分鐘報等
        • 如能因重新編譯 Stored Procedure,獲取更佳的執行效能。這筆支出,值得。
  • 而且,OPTION (RECOMPILE) 可以解除 "Local Variables / OPTIMIZE FOR UNKNOWN" 所帶來的副作用,但 sp_recompile  與 WITH RECOMPILE 是無此功效。



01. 建立使用 OPTION RECOMPILE 的 Stored Procedure。


  • 必須修改原來的程式碼。
  • 在指定的 SQL Statement,額外再宣告 OPTION RECOMPILE 來使用。


-- figure 51_ALTER Proc OPTION RECOMPILE






02. 執行 使用 OPTION RECOMPILE 的 Stored Procedure。

分別輸入 NULL 與 43659。


當輸入 NULL:

  • 取回全部資料,回傳 118,744 rows,使用 Clustered Index Scan。


當輸入 43659:

  • 取回指定 SalesOrderID 的資料,回傳 12 rows。
  • 經 Query Optimizer 執行優化後,使用 Index seek + Clustered Index Seek
    • 沒有使用到效率差的 Clustered Index Scan、Index Scan。
  • 這是有提升執行效能的 Execution Plan。
  • 與 WITH RECOMPILE 比較起來,OPTION RECOMPILE 有機會產更有效率的 Execution Plan。

若非 每秒 都要大量執行 或 同時間會大量執行 的程式,能因 OPTION RECOMPILE 重新編譯 Stored Procedure,獲取更佳的執行效能。這筆支出,是值得的。

-- figure 52_EXEC_Proc OPTION RECOMPILE




03. 觀察 Execution Plan:

EXEC GetSalesList_OPTION_RECOMPILE NULL


  • 使用 Clustered Index Scan。
  • Actual Rows: 118,744。
  • Est Rows: 116,322,這是 Estimated Number of Rows。
  • Reads: 2,313,這是 Logical Reads。


EXEC GetSalesList_OPTION_RECOMPILE 43659


  • 僅 Recompile 指定的 SQL Statement,就可以獲得執行效能更佳的 Execution Plan。
  • 使用 Index Seek + Clustered Index Seek
  • Actual Rows: 12。
  • Est Rows: 12,這是 Estimated Number of Rows。
  • Reads: 76,這是 Logical Reads。


-- figure 53_Plan_Explorer







Sample Code

20180821_Parameter_Sniffing_with_OR_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing




Reference

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or.html

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與 動態 SQL)
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-dynamic-sql.html

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (2)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or_23.html

Compare "WITH RECOMPILE / sp_recompile" with "OPTION RECOMPILE"
http://sharedderrick.blogspot.com/2018/02/sql-server-compare-with-recompile-with.html

Parameter Sniffing: Local Variable
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-local.html

Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-option_7.html

Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-option_14.html

2018-08-23

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (2)



延續前一篇文章:[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)


繼續討論 Parameter Sniffing 的 因應之道:

  1. 使用 EXEC ... WITH RECOMPILE
  2. 使用 sp_recompile
  3. 修改 Stored Procedure,使用 Local Variables
  4. 修改 Stored Procedure,使用 WITH RECOMPILE
  5. 修改 Stored Procedure,使用 OPTION RECOMPILE
  6. 改寫 Stored Procedure,使用 UNION ALL
  7. 改寫 Stored Procedure,使用 IF ELSE
  8. 改寫 Stored Procedure,使用 Dynamic SQL: EXEC
  9. 改寫 Stored Procedure,使用 Dynamic SQL: sp_executesql
  10. 因應需求,分別建立 Stored Procedure




Parameter Sniffing with OR Operator issue,
當 Parameter Sniffing 遇上 OR 運算子(2)




1. 使用 EXEC ... WITH RECOMPILE


說明:
  • 無須修改 Stored Procedure 程式碼
  • 執行 stored procedure,搭配 WITH RECOMPILE
    • 不方便。還需要 帶入 User Parameter。
  • 執行當下 Recompile (重新編譯) 此 stored procedure。

注意事項:
  • 單次性的處理。
    • 處理此次 因 Parameter Sniffing 機制,導致重複使用到 效能不佳 的 Execution Plan。
  • 但仍繼續使用 Parameter Sniffing 機制。
    • 若 Input Parameter 仍差異性過大,仍會遭遇重複使用到 效能不佳 的 Execution Plan 。

01. 執行當下 Recompile (重新編譯) 此 stored procedure。

-- figure 11_EXEC_WITH RECOMPILE






02. 觀察 Execution Plan

-- Good: Index Scan + Clustered Index Seek, return 12 rows
EXEC dbo.GetSalesList 43659 WITH RECOMPILE

  • 改用 Index Scan + Clustered Index Seek,Good Execution Plan。


-- figure 12_Good_Index Scan_Clustered Index Seek





03. 使用 Plan Explorer 來觀察


EXEC dbo.GetSalesList 43659 WITH RECOMPILE
  • 改用 Index Scan + Clustered Index Seek,Good Execution Plan。
  • Actual Rows: 12
  • Est Rows: 12,這是 Estimated Number of Rows。
  • Reads: 464,這是 Logical Reads。

與 "dbo.GetSalesList NULL" 對比起來,節省了大量的系統資源,Est Cost 是整體的 10%。

-- figure 13_Plan_Explorer






2. 使用 sp_recompile


說明:
  • 無須修改 Stored Procedure 程式碼。
  • 等到下次執行 stored procedure,才將 Recompile(重新編譯) 此 stored procedure。
  • 使用到此 Table 的各項 SQL 物件:stored procedure, trigger, or user-defined function,都將 Recompile 。
  • 比較起來,不先移除 該 stored procedure 的 Execution Plan。

注意事項:
  • 單次性的處理。
    • 處理此次 因 Parameter Sniffing 機制,導致重複使用到 效能不佳 的 Execution Plan。
  • 但仍繼續使用 Parameter Sniffing 機制。
    • 若 Input Parameter 仍差異性過大,仍會遭遇重複使用到 效能不佳 的 Execution Plan 。

01. 使用 sp_recompile 對 Table 後,再執行 Stored Procedure。


-- figure 21_sp_recompile





02. 使用 Plan Explorer 來觀察

-- Good: Index Scan + Clustered Index Seek, return 12 rows
EXEC sp_recompile 'dbo.GetSalesList'
GO
EXEC dbo.GetSalesList 43659
GO


  • 改用 Index Scan + Clustered Index Seek,Good Execution Plan。
  • Actual Rows: 12
  • Est Rows: 12,這是 Estimated Number of Rows。
  • Reads: 464,這是 Logical Reads。


與 "dbo.GetSalesList NULL" 對比起來,節省了大量的系統資源,Est Cost 是整體的 10%。

-- figure 22_Plan_Explorer






3. 修改 Stored Procedure,使用 "Local Variables / OPTIMIZE FOR UNKNOWN"


說明:
  • 修改 Stored Procedure 程式碼,牽扯版本問題。
    • 需要與 SQL Developer 人員溝通討論。
  • 設定 Query Optimizer 僅使用 Average Distribution (平均分配) 機制來執行 cardinality estimation。
    • 至於輸入什麼 parameter 值,回傳多少筆數等等,設定讓 Query Optimizer 都不要列入考量。
    • 停用 Parameter Sniffing。
  • 評估方式,例如:
    • 使用 equality (等號),則 Query Optimizer 僅使用 density vector 來執行 cardinality estimation,也就是: Rows * All density
    • 使用 Inequality (不相等),則 Query Optimizer 連 density vector 也不用了,就直接設定資料列總數的 30% 來執行 cardinality estimation,也就是: Rows * 30%

注意事項:
    • 停用 Parameter Sniffing 機制。
      • 僅重複使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制產生的 Execution Plan。
      • 堪用,非效能最佳化的 Execution Plan。
    • 看似節省編譯 Execution Plan 的系統資源,卻陷入 執行效能不佳的窘境,甚至耗損更多的 系統資源!
    • 必須修改 SQL 程式碼。
    • 不適用於需求是 高效能 的系統。


01. 建立使用 Local Variable 的 Stored Procedure。

  • 必須修改原來的程式碼。
  • 在 Stored Procedure 內,額外再宣告 Local Variable 來使用。

-- figure 31_ALTER Proc Local Variables_issue






02. 執行 使用 Local Variable 的 Stored Procedure。

分別輸入 NULL 與 43659。


  • 當輸入 NULL:
    • 取回全部資料,回傳 118,744 rows,使用 Clustered Index Scan。


  • 當輸入 43659: 
    • 取回指定 SalesOrderID 的資料,回傳 12 rows,但卻使用 Clustered Index Scan
      • 應該使用 Index Seek
    • 啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 判斷機制,停用 Parameter Sniffing 機制。
    • 看似節省了編譯 Execution Plan 的 CPU 資源,卻陷入 執行效能不佳的窘境,導致耗用更多的系統資源!


-- figure 32_EXEC_Local Variables_issue





03. 觀察: 這 2 份 Execution Plan。


  • 因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 判斷機制
    • 停用 Parameter Sniffing 機制。
    • 兩份完全相同的 Execution Plan。
  • 都是使用  Clustered Index Scan


-- Clustered Index Scan, return 118,744 rows
EXEC GetSalesList_Local_Variable NULL


  • 使用 Clustered Index Scan。
  • Actual Rows: 118,744。
  • Est Rows: 38,387,這是 Estimated Number of Rows,不精確。
    • 因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制所計算出來的回傳筆數,與事實不符。
  • Reads: 2,249,這是 Logical Reads。
  • Est Cost 卻都顯示 50%,這是 Estimated Subtree Cost,不精確,不具備參考價值。


-- Bad: Clustered Index Scan, return 12 rows
EXEC GetSalesList_Local_Variable 43659

  • 卻使用 Clustered Index Scan
  • Actual Rows: 12
  • Est Rows: 38,387,這是 Estimated Number of Rows,與 Actual Rows 差異極大,不精確。
    • 因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制所計算出來的回傳筆數,與事實不符。
  • Reads: 1,562,這是 Logical Reads。
  • Est Cost 卻都顯示 50%,這是 Estimated Subtree Cost,不精確,不具備參考價值。

-- figure 33_Plan_Explorer




進一步觀察 Execution Plan

Operation caused residual IO.
The actual number of rows read was 121,317, but the number of rows returned was 12.

實際僅取回 12 rows,卻因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制,額外讀取 121,317 rows,浪費了大量的 IO 系統資源!

看似節省編譯 Execution Plan 的系統資源,卻陷入 執行效能不佳的窘境,甚至耗損更多的 系統資源!

-- figure 34_Plan_Explorer




身為 SQL Developer 人員


  • 需要去 了解 資料特性、回傳筆數、執行頻率、Index、Statistics 等,選擇適合的因應之道,方能開發出 高效能 的 Stored Procedure。
  • 一昧使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制,或許,節省了開發時程,但卻落得 Stored Procedure 執行效能不佳的窘境!




Sample Code

20180821_Parameter_Sniffing_with_OR_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing




Reference

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or.html

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與 動態 SQL)
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-dynamic-sql.html

Compare "WITH RECOMPILE / sp_recompile" with "OPTION RECOMPILE"
http://sharedderrick.blogspot.com/2018/02/sql-server-compare-with-recompile-with.html

Parameter Sniffing: Local Variable
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-local.html

2018-08-22

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)


在先前的文章裡,陸續介紹過 Parameter Sniffing 的基本概念。本文以 Case Study (案例分享) 方式來綜合討論。


Parameter Sniffing
  • 優點:藉由重複使用 Execution Plan 的機制,來節省編譯 Execution Plan 資源 的耗損。
  • 缺點:卻存在著誤用 Execution Plan,導致效能更糟的問題。
  • 看似節省編譯 Execution Plan 的 CPU 資源,卻掉入效能不佳的窘境。


OR 運算子
  • 可篩選多值,延展條件式的功能,十分彈性,但卻潛藏效能不佳的問題。


當兩者結合,遭遇到效能不佳的困境時,該如何解決呢?


本文彙整 10 種 Parameter Sniffing 的 因應之道:
  1. 使用 EXEC ... WITH RECOMPILE
  2. 使用 sp_recompile
  3. 修改 Stored Procedure,使用 "Local Variables / OPTIMIZE FOR UNKNOWN"
  4. 修改 Stored Procedure,使用 WITH RECOMPILE
  5. 修改 Stored Procedure,使用 OPTION RECOMPILE
  6. 改寫 Stored Procedure,使用 UNION ALL
  7. 改寫 Stored Procedure,使用 IF ELSE
  8. 改寫 Stored Procedure,使用 Dynamic SQL: EXEC
  9. 改寫 Stored Procedure,使用 Dynamic SQL: sp_executesql
  10. 因應需求,分別建立 individual Stored Procedure

想知道之間的區別、應用時機 與 優缺點嗎?




本系列文章

Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)

Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (2)

Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (3)

Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (4)

Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (5)




Parameter Sniffing with OR Operator issue,
當 Parameter Sniffing 遇上 OR 運算子(1)



01. 建立 Stored Procedure: dbo.GetSalesList


條件式:  WHERE (@SalesOrderID IS NULL OR sd.SalesOrderID = @SalesOrderID)
  • @SalesOrderID IS NULL: 當 @SalesOrderID 是 NULL 時,取回全部的資料,回傳 118,744 rows。
  • sd.SalesOrderID = @SalesOrderID: 取回 指定的 SalesOrderID 的 資料,回傳 12 rows。

以 Index 使用策略來看:

  • 這兩個條件式,應該是使用不同的 Index 存取方式,例如:Index Scan、Index Seek 等。
  • 但透過 OR 運算子,整合在同一個 Stored Procedure,會遇到什麼效能上的難題呢?


-- figure 01_Parameter Sniffing with OR issue





02. 執行此 Stored Procedure: dbo.GetSalesList。

分別輸入 NULL 與 43659。

  • 當輸入 NULL:
    • 取回全部資料,回傳 118,744 rows,使用 Clustered Index Scan。
  • 當輸入 43659: 
    • 取回指定 SalesOrderID 的資料,回傳 12 rows,但卻仍是使用 Clustered Index Scan
    • 由於 Parameter Sniffing 發揮功效,重複使用 Execution Plan。
    • 雖然節省了編譯 Execution Plan 的 CPU 資源,但卻因故誤用效能不佳的 Bad Execution Plan,導致耗用更多的系統資源!


-- figure 02_EXEC_Parameter Sniffing with OR issue





03. 觀察: 這 2 隻 Stored Procedure 的 Execution Plan。


  • 由於 Parameter Sniffing 發揮功效,重複使用 Execution Plan。
  • 都是使用 Clustered Index Scan


-- figure 03_Clustered Index Scan





04. 綜合觀察


EXEC dbo.GetSalesList NULL

  • 使用 Clustered Index Scan。
  • Actual Rows: 118,744。
  • Est Rows: 116,322,這是 Estimated Number of Rows。
  • Reads: 2,335,這是 Logical Reads。


EXEC dbo.GetSalesList 43659
  • 仍是使用 Clustered Index Scan
  • Actual Rows: 12
  • Est Rows: 116,322,這是 Estimated Number of Rows。
  • Reads: 1,564,這是 Logical Reads。

這就是錯誤的重複使用 Execution Plan 後,所導致的效能低落問題。
看似節省編譯 Execution Plan 的 CPU 資源,卻掉入效能不佳的窘境。

下圖的 Execution Plan 是屬於 EXEC dbo.GetSalesList NULL。

-- figure 04_Plan_Explorer





05. 下圖的 Execution Plan 是屬於 EXEC dbo.GetSalesList 43659。

-- figure 05_Plan_Explorer






Sample Code

20180821_Parameter_Sniffing_with_OR_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing




Reference

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與 動態 SQL)
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-dynamic-sql.html

Compare "WITH RECOMPILE / sp_recompile" with "OPTION RECOMPILE"
http://sharedderrick.blogspot.com/2018/02/sql-server-compare-with-recompile-with.html

2018-08-18

[SQL Server] Disabled Index still update Statistics, 停用索引 仍會更新 統計值


在維護大型系統上,需要定期評估 Index 適用性。
若判斷無須使用該 Index,則執行 Disabled Index 與進一步觀察對系統的影響程度。

提醒

  • 若經確認無需使用該 Index 後,應移除該 Index。


Disabled Index 會防止使用者存取 Index,Query Optimizer 不使用此 Index。但 SQL Server 仍會維護與更新該 Index 的統計值。


Disabled Index 仍會更新 Statistics






Disabled Index still update Statistics, 停用索引 仍會更新 統計值




01. 使用 sp_helpindex 觀察資料表 [Person].[Address] 的 Index 資訊

-- figure 01_Index





02. 使用 DMV 與 STATS_DATE 觀察 全部資料表的 Index 與 Statistics 資訊

Get all indexes and Statistics last updated datetime

-- figure 02_Get all indexes and Statistics last updated datetime





03. 停用 Index

  • Disable Nonclustered Index
  • IX_Address_StateProvinceID


-- figure 11_Disable Nonclustered Index






04. 觀察 資料表的 Index 與 Statistics 資訊


  • 確認 IX_Address_StateProvinceID 已經被 Disabled


-- figure 12_Get all indexes and Statistics last updated datetime





05. 更新 Statistics

Update the Statistics of table

-- figure 21_Update the Statistics of table





06. 觀察 Statistics 資訊


  • 確認 Statistics 已經被更新
  • 即便該 Index 早已被 Disabled


-- figure 22_Get all indexes and Statistics last updated datetime





07. 使用 DBCC SHOW_STATISTICS 觀察 Statistics


  • 確認已經被更新


-- figure 31_DBCC SHOW_STATISTICS





08. 使用 DMV: dm_db_stats_properties 觀察 Statistics

  • 確認已經被更新

-- figure 32_dm_db_stats_properties





Disabled Index 會防止使用者存取 Index,Query Optimizer 不使用此 Index。
但 SQL Server 仍會維護與更新該 Index 的統計值。


Disabled Index


  • Disabled Index 會防止使用者存取 Index ,而 Disabled Clustered Index 則會防止存取基礎資料表資料。 
  • 索引定義會保留在中繼資料內,而 index statistics 資料會保留在 Nonclustered Indexes上。 
  • 停用 View 上的 非叢集 或 叢集索引,實際上會刪除 Index Data。 
  • Disabled Clustered Index,則會防止存取資料;這些資料仍留在資料表中,但無法用於資料操作語言 (DML) 作業,除非 卸除 或 重建索引。





Sample Code

20180818_Disabled_Index_Update_Statistics
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing





Reference

Disable Indexes and Constraints
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints?view=sql-server-2017

STATS_DATE (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/functions/stats-date-transact-sql?view=sql-server-2017