[SQL Server] Execution plan of Inline TVF is the same as Ad hoc Query

延續前文：Interleaved execution for multi-statement table valued functions(交錯執行多重陳述式資料表值函式)

Compare MSTVF with InlineTVF:

• Actually, Execution plan of Inline TVF is the same as Ad hoc Query.
• Without side effect of Parameter Sniffing.
• If the Multi-Statement TVF is single statement only, convert to Inline TVF.

建議：
如果 MSTVF 內只包含單一 SQL Query，請轉換為 Inline TVF。

與 MSTVF 比較起來:

• Inline TVF 的 Execution Plan 相同於 Ad hoc Query 的 Execution Plan。
• 沒有 Parameter Sniffing 的副作用。
• 反映精確的基數估計值，顯示精確的資料列筆數。
• 協助 Query Optimizer 選擇有效率的 Index。
• 選擇 合適且有效率的  Join Algorithms。

---

Execution plan of Inline TVF is the same as Ad hoc Query


InlineTVF - Inline Table-Valued Function.


01. 建立 Table-Valued Function: ufn_GetCity_InlineTVF

• 只包含單一 SQL Query。
• 功能與先前建立的 MSTVF 相同：ufn_GetCity_CE 與 ufn_GetCity_Join。

-- figure 01_CREATE FUNCTION_ufn_GetCity_InlineTVF





02. 執行 Inline TVF，設定 StateProvinceID 值為 32，觀察 Execution Plan:

• 使用 Live Query Statistics，可以觀察各個 Operator 的傳回資料列筆數。
• 相同於 Ad hoc Query ，顯示 Execution Plan，沒有 Parameter Sniffing 的副作用。
• 都是 1 row。
• Index Seek_[IX_Address_StateProvinceID]。

-- figure 11_Return 1 row, Index Seek_[IX_Address_StateProvinceID]




03. Estimated Number of Rows 回傳 1。

-- figure 12_Estimated Number of Rows





04. 執行 Inline TVF，設定 StateProvinceID 值為 9，觀察 Execution Plan:

• 相同於 Ad hoc Query ，顯示 Execution Plan，沒有 Parameter Sniffing 的副作用。
• 4564 row。
• Index Scan: [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]。

-- figure 21_Return 4564 rows Index Scan





05. Estimated Number of Rows: 4564。

-- figure 22_Estimated Number of Rows




06. 使用 Plan Explorer 觀察:

• Est Rows 與 Actual Rows 皆能精確顯示，協助 Query Optimizer 選擇有效率的 Index。

-- figure 31_Plan_Explorer






07. 使用 DMV 觀察 Performance Data:

• ObjType 都是 Adhoc。

-- figure 32_View_Performance_Data




08. 將 Inline TVF 應用於 Table Join 的 Query:

• 相同於 Ad hoc Query ，顯示 Execution Plan，沒有 Parameter Sniffing 的副作用。
• Index Seek: [IX_Address_StateProvinceID]。
• 觀察各個 Operator 的傳回資料列筆數，顯示精確的資料列筆數。
• 協助 Query Optimizer 選擇有效率的 Index 以及 選擇 合適且有效率的  Join Algorithms: Merge Join(Inner Join)。

-- figure 41_Return 5884 rows Merge Join(Inner Join)




-- figure 42_Plan_Explorer




-- figure 43_View_Performance_Data

---

Sample Code

20180331_InlineTVF_Adhoc
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

References

Post-migration Validation and Optimization Guide
https://docs.microsoft.com/en-us/sql/relational-databases/post-migration-validation-and-optimization-guide

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

[SQL Server] Multi-Statement Table-Valued Function have a fixed Cardinality Estimation value
http://sharedderrick.blogspot.tw/2018/03/sql-server-multi-statement-table-valued.html

[SQL Server] Interleaved execution for multi-statement table valued functions
http://sharedderrick.blogspot.tw/2018/03/sql-server-interleaved-execution-for.html

[SQL Server] Interleaved execution for multi-statement table valued functions

延續前文：[SQL Server] Multi-Statement Table-Valued Function have a fixed Cardinality Estimation value


在 SQL Server 2017 版本，設定 COMPATIBILITY_LEVEL = 140：

• MSTVF 能精確 反映 Cardinality Estimation 值。
• MSTVF 支援 Interleaved execution (交錯執行)功能。
• 讓 Query Optimizer 能選擇出 適合 且 有效率的  Join Algorithms (聯結演算法)。

