7

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

Any online resource for this?

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Ashwani K
  • 7,880
  • 19
  • 63
  • 102

3 Answers3

10

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.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • thank you very much for explaining each case. Do you know if you can create indexes on iTVFs since they are treated like views? – Gabriel Espinoza May 14 '18 at 12:41
  • @GabrielEspinoza No, you cannot create indexes on iTVFs since they are _inline_ code that does not define a return table schema, and you cannot create an index in a `SELECT` statement. Think of them as being sub-queries or derived tables. This is the same for CTEs as well, since they are essentially inline Views. Likewise, you can create indexes _on_ Views, but you cannot create an index _within_ a View. – Solomon Rutzky May 14 '18 at 14:00
8

Yes they do go in the execution plan cache.

The sys.dm_exec_query_plan DMV will show a plan for given plan handle. Quote from there:

Query plans for various types of Transact-SQL batches, such as ad hoc batches, stored procedures, and user-defined functions, are cached in an area of memory called the plan cache. Each cached query plan is identified by a unique identifier called a plan handle. You can specify this plan handle with the sys.dm_exec_query_plan dynamic management view to retrieve the execution plan for a particular Transact-SQL query or batch.

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
2

Yes they are considered for caching.

http://msdn.microsoft.com/en-us/library/ms181055.aspx

codingbadger
  • 42,678
  • 13
  • 95
  • 110
  • Thanks, but in the link it is not written that user defined functions are also considered for plan caching? – Ashwani K Jun 22 '10 at 07:01
  • 2
    This is a quote taken from the article: `When any SQL statement is executed in SQL Server, the relational engine first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists. SQL Server reuses any existing plan it finds, saving the overhead of recompiling the SQL statement. If no existing execution plan exists, SQL Server generates a new execution plan for the query.` – codingbadger Jun 22 '10 at 07:11