October 12, 2012 at 8:59 am
We work in a highly virtualized environment, and the standard server is minimally configured. Invariably, when trouble-shooting, we check MAX MEMORY to make sure it is set well below the RAM available on the virtual server. The virtual memory balloon is not an issue as it is never enabled. However, on a lot of servers, we see high values in the SQL_CP buffer cache (over a gig of ad-hoc plans). I have even gone so far as to create a job to keep value low by running DBCC FREESYSTEMCACHE('SQL Plans') regularly.
1. Is this a bad practice?
2. A co-worker is concerned that this could have an adverse effect on a query being executed when the cache is flushed. I argue that the plan is being used, and that the cache'd copy is irrelevant. Is this true or not?
Jeff Bennett
Saint Louis, MO
October 12, 2012 at 10:23 am
We flush if there is bad plan. You can flush specific plan which is causing the issue.
If query is running it's plan can't be flushed.
October 12, 2012 at 11:38 am
set the "optimized for adhoc workload" database option this reduces the size of adhoc plans in your cache
Pooyan
October 12, 2012 at 12:16 pm
chudman (10/12/2012)
1. Is this a bad practice?
Generally, yes.
Are these single-use plans? The fact that they're ad-hoc is not a problem, if they're single use then there may be a problem with the way the app is written that may need to be looked at and changed. If there are lots of single-use plans resulting in memory pressure, then there's a problem that needs addressing. Just having lots of single-use plans is not in and of itself a problem. 1GB isn't all that large for a plan cache, and if it's reaching that size, I'd guess you have a fair of memory
2. A co-worker is concerned that this could have an adverse effect on a query being executed when the cache is flushed. I argue that the plan is being used, and that the cache'd copy is irrelevant. Is this true or not?
The one running when the cache is cleared, no. The next one, yes, it'll have to compile if it could have used a cached plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2012 at 1:09 pm
We set the 'Optimize for Ad-hoc Queries' setting. I should have mentioned that in my initial statement.
Almost all of our SQL servers supports third-party applications and their databases. So we have little control over the code that is written. If we modify that code, then we jeopardize the support from that vendor for business critical applications.
We still see large amounts of memory tied up in this cache. I say large, because in some cases the virtual server is only allocated 1 gig of memory total. 2 gigs is more of a standard however. So anything approaching several hundred meg can monopolize a major portion of the memory.
Thanks
Jeff Bennett
St. Louis, MO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply