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 ?
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:
Compiled Plan Stub
CLR Compiled Func
CLR Compiled Proc
You may be looking to consider:
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 GO
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):