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

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

已知道 "OPTIMIZE FOR VALUE" 是：

指示 Query Optimizer 使用事先自訂的特定值來建立 Reuse 的 Execution Plan，節省 recompile 所耗用的系統資源。
減輕因 "先建續用" 的 Reuse Execution Plan 機制，所產生的效能不佳問題。

UNKNOWN 一詞，在 Oxford Dictionaries 解釋：

NOUN: An unknown person or thing.
ADJECTIVE: Not known or familiar.

那 "OPTIMIZE FOR UNKNOWN" 是什麼？

OPTIMIZE FOR UNKNOWN

OPTIMIZE FOR UNKNOWN 是

指示 Query Optimizer 使用 Statistical Data (而非 Initial Value)，來取代 Query Optimization 階段的 Local Variable 之值。這包含：使用 Forced Parameterization (強制參數化) 所建立的參數。
Query Optimizer 不使用 Parameter Sniffing，不使用 Histogram(長條圖)，僅使用 Density Vector(密度向量) 來執行評估 Cardinality Estimation(CE, 基數預估)。
Query Optimizer 使用 Cardinality Estimation，來產生最佳化的 Execution Plan。

但 OPTIMIZE FOR UNKNOWN 卻可能讓 Query Optimizer 產生效能不佳的 Execution Plan。

建立 Reuse 的 Execution Plan，節省 recompile 所耗用的系統資源。

"UNKNOWN" 字面上的意義容易理解，但 "OPTIMIZE FOR UNKNOWN" 實際運作的方式，是需要進一步討論的。

但使用 OPTIMIZE FOR UNKNOWN 建立 Reuse Execution Plan 是個好方法嗎？

有提高執行效能？
如何評估 Index 呢？

Compare

以 OPTIMIZE FOR UNKNOWN 為例:

若 @StateProvinceID= 32

Not match Execution Plan, Bad
理想是 Logical Reads: 6，但卻因故誤用，而耗用到 218，差距 36.33 倍。
原本是 CPU Time(sec): 0.000448000，但卻因故誤用，而耗用到 0.001645000，差距 3.67 倍。

若 @StateProvinceID= 9

算是 Match Execution Plan
但該有的 Missing Index 建議卻不見了!

將後續文章進一步討論 OPTIMIZE FOR UNKNOWN。

OPTIMIZE FOR VALUE/UNKNOWN

總括來說，使用 "OPTIMIZE FOR VALUE/UNKNOWN" 是

指示 Query Optimizer 在執行 Compile 與 Query Optimization 期間，使用 "OPTIMIZE FOR VALUE/UNKNOWN" 抵銷 Query Optimizer 預設的 Parameter Detection 行為。
Query Optimizer 不使用預設的 Parameter Detection 方式來執行 Query Optimization，改用 "OPTIMIZE FOR VALUE/UNKNOWN" 所指示的方式來執行 Query Optimization。
只有在 Query Optimization 階段才使用這些方法的值，來建立 Execution Plan，而不是在 Query Execution 階段。

OPTION OPTIMIZE FOR UNKNOWN

01. 建立 Stored Procedure: up_Parameter_OPTION_OPTIMIZE_FOR_UNKNOWN

Use OPTION (OPTIMIZE FOR UNKNOWN);

指示 Query Optimizer 使用 Statistical Data (而非 Initial Value)，來取代 Query Optimization 階段的 Local Variable 之值。這包含：使用 Forced Parameterization (強制參數化) 所建立的參數。
Query Optimizer 不使用 Parameter Sniffing，不使用 Histogram(長條圖)，僅使用 Density Vector(密度向量) 來執行評估 Cardinality Estimation(CE, 基數預估)。
Query Optimizer 使用 Cardinality Estimation，來產生最佳化的 Execution Plan。

但 OPTIMIZE FOR UNKNOWN 卻可能讓 Query Optimizer 產生效能不佳的 Execution Plan。

建立 Reuse 的 Execution Plan，節省 recompile 所耗用的系統資源。

-- figure 01_Create_Proc_OPTION (OPTIMIZE FOR UNKNOWN)

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

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

沒有使用到 Good Execution Plan

