Table Valued Function Very Slow

Question

I have a TVF that when called takes 10 secs to run.

However if I take the query inside that TVF, it takes only 1 sec. I have added correct indexes on this underlying query tables.

In the Execution Plan of the TVF it is performing a table scan. How do I find out why it is doing this?

Thanks.

Query...

select Username, Identifier 
from [dbo].Unmapped(@CustID, @StartDate, @EndDate)

Table Scan properties...

Answer 1

T-SQL manages the execution plan of non-inline table-valued functions separately from the code that calls the function. It treats it like a black box, which is why you see the UDF in the query plan instead of the UDF’s contents. This probably is related to the fact that non-inline table-valued functions can have multiple statements and a query plan is only capable of showing a single statement(?) (or at least it looks that way).

Mikael Eriksson commented:

You have a multi statement table valued function and SQL Server can not look into that and show the execution plan for the function. The scan you see here is a scan of the rows returned by the function and since you have no where clause, a scan is the best thing to do.

...and linked to this pastebin showing how to see the cached query plan of the non-inline table-valued function.

If you have executed your function at least once (e.g., SELECT * FROM myUdf()), then it should be cached in SQL Server. To show that cached plan, take the name of your function minus any schema and plug it into the query below (quoted from Mikael's code):

select
  PlanCreated       = qs.creation_time,
  ObjectName        = object_name(st.objectid),
  QueryPlan         = cast(qp.query_plan as xml),
  QueryText         = substring(st.text, 1 + (qs.statement_start_offset / 2), 1 + ((isnull(nullif(qs.statement_end_offset, -1), datalength(st.text)) - qs.statement_start_offset) / 2))
from sys.dm_exec_query_stats as qs
  cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
  cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp
where object_name(st.objectid) = 'FunctionName'

In the result set, the QueryText column should show a statement from the function and the QueryPlan should contain an XML query plan. In SSMS, if you click on a QueryPlan cell, it will automatically open up a new query plan viewer window. You will need to look at the source of your function and cross reference it to the statements and click through the query plans to search for performance issues.

Answer 2

Without seeing the underlying statement(s) in the table valued function and comparing them to the execution of the statement(s) outside of the tvf this is just a hypothesis of what you are seeing.

The most likely cause would be that the execution of the function and the statement(s) are happening serially and when you run the query in the tvf on its own it’s running in parallel.

Brent Ozar’s TVF is a brief overview of what I think you might be experiencing.

One way to compare the results is by looking at the execution plan the stand-alone query produces to see if its going parallel. The second would be to execute the stand-alone query with the OPTION(MAXDOP 1) at the end of the statement to force it to run serially. Here are a few references to the MAXDOP hint. Microsoft SQL Server docs. SO post