德瑞克：SQL Server 學習筆記: [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"

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