搜尋本站文章

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 聯結只會傳回 個資料列。
    • 此範例有 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

沒有留言:

張貼留言