搜尋本站文章

2018-01-20

[SQL Server] Ad hoc Query: Local Variable and Without Local Variable




延續前文:[SQL Server] Parameter Sniffing: Local Variable


來個有趣的問題,給 SQL Server Developer:
以下這兩段 SQL Query,有何不同?

What's the difference between 01 and 02?


-- figure 01_Whats the difference between 01 and 02










大相徑庭 的 Execution Plan

  • 即便回傳筆數相同
  • 但 02. 使用 Local Variable


-- figure 02_Execution_Plan


換成是 Ad hoc Query,也受到 Local Variables / OPTIMIZE FOR UNKNOWN 的影響,停用 Parameter Sniffing 。


評估方式
"Local Variables / OPTIMIZE FOR UNKNOWN" 

ConditionsQuery Optimizer only use
equality: =Rows * All density
inequality: >  ,  < , etc., Rows * 30%



當條件式是:
  • 使用 equality (等號),則 Query Optimizer 僅使用 density vector 來執行 Cardinality Estimation,也就是: Rows * All density
  • 使用 Inequality (不相等),則 Query Optimizer 連 density vector 也不用了,就直接設定資料列總數的 30% 來執行 Cardinality Estimation,也就是: Rows * 30% 。


Compare Match and Not match Execution Plan


以 Local Variables / OPTIMIZE FOR UNKNOWN 為例:


若  ProductID  = 897


  • Not match Execution Plan, Bad
  • 理想是 Logical Reads: 14,若改用 Local Variable,耗用增加到 1246,差距 89 倍。
  • 原本是 CPU Time(sec): 0.000488000,若改用 Local Variable,耗用增加到 0.011253000,差距 23 倍。





Ad hoc Query: Local Variable and Without Local Variable


01. 檢視:資料表 Sales.SalesOrderDetail 與 Index 資訊。


  • 資料表總筆數是:121,317 rows


-- figure 01_Get Table and Index Information




02. 檢視 依據 ProductID 分組資料


  • ProductID = 897,  return 2 rows.
  • ProductID = 870,  return 4688 rows.


-- figure 02_Query Data by ProductID




03. 執行查詢 WHERE ProductID = 897,檢視 Execution Plan



WHERE ProductID = 897
  • Good Execution Plan


  • return 2 row
  • Index Seek: [IX_SalesOrderDetail_ProductID]


  • Number of Rows Read: 2
  • Actual Number of Rows: 2
  • Estimated  Number of Rows: 75.6667
  • Estimated Number of Rows to be Read: 75.6667


-- figure 11_ProductID_897_Execution_Plan




Actual Number of Rows: 2
Number of Rows Read: 2
Estimated  Number of Rows: 75.6667


-- figure 12_Estimated  Number of Rows



04. 觀察 效能 STATISTICS  統計資訊: CPU, Disk IO


  • LastElapsedTime(sec): 0.073422000
  • LastCPUTime(sec)]: 0.000488000
  • LastRows: 2
  • EstimatedNumberofRows: 75.6667
  • Last_logical_reads: 14


-- figure 13_Performance_Statistics






Ad hoc Query with Local Variable


01. 宣告 DECLARE @ProductID INT = 897:


  • WHERE ProductID = @ProductID
  • Use Local Parameter


觀察 Execution Plan:

@ProductID INT = 897

  • Not match Execution Plan, Bad


  • return 2 row
  • Clustered Index Scan: [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
  • Suggestion: Missing index ???
  • Number of Rows Read: 121317
  • Actual Number of Rows: 2
  • Estimated  Number of Rows: 456.079
  • Estimated Number of Rows to be Read: 121317


-- figure 21_Ad hoc Query with Local Variable





  • Actual Number of Rows: 2
  • Number of Rows Read: 121317
  • Estimated  Number of Rows: 456.079


-- figure 22_Estimated  Number of Rows





02. 觀察 效能 STATISTICS  統計資訊: CPU, Disk IO


  • LastElapsedTime(sec): 0.028126000
  • LastCPUTime(sec)]: 0.011253000
  • LastRows: 2
  • EstimatedNumberofRows: 456.079
  • Last_logical_reads: 1246


-- figure 23_Performance_Statistics







Why does Estimated  Number of Rows get 456.079 ?


為什麼是使用 EstimatedNumberofRows: 456.079 來評估建立 Execution Plan 呢?


01. Show Execution Plan XML,可以到相關的資訊。


-- figure 31_Show Execution Plan XML



