Sql-server – Query plan cache bloated by ad-hoc queries, even with “Optimize for Ad-hoc Workloads”

execution-planplan-cachesql serversql-server-2012

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?

Best Answer

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.

Related Solutions

Sql-server – How bad is query plan pollution (too many equivalent plans in cache)

"How bad is it?" depends on the degree to which you are suffering now or could suffer with increased workload in the future.

One major point of suffering with plan cache pollution could be too many single use plans bloating your plan cache leading to inefficient cache usage.

Another point of suffering could be high compilations/second - so in an environment with a heavy workload and a lot of activity, there is a cost associated with compiling over and over.

You can see the impact of compilations/sec in perfmon (SQL Server Statistics:Compilations/sec). This can look like CPU pressure. To your performance/applications, this can look like increased query duration waiting for needless compiles each time it runs.

You can see the impact to the plan cache from the memory bloat by this query borrowed from Glenn Berry's Diagnostic scripts. How big is your SQLCP plan cache?

SELECT TOP(10) [type] AS [Memory Clerk Type], 
       SUM(pages_kb)/1024 AS [Memory Usage (MB)] 
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]  
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);

Also the query that was used in the question to identify the number of plans helps as well.

Is This Ever a Good Thing?

There are some cases where this could be good, but the situation is rare. Basically if you were suffering from parameter sniffing gone bad (nutshell: if the data can vary widely from execution to execution based on parameters, one compilation for one set of parameters ideal may yield an excellent query plan for that one query but poor for others.). My guess is that you likely wouldn't be dealing with that as bad as the implications from poor plan reuse.

What Can You Do About It?

Optimize For Ad Hoc Workloads can certainly help with the memory implications since only a stub of the plan is stored in cache at first execution, and the full plan isn't stored until it is executed a second time with the same plan.
Forced Parameterization could help here also. It can sometimes force parameterization to happen and help solve both the issue of cache bloat and the cost of having to recompile.
Fix The Queries Ideally, you shouldn't have to resort to these options, but instead can be more strict in your database development, encourage plan reuse, consider stored procedures for all of their benefits, and attempt to head off the problem that way. The ways to help fix this through forced parameterization or optimize for ad hoc are good to help, but the best solution is always aimed at the root cause.

There is an excellent resource here that talks about some of the dangers of plan cache pollution and some things you can do. I'd recommend a read here. It is written for SQL Server 2012, but the concepts and solutions apply.

Sql-server – Optimize for ad-hoc workload

Kris,

•Do we really think that we will see a measurable performance gain here? If so, how can we quantify it aside from looking at the space used in the query above?

That depends, but my gut instinct with the data you've given is - no. Sure, you'll potentially save some of that space as a plan stub will still take memory just not all that much (compared to your 1 MB plans). So you'll net memory, we get that. However, we don't know how many of those plans were executed a single time, and then some point later while still in cache executed again. This brings up the question about compilations/recompiles and the cpu utilization to go along with it. If you have a good bit of headroom then it may be a trivial issue (pun intended).

If your server isn't under memory pressure, I would not expect to see too much of an improvement in terms of "performance" depending on how you want to classify that. If you're swapping and having some slight memory pressure this could alleviate it for a few moments - though upping the VM memory would have the same effect at a much faster implementation without negative side effect cost.

•Understandably, I hesitate throwing switches like this on production systems. What implications should I be cautious of? Will the procedure cache wipe itself and rebuild? Are there any issues that I should be made aware of when turning this on?

According to BOL it will not affect anything currently in your plan cache: "Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected." http://msdn.microsoft.com/en-us/library/cc645587(v=sql.105).aspx

You may see the potential (depending on eventual re-use) of cpu hits for plan compiles (since it would have to do it twice, once for the original execution and then one for the second when it is stored).

Some other "weirdness" would include monitoring tools, especially if they are grabbing execution plans as plan stubs do not have any associated with them. Some odd results may come from tools that expect there to be one associated all the time.

I'm not extremely familiar with Dynamics but IIRC, it has a specific Microsoft setup guide like SharePoint. I'd double check this won't invalidate your supportability for the product.

Best Answer

Related Solutions

Sql-server – How bad is query plan pollution (too many equivalent plans in cache)

Sql-server – Optimize for ad-hoc workload

Related Question