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


  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
於

沒有留言:

張貼留言