02. 使用 DBCC SHOW_STATISTICS,顯示目前的 query optimization 的 statistics。


  • Query Optimizer 使用 statistics 來預估 Cardinality (基數) 或 查詢結果中的資料列數,如此可讓 Query Optimizer 建立高品質的 Execution Plan。
  • 舉例來說,Query Optimizer 可使用 Cardinality Estimates (CE) 估來選擇 Execution Plan 中的 index seek operator,而不是 index scan operator,避免發生 resource-intensive index scan 來提高查詢效能。


Query Optimizer 使用 statistics 物件中的相關資料來計算 Cardinality Estimates (CE) 。

DBCC SHOW_STATISTICS 可以顯示儲存在 statistics 物件中的資料,包含:


  • Header (標頭)
  • Density Vector (密度向量)
  • Histogram (長條圖)

請參考 [SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2
http://sharedderrick.blogspot.tw/2018/01/sql-server-parameter-sniffing-option_14.html



觀察 DBCC SHOW_STATISTICS 所顯示的資料。


  • 在 Header(標頭)
    • Rows: 121317
  • 在 Density Vector (密度向量)
    • All density: 0.0.003759399


-- figure 41_Displays Query Optimization statistics





03. 謎底揭曉:


  • 0.003759399 * 121317 = 456.079008483,約等於 456.079


-- figure 51_All density






當 Ad hoc Query with Local Variable and Equality Operator :


  • 使用 equality (等號),則 Query Optimizer 僅使用 density vector 來執行 cardinality estimation,也就是: Rows * All density




Ad hoc Query with Local Variable and Inequality Operator


Ad hoc Query and Inequality Operator

Use Inequality Operators, 使用 不相等

01. 執行查詢 WHERE ProductID > 897,檢視 Execution Plan

WHERE ProductID > 9999

  • Good Execution Plan


  • return 0 row
  • Index Seek: [IX_SalesOrderDetail_ProductID]
  • Number of Rows Read: 0
  • Actual Number of Rows: 0
  • Estimated  Number of Rows: 1
  • Estimated Number of Rows to be Read: 1


-- figure 61_Ad hoc Query and Inequality Operator





  • Actual Number of Rows: 0
  • Number of Rows Read: 1


-- figure 62_Estimated  Number of Rows



03. 觀察 效能 STATISTICS  統計資訊: CPU, Disk IO


  • LastElapsedTime(sec): 0.022990000
  • LastCPUTime(sec)]: 0.000421000
  • LastRows: 0
  • EstimatedNumberofRows: 1
  • Last_logical_reads: 2


-- figure 63_Performance_Statistics







Ad hoc Query with Local Variable and Inequality Operator


01. 宣告 DECLARE @ProductID INT > 9999:


  • WHERE ProductID > @ProductID
  • Use Local Parameter
  • Use Inequality Operator


觀察 Execution Plan:

WHERE ProductID > @ProductID

  • Not match Execution Plan, Bad
  • return 0 row
  • Clustered Index Scan: [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
  • Number of Rows Read: 121317
  • Actual Number of Rows: 0
  • Estimated  Number of Rows: 36395.1
  • Estimated Number of Rows to be Read: 121317


-- figure 71_Ad hoc Query with Local Variable and Inequality Operator





  • Actual Number of Rows: 0
  • Number of Rows Read: 121317
  • Estimated  Number of Rows: 36395.1


-- figure 72_Estimated  Number of Rows





02. 觀察 效能 STATISTICS  統計資訊: CPU, Disk IO


  • LastElapsedTime(sec): 0.031641000
  • LastCPUTime(sec)]: 0.009111000
  • LastRows: 0
  • EstimatedNumberofRows: 36395.1
  • Last_logical_reads: 1246


-- figure 73_Performance_Statistics




為什麼是使用 EstimatedNumberofRows: 36395.1 來評估建立 Execution Plan 呢?


03. 觀察 DBCC SHOW_STATISTICS 所顯示的資料


  • 在 Header(標頭)
  • Rows: 121317


-- figure 81_Displays Query Optimization statistics




-- 04. 謎底就是:


  • Inequality: EstimatedNumberofRows is 30% of the rows
  • 121317 * 0.3 = 36395.1


-- figure 82_EstimatedNumberofRows is 30




當 Ad hoc Query with Local Variable and Inequality Operator


  • Query Optimizer 一律都 僅 使用 Rows * 30% 方式來評估


也就是說,輸入什麼 parameter,回傳多少筆數,Query Optimizer 都不列入考量。




Sample Code

20180120_Ad hoc Query_Local Variable
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing




參考資料

[SQL Server] Parameter Sniffing: Local Variable
http://sharedderrick.blogspot.tw/2018/01/sql-server-parameter-sniffing-local.html

沒有留言:

張貼留言