搜尋本站文章

2018-03-12

[SQL Server] Multi-Statement Table-Valued Function have a fixed Cardinality Estimation value


延續前文:[SQL Server] Parameter sniffing occurs with Multi-Statement Table-Valued Function

已知 MSTVF (multi-statement table valued functions, 多重陳述式資料表值函式) 是會受到 Parameter Sniffing 的影響,導致 執行效能 差,忽快忽慢。


但 MSTVF 在 Cardinality Estimation (CE, 基數估計) 上有 因不精確的評估,所產生的效能問題:

  • MSTVF 在 SQL Server 2014 和 SQL Server 2016  則固定 基數估計值 為 "100",舊版中是 "1"。 
  • 今在 SQL Server 2017 版本,已能精確 反映 Cardinality Estimation 值。
    • 也就是,Estimated Number of Rows 能精確反映出 實際的筆數,相同於 Actual Number of Rows


Compare: Cardinality Estimation value for MSTVF


SQL Server Edition Cardinality Estimation
2012
Fixed value is 1
2014
Fixed value is 100
2016
Fixed value is 100
2017
Accurate






Multi-Statement Table-Valued Function have a fixed Cardinality Estimation value



01. 建立 MSTVF (multi-statement table valued functions, 多重陳述式資料表值函式) : ufn_GetCity_CE。


-- figure 01_CREATE FUNCTION ufn_GetCity_CE




02. 檢視資料庫的 Compatibility Level。

-- figure 02_Database_Properties_Compatibility_Level




COMPATIBILITY_LEVEL = 110, SQL Server 2012

03. 設定 COMPATIBILITY LEVEL = 110,SQL Server 2012 版本。

-- figure 11_COMPATIBILITY_LEVEL_110





04. 執行此 MSTVF,輸入以下的 StateProvinceID 值

  • StateProvinceID = 9, Return Rows = 4564
  • StateProvinceID = 79, Return Rows = 2636
  • StateProvinceID = 32, Return Rows = 1

-- figure 12_Run_MSTVF





05. 觀察 Execution Plan:

  • 輸入不同參數,回傳不同的資料列筆數。
  • 但這三段 SQL Script 的 Execution Plan 卻都一樣!
  • Query Cost (relative to the batch) 都是耗用 33%。

-- figure 13_Execution_Plan_1




06. 進一步觀察 Execution Plan:

  • 竟然 Estimated Number of Rows 全都固定是 1


-- figure 14_Execution_Plan





07. 進一步觀察 Execution Plan:

  • 竟然 Estimated Number of Rows 全都固定是 1

-- figure 15_Row1





08. 切換到 Plan Explorer 來觀察這 3 段 SQL Script 的 Execution Plan。


可以清楚看到:

  • 輸入不同參數,回傳 不同的資料列筆數。
  • Estimated Number of Rows(Est Rows) 都是 1。
  • Actual Rows 顯示出各個 SQL Script 實際的回傳 資料列筆數。
  • Query Cost (relative to the batch) 都是耗用 33%。
  • Cardinality Estimation 不精確的問題。


-- figure 16_Plan_Explorer





Summary

MSTVFs have a fixed cardinality guess of “100” in SQL Server 2014 and SQL Server 2016, and “1” for earlier versions.

MSTVF 在 SQL Server 2014 和 SQL Server 2016 的 固定基數估計值為 "100",舊版中是 "1"。


COMPATIBILITY_LEVEL = 110, SQL Server 2012

  • StateProvinceID = 9, Return Rows = 4564, Estimated Number of Rows = 1
  • StateProvinceID = 79, Return Rows = 2636, Estimated Number of Rows = 1
  • StateProvinceID = 32, Return Rows = 1, Estimated Number of Rows = 1






COMPATIBILITY_LEVEL = 120, SQL Server 2014



01. 設定 COMPATIBILITY LEVEL = 110,SQL Server 2012 版本。



02. 執行此 MSTVF,輸入以下的 StateProvinceID 值

  • StateProvinceID = 9, Return Rows = 4564
  • StateProvinceID = 79, Return Rows = 2636
  • StateProvinceID = 32, Return Rows = 1


-- figure 21_COMPATIBILITY_LEVEL_120





