搜尋本站文章

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" 

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%

試想:

  • 這種方式建立出來的 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。


  • Use Local Parameter
  • Use < (Less Than) Operators


-- figure 21_inequality





02. 執行 Stored Procedure,輸入 Parameter: @StateProvinceID= 2


  • Number of Rows Read: 19614
  • Actual Number of Rows: 25
  • Estimated  Number of Rows: 5884.2
  • Estimated Number of Rows to be Read: 19614


Use Local Parameter,使用 < (Less Than)


  • return 25 rows
  • 但為什麼  EstimatedNumberofRows 卻是 5884.2 ?


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


-- figure 31_inequality_StateProvinceID_2_Execution_Plan





03. 再度更換 Parameter: @StateProvinceID= 181


  • Number of Rows Read: 19614
  • Actual Number of Rows: 19583
  • Estimated  Number of Rows: 5884.2
  • Estimated Number of Rows to be Read: 19614


Use Local Parameter,使用 < (Less Than)


  • return 19583 rows
  • 但為什麼  EstimatedNumberofRows 卻是 5884.2 ?


怎麼又是使用 EstimatedNumberofRows: 5884.2 來評估建立 Execution Plan 呢?

-- figure  41_inequality_StateProvinceID_181_Execution_Plan




後續的相關測試,請參考先前的文章:

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

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




Sample Code

20180116_Parameter_Sniffing_Local_Variable
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing



參考資料

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

2018-01-14

[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




謎底揭曉:

  • 0.01351351 * 19614 = 265.05398514,約等於 265.054


-- figure 14_Rows_Density




由上得知,當使用 OPTION (OPTIMIZE FOR UNKNOWN) :

  • 且 條件式 使用 equality (等號,  )
  • Query Optimizer 一律都  使用 Rows * All density 方式來評估

也就是說,輸入什麼 parameter,回傳多少筆數,Query Optimizer 都不列入考量。
還記得嗎?在本次範例中,連該有的 Missing Index 建議也不見了!

試想:
  • 這種方式建立出來的 Execution Plan 會有好的效能嗎?
  • 這會是 效能最佳化 的 Execution Plan 嗎?


06. 在 Histogram (長條圖),接近 265.054 rows 的是:

  • RANGE_HI_KEY: 66
  • EQ_ROWS:  242

也就是
  • StateProvinceID: 66
  • Return: 242 rows


-- figure 17_RANGE_HI_KEY = 66




07. 改用 Ad hoc Query,條件式 StateProvinceID= 66:


  • Return: 242 rows
  • Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] 


-- figure 21_StateProvinceID_66_Execution_Plan




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

  • Number of Rows Read: 19614
  • Actual Number of Rows: 242
  • Estimated  Number of Rows: 242
  • Estimated Number of Rows to be Read: 19614
  • LastElapsedTime(sec): 0.001405000
  • LastCPUTime(sec)]: 0.001404000
  • LastRows: 242
  • EstimatedNumberofRows: 242
  • Last_logical_reads: 216


-- figure 22_Performance_Statistics




Change condition: StateProvinceID = 163


01. 改用 Ad hoc Query,條件式 StateProvinceID= 163

  • Return: 62 rows
  • Index Seek: [IX_Address_StateProvinceID]

當 StateProvinceID = 163,Query Optimizer 評估使用 Index Seek - Good Execution Plan.

-- figure 23_StateProvinceID_163_Execution_Plan






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

  • Number of Rows Read: 62
  • Actual Number of Rows: 62
  • Estimated  Number of Rows: 62
  • Estimated Number of Rows to be Read: 62
  • LastElapsedTime(sec): 0.066149000
  • LastCPUTime(sec)]: 0.000820000
  • LastRows: 62
  • EstimatedNumberofRows: 62
  • Last_logical_reads: 150

-- figure 24_Performance_Statistics




03. 使用 SQL 查詢式,觀察 StateProvinceID: 66

  • RowCount: 242

-- figure 31_GROUP BY StateProvinceID_66





04. 使用 SQL 查詢式,觀察 StateProvinceID: 163

  • RowCount: 62

-- figure 32_GROUP BY StateProvinceID_163




Return 74 group

  • There are 53 categories of StateProvinceID to use use Index Seek
  • There are 21 categories of StateProvinceID to use use Index Scan





Use Inequality Operators, 使用 不相等



  • Equality Operators (=) 
  • Inequality Operators 是指使用:> (Greater Than)  ,  < (Less Than) 等等。


01. 建立新的 Stored Procedure: up_OPTION_OPTIMIZE_FOR_UNKNOWN_Inequality,使用 Inequality Operators。

  • OPTION (OPTIMIZE FOR UNKNOWN);
  • Use < (Less Than) Operators


-- figure 41_inequality





02. 執行 Stored Procedure,輸入 Parameter: @StateProvinceID= 2


  • Number of Rows Read: 19614
  • Actual Number of Rows: 25
  • Estimated  Number of Rows: 5884.2
  • Estimated Number of Rows to be Read: 19614


Use OPTION (OPTIMIZE FOR UNKNOWN),使用 < (Less Than)

  • return 25 rows
  • 但為什麼  EstimatedNumberofRows 卻是 5884.2 ?

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

-- figure 42_StateProvinceID_2_Execution_Plan




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


  • LastRows: 25
  • EstimatedNumberofRows: 5884.2
  • Last_logical_reads: 218


-- figure 43_Performance_Statistics



03. 再度更換 Parameter: @StateProvinceID= 181


  • Number of Rows Read: 19614
  • Actual Number of Rows: 19583
  • Estimated  Number of Rows: 5884.2
  • Estimated Number of Rows to be Read: 19614


Use OPTION (OPTIMIZE FOR UNKNOWN),使用 < (Less Than)

  • return 19583 rows
  • 但為什麼  EstimatedNumberofRows 卻是 5884.2 ?


怎麼又是使用 EstimatedNumberofRows: 5884.2 來評估建立 Execution Plan 呢?

-- figure 44_StateProvinceID_181_Execution_Plan




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


  • LastRows: 19583
  • EstimatedNumberofRows: 5884.2
  • Last_logical_reads: 218


-- figure 45_Performance_Statistics



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


  • 在 Header(標頭)
  • Rows: 19614

謎底就是:

  • Inequality: EstimatedNumberofRows is 30% of the rows
  • 19614*0.3 = 5884.2


-- figure 46_Inequality_EstimatedNumberofRows is 30 of the rows




當使用 OPTION (OPTIMIZE FOR UNKNOWN) :

  • 且 條件式 使用 Inequality Operators :> (Greater Than)  ,  < (Less Than) 等等。
  • Query Optimizer 一律都 使用 Rows * 30% 方式來評估


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

試問:

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




Sample Code

20180114_OPTION_OPTIMIZE_FOR_UNKNOWN_2
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing




參考資料

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