The optimizer always estimates a Multi-statement TVF has only a single row return, which is mostly not the truth. In order to see the detailed plan inside the Multi-statement TVF, you can run the following SQL statement to get it from the plan cache:
SELECT deqp.query_plan, dest.text,
SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,
(deqs.statement_end_offset - deqs.statement_start_offset)
/ 2 + 1) AS actualstatement, deqs.creation_time
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE deqp.objectid = OBJECT_ID('YourFunctionName');
The result will look like this:
Multi-statement TVF can be bad for performance due to the fact that it's treated like a table except there are no statistics available for SQL Server to base a good execution plan on - so it will estimate the function as returning a very small number of rows. If it returns a larger number of rows, then the plan generated could be a lot less than optimal.
Let's compare the showplan difference between Inline TVF and Multi-statement TVF. Below are two TVFs, one Inline and another Multi-statement:
CREATE FUNCTION udfInline
(
@id int
)
RETURNS TABLE
AS
RETURN
SELECT * FROM Account
GO
CREATE FUNCTION udf_Multi
(
@id int
)
RETURNS
@result TABLE
(
AccountId int, UtcOffset int
)
AS
BEGIN
INSERT @result SELECT * FROM Account
RETURN
END
GO
The Inline TVF execution plan look like this:
For Inline TVF, the estimated number of rows are quite accurate. The actual underlying tables get brought into the main query and a better execution plan can be generated based on proper statistics. You'll notice that in this case, the execution plan will NOT have a mention of the function at all as it's basically just merged the function into the main query.
The Multi statement TVF execution plan look like this: |