-- figure 02_StateProvinceID_32_OPTION (OPTIMIZE FOR UNKNOWN)

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

使用 XQuery 查詢 Execution Plan 的 Estimate Number of Rows。

-- figure 03_Get last performance statistics

實際回傳的資料列筆數是: 1。
但評估 Estimate Number of Rows 卻是: 265.054 ?

-- figure 04_Get last performance statistics

04. 由 SSMS 的 GUI 介面，觀察 Execution Plan 的 Estimate Number of Rows 也是: 265.054 ?

-- figure 05_Execution_Plan_EstimatedNumberofRows

-- figure 0502_Actual_Estimate_Rows

Number of Rows Read: 19614
Actual Number of Rows: 1
Estimated Number of Rows: 265.054
Estimated Number of Rows to be Read: 19614

-- figure 0503_Actual_Number_Estimated_Rows

Actual Number of Rows: 1
Number of Rows Read: 19614
Estimated Number of Rows: 265.054

05. 由 SSMS 的 GUI 介面，觀察 Execution Plan: [Parameter List] and [Estimate Number of Rows]

[Parameter List], [Parameter Runtime Value]: (32)
[Estimate Number of Rows]: 265.054

-- figure 06_Execution_Plan_EstimatedNumberofRows_[OptimizeStatsUsage]_Statistics

Summary

Use OPTION (OPTIMIZE FOR UNKNOWN);

Only use statistical data for during query optimization.
Only use statistical data instead of the initial value.
Specifies that the query optimizer use statistical data instead of the initial value to determine the value for a local variable during query optimization.
Middle value, mediocre for Execution Plan

@StateProvinceID= 32

Not match Execution Plan, Bad
retun 1 row
Index Scan - [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
LastElapsedTime(sec): 0.006785000
LastCPUTime(sec)]: 0.001645000
LastRows: 1
EstimatedNumberofRows: 265.054
Last_logical_reads: 218
[OptimizeStatsUsage], [Statistics]: [IX_Address_StateProvinceID]

Changing Parameter Values @StateProvinceID = 9

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

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

改採 OPTION (OPTIMIZE FOR UNKNOWN) 方式來建立 Execution Plan 後，當 StateProvinceID=9，竟然沒有原本該有的 Missing Index 建議。

-- figure 11_StateProvinceID_9_OPTION (OPTIMIZE FOR UNKNOWN)

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

實際回傳的資料列筆數是: 4,564。
但評估 Estimate Number of Rows 卻是: 265.054 ?

-- figure 12_Get last performance statistics

03. 由 SSMS 的 GUI 介面，觀察 Execution Plan 的 Estimate Number of Rows 也是: 265.054 ?

-- figure 13_Execution_Plan_EstimatedNumberofRows

-- figure 1302_Actual_Estimate_Rows

Number of Rows Read: 19614
Actual Number of Rows: 4564
Estimated Number of Rows: 265.054
Estimated Number of Rows to be Read: 19614

-- figure 1303_Actual_Number_Estimated_Rows

Actual Number of Rows: 4564
Number of Rows Read: 19614
Estimated Number of Rows: 265.054

04. 由 SSMS 的 GUI 介面，觀察 Execution Plan: [Parameter List] and [Estimate Number of Rows]

[Parameter List], [Parameter Runtime Value]: (9)
[Estimate Number of Rows]: 265.054

-- figure 14_Execution_Plan_EstimatedNumberofRows_[OptimizeStatsUsage]_Statistics

Summary

Use OPTION (OPTIMIZE FOR UNKNOWN);

Only use statistical data for during query optimization.
Only use statistical data instead of the initial value.
Specifies that the query optimizer use statistical data instead of the initial value to determine the value for a local variable during query optimization.

@StateProvinceID= 9

Match Execution Plan, But unexpected issue: without missing index
retun 4564 row
Index Scan - [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
LastElapsedTime(sec): 0.118544000
LastCPUTime(sec)]: 0.006451000
LastRows: 4564
EstimatedNumberofRows: 265.054
Last_logical_reads: 218
[OptimizeStatsUsage], [Statistics]: [IX_Address_StateProvinceID]