德瑞克：SQL Server 學習筆記: [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

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

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)

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

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

中繼結果集的預估和實際資料列數目間有很大的扭曲 (本例中為 MSTVF)，而...
...整體查詢對中繼結果的大小變更十分敏感。

這通常發生在查詢計劃有樹狀子目錄的複雜樹狀結構時。
僅僅 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:

There is a large skew between the estimated vs. actual number of rows for the intermediate result set (in this case, the MSTVF), and…
…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