德瑞克：SQL Server 學習筆記: 2018/08

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 的因應之道：

使用 EXEC ... WITH RECOMPILE
使用 sp_recompile
修改 Stored Procedure，使用 Local Variables
修改 Stored Procedure，使用 WITH RECOMPILE
修改 Stored Procedure，使用 OPTION RECOMPILE
改寫 Stored Procedure，使用 UNION ALL
改寫 Stored Procedure，使用 IF ELSE
改寫 Stored Procedure，使用 Dynamic SQL: EXEC
修改 Stored Procedure，使用 Dynamic SQL: sp_executesql
因應需求，分別建立 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

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。

使用 sp_recompile，重新編譯。
輸入參數 NULL。

因 Parameter Sniffing 發揮功效，重複使用 Execution Plan。
誤用到效能差的 Execution Plan: Clustered Index Scan。
輸入參數 43659。

再度使用 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	17	0.181764000	0.066875000	1564	12
3rd	0.5626740	6	0.123931000	0.010333000	398	12

-- 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

http://sharedderrick.blogspot.com/2017/12/sql-server-get-subtreecost-compiletime.html

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.

顯示 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 的因應之道：

使用 EXEC ... WITH RECOMPILE
使用 sp_recompile
修改 Stored Procedure，使用 Local Variables
修改 Stored Procedure，使用 WITH RECOMPILE
修改 Stored Procedure，使用 OPTION RECOMPILE
改寫 Stored Procedure，使用 UNION ALL
改寫 Stored Procedure，使用 IF ELSE
改寫 Stored Procedure，使用 Dynamic SQL: EXEC
改寫 Stored Procedure，使用 Dynamic SQL: sp_executesql
因應需求，分別建立 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

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 的因應之道：

使用 EXEC ... WITH RECOMPILE
使用 sp_recompile
修改 Stored Procedure，使用 Local Variables
修改 Stored Procedure，使用 WITH RECOMPILE
修改 Stored Procedure，使用 OPTION RECOMPILE
改寫 Stored Procedure，使用 UNION ALL
改寫 Stored Procedure，使用 IF ELSE
改寫 Stored Procedure，使用 Dynamic SQL: EXEC
改寫 Stored Procedure，使用 Dynamic SQL: sp_executesql
因應需求，分別建立 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

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-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 的因應之道：

使用 EXEC ... WITH RECOMPILE
使用 sp_recompile
修改 Stored Procedure，使用 "Local Variables / OPTIMIZE FOR UNKNOWN"
修改 Stored Procedure，使用 WITH RECOMPILE
修改 Stored Procedure，使用 OPTION RECOMPILE
改寫 Stored Procedure，使用 UNION ALL
改寫 Stored Procedure，使用 IF ELSE
改寫 Stored Procedure，使用 Dynamic SQL: EXEC
改寫 Stored Procedure，使用 Dynamic SQL: sp_executesql
因應需求，分別建立 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