7

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?

5

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.

  • Just so I understand what you're saying, the usecount of 1 means the plan has been used once, however the query has been executed twice? A stub is therefore not converted into a plan, rather replaced by it? – Mark Sinkinson May 16 '14 at 17:12
  • 3
    From the link provided by @SQLFox - The compiled plan stub allows the Database Engine to recognize that this ad hoc batch has been compiled before but has only stored a compiled plan stub, so when this batch is invoked (compiled or executed) again, the Database Engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache. – Kin Shah May 16 '14 at 17:27

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.