在本次練習中，以 Logical Reads 來看，若 Query Optimizer 能選擇 合適且有效率的  Join Algorithms，可提升約 38.9% 效能。(22527/16214.0 = 1.3893548。約 38.9%。)

COMPATIBILITY_LEVEL	STATISTICS
110 (SQL Server 2012)	Logical Reads = 20967
130 (SQL Server 2016)	Logical Reads = 22527
140 (SQL Server 2017)	Logical Reads = 16214

---

Interleaved execution for multi-statement table valued functions (交錯執行多重陳述式資料表值函式)

• 交錯執行會變更單次查詢執行的最佳化和執行階段之間的單向界限，並讓計劃根據修改過的基數估計值調整。 
• 在最佳化期間，如果遇到交錯執行的候選項目，目前是多重陳述式資料表值函式 (MSTVF)，會暫停最佳化、執行適用的樹狀子目錄、擷取精確的基數估計值，再繼續下游作業的最佳化。 
• MSTVF 在 SQL Server 2014 和 SQL Server 2016 的固定基數估計值為 "100"，舊版中是 "1"。 
• 交錯執行有利處理因為這些與多重陳述式資料表值函式建立關聯之 固定基數估計值引起的工作負載效能問題。

---

Interleaved execution for multi-statement table valued functions (交錯執行多重陳述式資料表值函式)



01. 準備環境，建立 Non-Clustered Index：

-- figure 01_CREATE NONCLUSTERED INDEX




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

-- figure 02_CREATE FUNCTION dbo.ufn_GetCity_Join





COMPATIBILITY_LEVEL = 110, SQL Server 2012


在 SQL Server 2012 之前(含)的版本，MSTVF 的 Cardinality Estimation (CE, 基數估計) 都是固定為 1。


03. 設定 COMPATIBILITY LEVEL = 110，SQL Server 2012 版本。並執行此 MSTVF，設定 StateProvinceID 值為 9。

• 設定 MSTVF 使用 StateProvinceID = 9，則 MSTVF 內部將 Return Rows = 4564。
• 使用 資料表 Sales.SalesOrderHeader 與 MSTVF 做 Table Join，將 Return 5884 rows。

-- figure 11_COMPATIBILITY_LEVEL_110




04. 觀察 Execution Plan:

• Nested Loops(Inner Join) Cost: 0%
• Sequence Cost: 0%

-- figure 12_Execution_Plan_1





05. 觀察 Table Scan Operator：

• Actual Number of Rows: 4564
• Estimated Number of Rows: 1

這兩者誤差值極大。

在 SQL Server 2012 之前(含)的版本，MSTVF 的 Cardinality Estimation (CE, 基數估計) 都是固定為 1。



-- figure 13_Execution_Plan_2



06. 觀察 Index Seek operator：

• Actual Number of Rows: 5884
• Estimated Number of Rows: 1.64575

這兩者誤差值極大。

-- figure 14_Index_Seek




07. 觀察 Nested Loops operator：

• Actual Number of Rows: 5884
• Estimated Number of Rows: 1.64575

這兩者誤差值極大。

-- figure 15_Nested_Loops




08. 點選 Include Live Query Statistics


-- figure Include Live Query Statistics






09. 使用 Live Query Statistics 觀察 Execution Plan


以下說明 Live Query Statistics (即時查詢統計資料) 輸出，顯示 MSTVF 固定基數估計值的影響。

您可以查看實際的資料列流程與估計的資料列。此計劃有 2 個重要區域 (流向為由 右至左)：

1. MSTVF 資料表掃描的 固定估計值 是 1 個資料列。
• 此範例有 4,564 個資料列流經此 MSTVF 資料表掃描。
• 但如即時查詢統計資料中所見的 “4564 of 1” 估計實值。所以，受到 固定基數估計值 的影響，產生大幅扭曲。
2. 關於 Nested Loops (巢狀迴圈)作業，假定外部端 Join 聯結只會傳回 2 個資料列。
• 此範例有 5,884 個資料列流經此 Nested Loops(Inner Join)。
• 但如即時查詢統計資料中所見的 “5884 of 2” 估計實值。所以，受到 固定基數估計值 的影響，產生大幅扭曲。
• 如果 MSTVF 實際傳回大量的資料列，則使用不同的 Join Algorithms 可能會更有效率。

-- figure Live Query Statistics

 




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

-- figure 16_Performance




COMPATIBILITY_LEVEL = 110, SQL Server 2012

• Estimated Number of Rows: 1.64575 + 4564 = 4565.64575
• Last CPU Time(sec) = 0.042088000 + 0.015588000 = 0.057676000
• Last Logical Reads = 15198 + 5769 = 20967

