德瑞克：SQL Server 學習筆記: [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) 的陳述式會建立使用交錯執行的新計劃，且不會快取它。