德瑞克：SQL Server 學習筆記: [SQL Server] Parameter Sniffing: Local Variable

2018-01-17

[SQL Server] Parameter Sniffing: Local Variable

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

使用 "Local Variables / 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 建議也不見了!

"Local Variables / OPTIMIZE FOR UNKNOWN" 這兩者是相同功能。

先前，若要停用 Parameter Sniffing ，要對每一個 Input Parameter，額外再多宣告對應出 Local Parameter 來承接。
在 SQL Server 2008 可以改用 OPTIMIZE FOR UNKNOWN，這是 SQL Statement 陳述式層級方式來停用 Parameter Sniffing。

使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 建立 Reuse Execution Plan 是個好方法嗎？

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

評估方式
"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% 。

試想：

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

Compare Match and Not match Execution Plan

以 Local Variables / 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 建議卻不見了!

Local Variable and Equality Operator

01. 建立 Stored Procedure: up_Parameter_Local_Variable

Use Input Parameter
Use Local Variable
Use Equality Operator

SQL 開發人員為了要停用 Parameter Sniffing，設定 Query Optimizer 僅使用 Average Distribution (平均分配) 機制來執行 Cardinality Estimation。
至於輸入什麼 parameter，回傳多少筆數，讓 Query Optimizer 都不要列入考量。

要達成停用 Parameter Sniffing 方法是：

額外再宣告 Local Variable。
承接來自於 Input Parameter 的值
設定條件式僅使用 Local Variable。

-- figure 01_Local Variable

02. 設定 Input Parameter: @StateProvinceID= 32，執行此 Stored Procedure。

Input Parameter: StateProvinceID= 32
Use Local Parameter

Actual Number of Rows 是: 1。
Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
但評估 Estimate Number of Rows 卻是: 265.054 ?

Local Variable 與 OPTIMIZE FOR UNKNOWN 兩者是一樣的功能，有著相同的誤用問題!

-- figure 02_Local Variable_StateProvinceID_32

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

StatementEstRows="265.054"
OptimizeStatsUsage

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

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

-- figure 03_OptimizeStatsUsage

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

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

-- figure 04_XML_OptimizeStatsUsage

Changing Parameter Values @StateProvinceID = 9

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

Input Parameter: StateProvinceID= 9
Use Local Parameter

改採 Use Local Parameter方式來建立 Execution Plan 後，當 StateProvinceID=9，竟然沒有原本該有的 Missing Index 建議。

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

-- figure 11_Local Variable_StateProvinceID_9

後續的相關測試，請參考先前的文章：

[SQL Server] Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2

"Local Variables / OPTIMIZE FOR UNKNOWN" 這兩者是相同功能，有著相同的誤用問題!

Local Variable and Inequality Operator

Use Inequality Operators, 使用不相等

01. 建立新的 Stored Procedure: up_Parameter_Local_Variable_Inequality，使用 Inequality Operators。