---

COMPATIBILITY_LEVEL = 130, SQL Server 2016


在 SQL Server 2014 與 2016 版本，MSTVF 的 Cardinality Estimation (CE, 基數估計) 都是固定為 100。


01. 設定 COMPATIBILITY LEVEL = 130，SQL Server 2016 版本。並執行此 MSTVF，設定 StateProvinceID 值為 9。

• 設定 MSTVF 使用 StateProvinceID = 9，則 MSTVF 內部將 Return Rows = 4564。
• 使用 資料表 Sales.SalesOrderHeader 與 MSTVF 做 Table Join，將 Return 5884 rows。

-- figure 31_COMPATIBILITY_LEVEL_130





02. 觀察 Execution Plan:

• Nested Loops(Inner Join) Cost: 1%
• Sequence Cost: 1%

-- figure 32_Execution_Plan_1




03. 觀察 Table Scan Operator：

• Actual Number of Rows: 4564
• Estimated Number of Rows: 100

這兩者誤差值極大。

在 SQL Server 2014 與 2016 版本，MSTVF 的 Cardinality Estimation (CE, 基數估計) 都是固定為 100。


-- figure 33_Execution_Plan_2




04. 觀察 Index Seek operator：

• Actual Number of Rows: 5884
• Estimated Number of Rows: 1.64575

這兩者誤差值極大。


-- figure 34_Index_Seek




05. 觀察 Nested Loops operator：

• Actual Number of Rows: 5884
• Estimated Number of Rows: 164.575

這兩者誤差值極大。



為什麼是 164.575?

• 在  Index Seek operator 裡，其 Estimated Number of Rows: 1.64575。
• 在 SQL Server 2014 與 2016 版本，MSTVF 的 Cardinality Estimation (CE, 基數估計) 都是固定為 100。
• 因此，在 Nested Loops operator 其 Estimated Number of Rows: 1.64575 x 100 = 164.575。

但與 Actual Number of Rows 對照後，仍是有很大的落差。

-- figure 35_Nested_Loops





06. 觀察 Table Valued Function operator：

• 在 SQL Server 2016 版本，尚未支援：IsInterleavedExecuted 功能。
• Actual Number of Rows: 0
• Estimated Number of Rows: 100

在 SQL Server 2014 與 2016 版本，MSTVF 的 Cardinality Estimation (CE, 基數估計) 都是固定為 100。

-- figure 36_Without_IsInterleavedExecuted






07. 使用 Live Query Statistics 觀察 Execution Plan

您可以查看實際的資料列流程與估計的資料列。此計劃有 2 個重要區域 (流向為由 右至左)：

1. MSTVF 資料表掃描的 固定估計值 是 100 個資料列。
• 此範例有 4,564 個資料列流經此 MSTVF 資料表掃描。
• 但如即時查詢統計資料中所見的 “4564 of 100” 估計實值。所以，受到 固定基數估計值 的影響，產生大幅扭曲。
2. 關於 Nested Loops (巢狀迴圈)作業，假定外部端 Join 聯結只會傳回 165 個資料列。
• 此範例有 5,884 個資料列流經此 Nested Loops(Inner Join)。
• 但如即時查詢統計資料中所見的 “5884 of 165” 估計實值。所以，受到 固定基數估計值 的影響，產生大幅扭曲。
• 如果 MSTVF 實際傳回大量的資料列，則使用不同的 Join Algorithms 可能會更有效率。

-- figure Live Query Statistics




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

-- figure 37_Performance




COMPATIBILITY_LEVEL = 130, SQL Server 2016

• Estimated Number of Rows: 164.575 + 4564 = 4728.575
• Last CPU Time(sec) = 0.042000000 + 0.015174000 = 0.057174000
• Last Logical Reads = 16758 + 5769 = 22527

---

COMPATIBILITY_LEVEL = 140, SQL Server 2017

• 在 SQL Server 2017 版本，支援 Interleaved execution功能。
• 在 SQL Server 2017 版本，已能精確 反映 Cardinality Estimation 值。

01. 設定 COMPATIBILITY LEVEL = 140，SQL Server 2017 版本。並執行此 MSTVF，設定 StateProvinceID 值為 9。

• 設定 MSTVF 使用 StateProvinceID = 9，則 MSTVF 內部將 Return Rows = 4564。
• 使用 資料表 Sales.SalesOrderHeader 與 MSTVF 做 Table Join，將 Return 5884 rows。

-- figure 41_COMPATIBILITY_LEVEL_140




02. 觀察 Execution Plan:

