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:

  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

| improve this answer | |
  • so in this case it's better to convert all my Table valued function into stored procedures , what do you think ? – A.HADDAD May 11 at 21:25
  • not necessarily, it depends really on what improvement you want\need\envision to achieve. if you are experiencing bad performance in some or on a function in specific, ask another question specifically on that. make sure you add the execution plan and a link to this question too. – Marcello Miorelli May 12 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.