03. 觀察 Execution Plan:

  • 輸入不同參數,回傳不同的資料列筆數。
  • 但這 3 段 SQL Script 的 Execution Plan 卻都一樣!
  • 竟然 Estimated Number of Rows 全都固定是 100
  • Query Cost (relative to the batch) 都是耗用 33%。


-- figure 22_Execution_Plan_Estimated Number of Rows_100





04. 進一步觀察 Execution Plan:

竟然 Estimated Number of Rows 全都固定是 100

-- figure 23_Estimated Number of Rows_100





05. 切換到 Plan Explorer 來觀察這 3 段 SQL Script 的 Execution Plan。


可以清楚看到:

  • 輸入不同參數,回傳 不同的資料列筆數。
  • Estimated Number of Rows(Est Rows) 都是 100
  • Actual Rows 顯示出各個 SQL Script 實際的回傳 資料列筆數。
  • Query Cost (relative to the batch) 都是耗用 33%。
  • Cardinality Estimation 不精確的問題。

-- figure 24_Plan_Explorer




Summary

MSTVFs have a fixed cardinality guess of “100” in SQL Server 2014 and SQL Server 2016, and “1” for earlier versions.

MSTVF 在 SQL Server 2014 和 SQL Server 2016 的 固定基數估計值為 "100",舊版中是 "1"。

COMPATIBILITY_LEVEL = 120, SQL Server 2014

StateProvinceID = 9, Return Rows = 4564, Estimated Number of Rows = 100
StateProvinceID = 79, Return Rows = 2636, Estimated Number of Rows = 100
StateProvinceID = 32, Return Rows = 1, Estimated Number of Rows = 100







COMPATIBILITY_LEVEL = 130, SQL Server 2016


01. 設定 COMPATIBILITY LEVEL = 130,SQL Server 2016 版本。



02. 執行此 MSTVF,輸入以下的 StateProvinceID 值

  • StateProvinceID = 9, Return Rows = 4564
  • StateProvinceID = 79, Return Rows = 2636
  • StateProvinceID = 32, Return Rows = 1


-- figure 31_COMPATIBILITY_LEVEL_130






03. 觀察 Execution Plan:

  • 輸入不同參數,回傳 不同的資料列筆數。
  • 但這三段 SQL Script 的 Execution Plan 卻都一樣!
  • 竟然 Estimated Number of Rows 全都固定是 100
  • Query Cost (relative to the batch) 都是耗用 33%。

-- figure 32_Execution_Plan_100






04. 進一步觀察 Execution Plan:

  • 竟然 Estimated Number of Rows 全都固定是 100


-- figure 33_Row_Estimated Number of Rows_100





05. 切換到 Plan Explorer 來觀察這 3 段 SQL Script 的 Execution Plan。


可以清楚看到:

  • 輸入不同參數,回傳 不同的資料列筆數。
  • Estimated Number of Rows(Est Rows) 都是 100
  • Actual Rows 顯示出各個 SQL Script 實際的回傳 資料列筆數。
  • Query Cost (relative to the batch) 都是耗用 33%。
  • Cardinality Estimation 不精確的問題。


-- figure 34_Plan_Explorer




Summary

MSTVFs have a fixed cardinality guess of “100” in SQL Server 2014 and SQL Server 2016, and “1” for earlier versions.

MSTVF 在 SQL Server 2014 和 SQL Server 2016 的 固定基數估計值為 "100",舊版中是 "1"。


COMPATIBILITY_LEVEL = 130, SQL Server 2016


  • StateProvinceID = 9, Return Rows = 4564, Estimated Number of Rows = 100
  • StateProvinceID = 79, Return Rows = 2636, Estimated Number of Rows = 100
  • StateProvinceID = 32, Return Rows = 1, Estimated Number of Rows = 100





COMPATIBILITY_LEVEL = 140, SQL Server 2017



01. 設定 COMPATIBILITY LEVEL = 140,SQL Server 2017 版本。


02. 執行此 MSTVF,輸入以下的 StateProvinceID 值

  • StateProvinceID = 9, Return Rows = 4564
  • StateProvinceID = 79, Return Rows = 2636
  • StateProvinceID = 32, Return Rows = 1


-- figure 41_COMPATIBILITY_LEVEL_140





03. 觀察 Execution Plan:


  • 輸入不同參數,回傳 不同的資料列筆數。
  • 但這三段 SQL Script 的 Execution Plan 已 不盡相同。
  • Query Cost (relative to the batch) 已不同。
  • Estimated Number of Rows 顯示的筆數 與 實際回傳筆數(Actual Number of Rows) 是相同的。


