Does “Table Valued Function” force the plan to get into cache?

Posted on

Question :

sp_executesql and stored procedures force the plan generated from t-sql to get put into the cache, does this behaviour is shared with table value function or not ?

Answer :

does this behaviour is shared with table value function or not?

the short answer is:
It is not, because if you check what execution plans you have in the cache these are the objects you can find there:

cacheobjtype nvarchar(34) Type of object in the cache.
The value can be one of the following:

  1. Compiled Plan

  2. Compiled Plan Stub

  3. Parse Tree

  4. Extended Proc

  5. CLR Compiled Func

  6. CLR Compiled Proc

You may be looking to consider:

Plan cache and optimizing for adhoc workloads

check what your plan cache is made up of:

SELECT objtype AS [CacheType],
    COUNT_BIG(*) AS [Total Plans],
    SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
    AVG(usecounts) AS [Avg Use Count],
    SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
        ELSE 0
        END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
    SUM(CASE WHEN usecounts = 1 THEN 1
        ELSE 0
        END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC

enter image description here

Minimize plan cache bloat with the optimize for ad hoc workloads configuration option

SQL Server 2008 introduced the ‘optimize for ad-hoc workloads
configuration option which, when enabled, causes SQL Server to create
a plan stub instead of a full plan the first time a statement is
executed. If the plan is used a second time, then SQL Server will
store the full plan. This option was introduced to help minimize the
performance impact of plan cache bloat.

and another thing I would like to mention is..

High compiles (not recompiles):

High Compiles and Multi-Statement Table Valued Functions

Leave a Reply

Your email address will not be published. Required fields are marked *