{"id":507,"date":"2010-04-22T17:50:00","date_gmt":"2010-04-22T17:50:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Procedure-cache-and-optimizing-for-adhoc-workloads.aspx"},"modified":"2015-11-03T10:39:28","modified_gmt":"2015-11-03T18:39:28","slug":"plan-cache-and-optimizing-for-adhoc-workloads","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/plan-cache-and-optimizing-for-adhoc-workloads\/","title":{"rendered":"Plan cache and optimizing for adhoc workloads"},"content":{"rendered":"

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<\/a>. 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.<\/p>\n

First – let’s check to see how your plan cache is currently allocated:<\/p>\n

(note:<\/strong> updated to decimal(18,2) as a few of you had overflow errors due to high use counts!)<\/p>\n

SELECT objtype AS [CacheType],
\n COUNT_BIG(*) AS [Total Plans],
\n SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) \/ 1024 \/ 1024 AS [Total MBs],
\n AVG(usecounts) AS [Avg Use Count],
\n SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
\n ELSE 0
\n END) AS DECIMAL(18, 2))) \/ 1024 \/ 1024 AS [Total MBs \u2013 USE Count 1],
\n SUM(CASE WHEN usecounts = 1 THEN 1
\n ELSE 0
\n END) AS [Total Plans \u2013 USE Count 1]
\nFROM sys.dm_exec_cached_plans
\nGROUP BY objtype
\nORDER BY [Total MBs \u2013 USE Count 1] DESC
\nGO<\/p>\n

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.<\/span><\/span><\/p>\n

IMPORTANT NOTE \/ UPDATE:<\/strong>\u00a0The 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 (\u2018SQL Plans\u2019)\u2026 So, this post is really more about\u00a0understanding the problem. Check out this post for more information on these numbers as well as cleaning the plan cache:\u00a0Plan cache, adhoc workloads and clearing the single-use plan cache bloat<\/a>.<\/p>\n

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<\/a>). 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\u00a0plan 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<\/a>\u00a0(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<\/a>|twitter<\/a>)\u00a0whitepaper titled: Plan Caching in SQL Server 2008<\/a>. Taken STRAIGHT from the first section of the whitepaper:<\/p>\n

SQL Server 2008 and SQL Server 2005 SP2 <\/strong>
\n* 75% of visible target memory from 0-4GB + 10%<\/strong><\/span> of visible target memory from 4Gb-64GB + 5%<\/span><\/strong> of visible target memory > 64GB<\/p>\n

SQL Server 2005 RTM and SQL Server 2005 SP1
\n<\/strong>* 75% of visible target memory from 0-8GB + 50%<\/span><\/strong> of visible target memory from 8Gb-64GB + 25%<\/span><\/strong>\u00a0of visible target memory > 64GB<\/p>\n

SQL Server 2000
\n<\/strong>*\u00a0SQL Server 2000 4GB upper cap on the plan cache<\/p><\/blockquote>\n

Finally, lots of additional posts on this topic will give you even more details:<\/p>\n