[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