I mentioned that servers receiving a lot of dynamic constructs (typically those generated by client SQL apps/ORMs/LINQ, etc.) can start to consume too much plan cache and have problems with “single-use plans” in my last post titled: Statement execution and why you should use stored procedures. I also mentioned that SQL Server 2008 has an option/feature specifically to help reduce the bloat on the plan cache by only storing a query plan hash the first time a plan is created.
First – let’s check to see how your plan cache is currently allocated:
(note: updated to decimal(18,2) as a few of you had overflow errors due to high use counts!)
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]
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
This statement will show you how much of your cache is allocated to single use plans… this query works in 2005 and 2008; however, the primary solution I’m describing here (optimize for adhoc workloads) will only work in SQL Server 2008 and higher.
IMPORTANT NOTE / UPDATE: The most important point about this post is that you should be using resources more efficiently by regularly (and programmatically) checking / clearing the ad hoc / prepared plan cache using DBCC FREESYSTEMCACHE (‘SQL Plans’)… So, this post is really more about understanding the problem. Check out this post for more information on these numbers as well as cleaning the plan cache: Plan cache, adhoc workloads and clearing the single-use plan cache bloat.
If you have a lot of your cache going to plans that are only executed once, then it’s time to clean up the cache and take better advantage of it with plans that are more consistent and more stable. Ideally, this means using more stored procedures and writing these stored procedures effectively for better performance (I’m currently in the midst of doing this in my Optimizing Procedural Code category). However, if you absolutely must use a lot of adhoc SQL, then you should consider upgrading and turning on this new option. It’s an advanced configuration option so you won’t see it until you “show advanced options” and it’s set using sp_configure. There have been some other really good posts out there on how to use this and what this is so I’m just going to bring together some great resources for you to read. The most important post to read (and it’s especially important for those on you on versions of SQL Server 2000 or SQL Server 2005 RTM/SP1 [er... why aren't you on SP2?]) is that plan cache can get out of control. 2005 SP2 and 2008 reduce the total size but there no upper limit (which is again – a GREAT reason for the addition of “optimize for adhoc workloads”). Kalen Delaney talks about how things really work in her SQL Server 2005 SP2 post titled: Did You Know? SP2 does NOT limit the amount of plan cache you can have (key word there is LIMIT). Again, SQL Server doesn’t set an upper limit but it does [drastically] reduce the total size that’s possible (as of SP2). As for even more details on plan caching, recompilation and SQL Server 2008′s better cache control – check out Greg Low’s (blog|twitter) whitepaper titled: Plan Caching in SQL Server 2008. Taken STRAIGHT from the first section of the whitepaper:
SQL Server 2008 and SQL Server 2005 SP2
* 75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB
SQL Server 2005 RTM and SQL Server 2005 SP1
* 75% of visible target memory from 0-8GB + 50% of visible target memory from 8Gb-64GB + 25% of visible target memory > 64GB
SQL Server 2000
* SQL Server 2000 4GB upper cap on the plan cache
Finally, lots of additional posts on this topic will give you even more details:
- Bob Beauchemin (blog) did a simple post on this while it was still in RC0 here: Performance features in SQL Server 2008 RC0 – Optimize for Adhoc Workloads.
- Jose Barreto (blog|twitter) did a simple post on this and pulls together a few nice resources as well here: SQL Server 2008 Optimize for Ad Hoc Workloads.
- Kalen Delaney (blog) did a post on how SQL Server 2008′s metadata returns only 17 characters when “Compiled Plan Stub” is actually 18… and, how to correctly see it here: Geek City: Reducing Cache Bloat and a Metadata Bug. Note: this bug has been fixed in SQL Server 2008 SP1.
- Kalen Delaney wrote an article on SQL Server Magazine (note: this article requires a SQL Server Magazine subscription) titled: Managing Plan Cache Size and after the article, she posted a Q&A on the questions she received from the article in a blog post titled: Geek City: Q & A on my Plan Cache Sizing article in SQL Server Magazine.
- Books online topics:
- Glenn [Alan] Berry (blog|twitter) has a post in his DMV A Day series on looking at the plan cache and seeing which statements are causing you grief. Once you see how much cache is being wasted with your single use plans (with the above query) – it’s time to start looking and seeing what they are. His blog post is titled: A DMV A Day – Day 16.
NOTE: Be sure to read the subsequent post on these numbers and how to deal with it when your cache is bloated!
Next couple of posts: (read this first) Plan cache, adhoc workloads and clearing the single-use plan cache bloat and then read this: Clearing the cache – are there other options?
Thanks for reading!