Joins to Table-Valued Functions

SQL Server’s table-valued functions (TVFs) seem like a good idea, but they mask a host of potential performance problems. TVFs cause portions of an execution plan to stay serial (they’ll avoid parallelism), they produce bad row estimations, and multi-statement TVFs may not even get the best optimization available. In short – TVFs stink.

To fix the problem, your first step is to try to inline the function’s code. If you can reduce the TVF to a single statement, it’s likely that SQL Server will inline the logic of the TVF and fully optimize it. If you can’t reduce the TVF to a single statement, it’s time to consider other alternatives like inlining the code yourself or inserting into a temporary table.

You can find queries that trigger this warning with the following:

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT  st.text,
        qp.query_plan
FROM    (
    SELECT  TOP 50 *
    FROM    sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.exist('//p:RelOp[contains(@LogicalOp, "Join")]/*/p:RelOp[(@LogicalOp[.="Table-valued function"])]') = 1

1

2

3

4

5

6

7

8

9

10

11

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)

SELECT st.text,

qp.query_plan

FROM (

SELECT TOP 50 *

FROM sys.dm_exec_query_stats

ORDER BY total_worker_time DESC

) AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qp.query_plan.exist('//p:RelOp[contains(@LogicalOp, "Join")]/*/p:RelOp[(@LogicalOp[.="Table-valued function"])]') = 1

1 Comment.

Things to Avoid in Your Query – EugeneChiang.com
March 21, 2024 10:29 pm

[…] https://www.brentozar.com/blitzcache/tvf-join/ […]