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
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?