These wait types are indicative of a shortage of memory objects.This special type of wait occurs when a task is waiting for a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.This wait type indicates that the rate of insertion of entries into the plan cache is very high and there is contention on the memory object.

A typical case is that the high usage of adhoc queries.Adhoc query execution creates separate plan for each parameter(if its not parameterized.)Here, the case is each entry will have to put in the procedure cache there by a case of bloating the cache.

As a first step, Lets have a look at the objects in the procedure cache:

SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(12,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(12,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

The above query will give information how the cache been used for different cache types.Here lets have a close watch on the adhoc queries. How much size been occupied? How many single use plans are created? Whats the size of single use cached plan?

The above will give a good information how our system is being heavily used for adhoc execution.

Okei, now we will have to look at the adhoc plans created to understand the usage(find out the maximum memory used adho queries ordered by size in bytes):

SELECT TOP 500 *, TEXT
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE cacheobjtype ='Compiled Plan' and objtype not in ('proc','trigger') 
--and text like 'SELECT DISTINCT vw.request_code%'
oRDER BY SIZE_IN_BYTES DESC , Usecounts asc

I wonder on the number,you may check in your database the above query to see the number of adhoc plans created with its usecounts. If there are many adhoc queries with single usecounts, do you really wanna be stored in the plan? Why cant we have a parameterized query instead of creating single plan there?

There are few options to avoid/restrict the single usecount plan created in the procedure cache.

1. Avoid dynamic queries from the fornt end as possible as.
2. In certain case where we cant avoid dynamic queries, Try to use sp_executeSQL for dynamic queries.
3. Turn on Optimize for Ad hoc Workloads (Optimize for adhoc will never save the cache plan until it is been used for the second time. First time, it saves only the plan stub in the memeory.Second time execution, it uses the same plan stub and save the cached plan in the memeory.To me this is a very good option that we can think of.)

There would be also an issue of CMEMTHREAD wait type associated with Token and Perm User Store which am not going to cover here. But worthy look at!!!

I would like to hear from you any special cases you have come across and share the knowledge out of it!!!