Plan cache and optimizing for adhoc workloads

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
END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
SUM(CASE WHEN usecounts = 1 THEN 1
END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
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:

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!

Other articles

SQLskills SQL101: Partitioning

Continuing on our path to understanding the basics and core concepts, there’s a big topic that’s often greatly misunderstood and that’s partitioning. I know I’m


Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.