-- figure 42_Execution_Plan_Acc





04. 進一步觀察 Execution Plan:


  • Estimated Number of Rows 顯示的筆數 已經相同於 Actual Number of Rows 
  • Estimated Number of Rows  能精確顯示出 實際的筆數。


-- figure 43_Row




05. 切換到 Plan Explorer 來觀察這 3 段 SQL Script 的 Execution Plan。

  • Estimated Number of Rows(Est Rows) 能精確顯示出 實際的筆數,相同於 Actual Number of Rows。
  • Actual Rows 顯示出各個 SQL Script 實際的回傳 資料列筆數。
  • 每段 SQL Script 的 Est Cost % ,已能精確顯示,避免 Cardinality Estimation 不精確的問題。

-- figure 44_Plan_Explorer





Summary

MSTVFs have a fixed cardinality guess of “100” in SQL Server 2014 and SQL Server 2016, and “1” for earlier versions.

SQL Server 2017
Notice that the MSTVF table scan now reflects an accurate cardinality estimate. Also notice the re-ordering of this table scan and the other operations.

MSTVF 在 SQL Server 2014 和 SQL Server 2016 的 固定基數估計值為 "100",舊版中是 "1"。


SQL Server 2017

  • MSTVF 資料表掃描,現在能反映精確的基數估計值 accurate cardinality estimate.
  • 根據 MSTVF 資料表掃描的實際資料列計數,授與所需的記憶體。


COMPATIBILITY_LEVEL = 140, SQL Server 2017


  • StateProvinceID = 9, Return Rows = 4564, Estimated Number of Rows = 4564
  • StateProvinceID = 79, Return Rows = 2636, Estimated Number of Rows = 2636
  • StateProvinceID = 32, Return Rows = 1, Estimated Number of Rows = 1




Summary



交錯執行的優點 (Interleaved execution benefits)


一般情況下,預估和實際資料列數目間的扭曲愈高,加上下游計劃作業的數目,對效能的影響就愈大。

一般而言,交錯執行有益於下列情況的查詢:

  1. 中繼結果集的預估和實際資料列數目間有很大的扭曲 (本例中為 MSTVF),而...
  2. ...整體查詢對中繼結果的大小變更十分敏感。 
    • 這通常發生在查詢計劃有樹狀子目錄的複雜樹狀結構時。
    • 僅僅 MSTVF 的 "SELECT *" 不會得益於交錯執行。



符合交錯執行的陳述式

  • 在交錯執行中參考陳述式的 MSTVF,目前必須是唯讀的,且不為資料修改作業的一部分。 
  • 此外,如果 MSTVF 未使用執行階段常數,則不適用於交錯執行。
  • 例如:如果 MSTVF 在 CROSS APPLY 內部使用,則它們將不具備交錯執行的資格。



Interleaved execution benefits

In general, the higher the skew between the estimated vs. actual number of rows, coupled with the number of downstream plan operations, the greater the performance impact. In general, interleaved execution benefits queries where:
  1. There is a large skew between the estimated vs. actual number of rows for the intermediate result set (in this case, the MSTVF), and…
  2. …the overall query is sensitive to a change in the size of the intermediate result. This typically happens when there is a complex tree above that subtree in the query plan. A simply "SELECT *" from an MSTVF will not benefit from interleaved execution.


Interleaved execution eligible statements

MSTVF referencing statements in interleaved execution must currently be read-only and not part of a data modification operation.
Also, MSTVFs are not eligible for interleaved execution if they do not use runtime constants.
For example, the MSTVFs will not be eligible for interleaved execution if they are used on the inside of a CROSS APPLY.



Sample Code

20180312_MSTVF_fixed_CE
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing





References

Introducing Interleaved Execution for Multi-Statement Table Valued Functions
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-interleaved-execution-for-multi-statement-table-valued-functions/

SQL 資料庫中的彈性查詢處理 (Adaptive query processing in SQL databases)
https://docs.microsoft.com/zh-tw/sql/relational-databases/performance/adaptive-query-processing

[SQL Server] Parameter sniffing occurs with Multi-Statement Table-Valued Function
http://sharedderrick.blogspot.tw/2018/03/sql-server-parameter-sniffing-occurs.html


沒有留言:

張貼留言