Query plan cache bloated by ad-hoc queries, even with "Optimize for Ad-hoc Workloads"

Question

I've been noticing what I thought to be unusual issues with our query plan cache, where the plans in the cache were never more than a day old.

Through running the following query (courtesy of Kimberly Tripp), it showed that the majority of plans (4.5Gb of 6Gb cached plans or 44813 of ~50000), were Ad-hoc queries that had a use count of 1.

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

I've determined the problem query (dynamic, with an EXEC, of course...), which is rather hideous, but also complicated to just 'fix', so I was looking into immediate improvements that could be made.

The instance is already set to use Optimize for Ad-hoc Workloads, however the CacheObjType from sys.dm_exec_cached_plans are all Compiled Plan rather than Compiled Plan Stub.

When using the Ad-hoc Workload mode, should the plans not be Compiled Plan Stub until their usecounts are greater than 1? Or is that not how this works?

There's also a refcounts field that no one seems to refer to when talking about Adhoc queries. The refcounts is always 1 when the type is Compiled Plan Stub and 2 when type is Compiled Plan. Even through reading BOL, I'm not entirely sure what this field means. Can someone clarify?

Answer 1

According to this, the second run of an ad hoc batch removes the stub (which was used only once) and creates and caches the plan (using it for the first time).

I also haven't seen many references to refcounts other than it being a count of references by cache objects. Adhoc Compiled Plan objects can still have a refcount of 1, so it's not exclusively caused by the persistence of the plan.