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 TOP 50 *
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