德瑞克：SQL Server 學習筆記: [SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2

延續前文：[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN

使用 OPTIMIZE FOR UNKNOWN

如果無法識別出常用的 parameter 值，或是 Query Optimizer 使用此 parameter 值所 compile 的 Execution Plan 效率不佳(例如：由於 data skew 數據傾斜)。

SQL 開發人員設定 Query Optimizer 僅使用 Average Distribution (平均分配) 機制來執行 cardinality estimation。

至於輸入什麼 parameter，回傳多少筆數，讓 Query Optimizer 都不要列入考量，也不使用 Parameter Sniffing。
在此次範例中，甚至連該有的 Missing Index 建議也不見了!

評估方式
OPTIMIZE FOR UNKNOWN / Local variables

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

試想：

這種方式建立出來的 Execution Plan 會有好的效能嗎？
這會是效能最佳化的 Execution Plan 嗎？

OPTION OPTIMIZE FOR UNKNOWN 2

延續前文：[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN

01. 執行 Stored Procedure，輸入 Parameter: @StateProvinceID= 32

Input Parameter: StateProvinceID= 32
Use OPTION (OPTIMIZE FOR UNKNOWN);

Actual Number of Rows 是: 1。
但評估 Estimate Number of Rows 卻是: 265.054 ?

Why is "Estimated Number of Rows" get 265.054 ?

-- figure 01_Why is "Estimated Number of Rows" get 265.054 ?

02. 由 SSMS 的 GUI 介面，觀察 Execution Plan

StatementEstRows="265.054"
OptimizeStatsUsage

在 OptimizeStatsUsage，可以看是 Statistics: [IX_Address_StateProvinceID]。

也就是說，Query Optimizer 是使用 Index: [IX_Address_StateProvinceID] 的 statistics (統計資料)，執行 Query Optimization。

-- figure 11_OptimizeStatsUsage

03. Show Execution Plan XML，可以到相關的資訊。

-- figure 12_XML_OptimizeStatsUsage

04. 使用 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 (長條圖)

Header (標頭)

有關 statistics 的 metadata 。

Histogram (長條圖)

顯示 first key column (第一個資料行) 中的 distribution of values (值分佈狀態)。

Density Vector (密度向量)

用來測量 cross-column correlation (跨資料行關聯)。
Density 是資料行或組合資料行中的 duplicates 重複項目數量之資訊，其計算方式為 1/(distinct values 數目)。

-- figure 13_Displays Query Optimization statistics

05. 觀察 DBCC SHOW_STATISTICS 所顯示的資料。

在 Header(標頭)

Rows: 19614

在 Density Vector (密度向量)

All density: 0.01351351

Density 是資料行或組合資料行中的 duplicates 重複項目數量之資訊，其計算方式為 1/(distinct values 數目)。

StateProvinceID 的 All density: 0.01351351，可以使用以下方式檢驗：

-- figure 13_01_All density