Are execution plan for functions cached in SQL server?

Question

Can any body help me in understanding if the execution plan for functions cached in SQL server?

Any online resource for this?

Answer 1

The accepted answer is inaccurate / misleading, primarily due to the referenced quote being too vague with regards to the term "user-defined functions".

There are several different types of User-Defined Functions in Microsoft SQL Server, and they are treated differently:

• Multi-statement TVFs:

  These are treated like Stored Procedures. The query that executes them only shows the reference to their name, not to any of their definition. They show up in sys.dm_exec_cached_plans with a cacheobjtype of "Compiled Plan" and an objtype of "Proc". Any input parameter values are also stored with the plan, hence Multi-statement TVFs are subject to parameter-sniffing issues.

• Inline TVFs (iTVFs):

  These are treated like Views. The query that executes them incorporates their definition. They show up in sys.dm_exec_cached_plans with a cacheobjtype of "Parse Tree" and an objtype of "View". Input parameter values are not stored with the plan, hence Inline TVFs are not subject to parameter-sniffing issues.

• Scalar UDFs:

  These are treated like Stored Procedures. The query that executes them only shows the reference to their name, not to any of their definition. They show up in sys.dm_exec_cached_plans with a cacheobjtype of "Compiled Plan" and an objtype of "Proc". Any input parameter values are also stored with the plan, hence Scalar UDFs are subject to parameter-sniffing issues. Also, unlike the two types of TVFs noted above, but like regular Stored Procedures, you can force recompilation of the execution plan using the WITH RECOMPILE option when executing via EXEC[UTE] instead of SELECT or SET.

• SQLCLR objects:

  These are treated more like client / app code. The query that executes them only shows the reference to their name, not to any of their definition. They show up in sys.dm_exec_cached_plans with a cacheobjtype of "CLR Compiled Func" or "CLR Compiled Proc", and an objtype of "Proc". But, unlike Multi-statement TVFs and Scalar UDFs, they do not have a definition and so do not have an associated query plan. However, any adhoc queries (not stored procedure calls) that they execute show up in sys.dm_exec_cached_plans with a cacheobjtype of "Compiled Plan" and an objtype of "Prepared". Any of these adhoc queries, if parameterized, should be storing the initial input parameter values with the prepared plan, and would hence be subject to parameter-sniffing issues.

For more details on object caching, please see the MSDN page on Caching Mechanisms.