• Merge Join(Inner Join) Cost: 32%
• Sequence Cost: 3%

由先前的 Nested Loop 更換為 Merge Join。

-- figure 42_Execution_Plan_1





03. 觀察 Table Scan Operator：

• Actual Number of Rows: 4564
• Estimated Number of Rows: 4564

在 SQL Server 2017 版本，已能精確 反映 Cardinality Estimation 值。

• Estimated Number of Rows 能精確反映出 實際的筆數，相同於 Actual Number of Rows。

-- figure 43_Execution_Plan_2





04. 觀察 Index Scan operator：

• Actual Number of Rows: 31463
• Estimated Number of Rows: 31465

-- figure 44_Index_Scan





05. 觀察 Merge Join operator：

• Actual Number of Rows: 5884
• Estimated Number of Rows: 7511.18

-- figure 45_Merge_Join





06. 觀察 Table Valued Function operator：

• 在 SQL Server 2017 版本，支援 Interleaved Execution 功能。
• Actual Number of Rows: 0
• Estimated Number of Rows: 4564

-- figure 46_IsInterleavedExecuted




07. 使用 Live Query Statistics 觀察 Execution Plan

1. MSTVF 資料表掃描現在反映精確的 基數估計值。
• 此範例有 4,564 個資料列流經此 MSTVF 資料表掃描。
• 如即時查詢統計資料中所見的 “4564 of 4564” 估計實值，完全符合 100%。
2. 關於 Join Algorithms，已經由 Nested Loops 改用為 Merge Join。
• 在本次範例中，選擇 合適且有效率的  Join Algorithms，可提升約 38.9% 效能。
• Merge Join 作業，假定外部端 Join 聯結傳回 7511 個資料列。
• 如即時查詢統計資料中所見的 “5884 of 7511” 估計實值，符合比率 78% 。

-- figure Live Query Statistics




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

-- figure 47_Performance




COMPATIBILITY_LEVEL = 140, SQL Server 2017

• Estimated Number of Rows:  7511.18 + 4564 = 12075.18
• Last CPU Time(sec) = 0.043117000 + 0.011607000 = 0.054724000
• Last Logical Reads = 10445 + 5769 = 16214

---

Compare: Execution Plan in different COMPATIBILITY_LEVEL mode



01. 比對 3 份 Execution Plan：

• 乍看之下，更換到 COMPATIBILITY_LEVEL = 140, SQL Server 2017 後，反而耗用更多的 Cost!
• 事實是，第 1 份 與 第 2 份 的 Execution Plan 之 Subtree Cost 已不具備參考價值。
• 這是由於 MSTVF 在 SQL Server 2017 之前的版本，採用 固定 基數估計值，因此 Subtree Cost 失真。

-- figure 51_Compare_Execution_Plan





02. 使用 Plan Explorer 來觀察

• 如同先前所述，由於 MSTVF 在 SQL Server 2017 之前的版本，採用 固定 基數估計值，因此 Execution Plan 的 Subtree Cost 失真 (ESt Cost%)。

-- figure 52_Compare_Plan_Explorer



03. 測試 SQL Server 2012, 2014, 2016, 2017 版本的 COMPATIBILITY_LEVEL。

-- figure 53_Compare_4





04. 使用 Plan Explorer 來觀察

• 如同先前所述，由於 MSTVF 在 SQL Server 2017 之前的版本(CE Model = 140)，採用 固定 基數估計值，因此 Execution Plan 的 Subtree Cost 失真  (ESt Cost%)。

-- figure 54_Compare_Plan_Explorer

---

注意事項

• 在交錯執行中參考陳述式的 MSTVF，目前必須是唯讀的，且不為資料修改作業的一部分。 
• 此外，如果 MSTVF 未使用執行階段常數，則不適用於交錯執行。
• 一般情況下，預估和實際資料列數目間的扭曲愈高，加上下游計劃作業的數目，對效能的影響就愈大。
• 一旦快取交錯執行計劃，第一次執行即修改過估計值的計劃會用於連續執行，不必重新具現化交錯執行。
• 如果從快取清除或收回計劃，就會在執行查詢時重新整理使用交錯執行的編譯。使用 OPTION(RECOMPILE) 的陳述式會建立使用交錯執行的新計劃，且不會快取它。

---

Sample Code

20180316_MSTVF_Interleaved_execution
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

[SQL Server] Multi-Statement Table-Valued Function have a fixed Cardinality Estimation value
http://sharedderrick.blogspot.tw/2018/03/sql-server-multi-statement-table-valued.html

[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