搜尋本站文章

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

沒有留言:

張貼留言