德瑞克：SQL Server 學習筆記: 2018/03

2018-03-31

[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

2018-03-15

[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 個重要區域 (流向為由右至左)：

MSTVF 資料表掃描的固定估計值是 1 個資料列。

此範例有 4,564 個資料列流經此 MSTVF 資料表掃描。
但如即時查詢統計資料中所見的 “4564 of 1” 估計實值。所以，受到固定基數估計值的影響，產生大幅扭曲。

關於 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 個重要區域 (流向為由右至左)：

MSTVF 資料表掃描的固定估計值是 100 個資料列。

此範例有 4,564 個資料列流經此 MSTVF 資料表掃描。
但如即時查詢統計資料中所見的 “4564 of 100” 估計實值。所以，受到固定基數估計值的影響，產生大幅扭曲。

關於 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

MSTVF 資料表掃描現在反映精確的基數估計值。

此範例有 4,564 個資料列流經此 MSTVF 資料表掃描。
如即時查詢統計資料中所見的 “4564 of 4564” 估計實值，完全符合 100%。

關於 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

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

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