Plan cache, adhoc workloads and clearing the single-use plan cache bloat

In my post describing the new SQL Server 2008 configuration option [optimize for adhoc workloads] titled: Plan cache and optimizing for adhoc workloads, I asked for you to send me your plan cache numbers. In addition to the comments on the post, I received a lot of feedback in email (and lots of comments – and even a related blog posts). The general state of the cache for many was that only 10s of MB were wasted, for a couple it was 100s MB and for a few it was GBs of memory – completely wasted. I’ve taken some of the worst numbers and compiled them below:

First, some of these do have the “optimize for adhoc workloads” option set. However, not too many. And, for those that do have this set, the single-use plan cache was for the amount of space taken by the compile plan stubs – not actual execution plans. Typically, these are significantly smaller (typically only a matter of bytes) rather than 10s-100s of kilobytes. So, while the amount of cache wasted is reduced to only a fraction of what it would have been without the option set, there’s still the potential for a lot of cache wasted.

Another thing to notice is that the only interesting nmumbers came from these two types of cache: Adhoc and Prepared. This is to be expected – the numbers for single-use plan cache are worst for the scenarios where statements are auto parameterized and “prepared” but not often re-used (where the statements submitted vary wildly because of different client applications/executions). But, while it is expected, what can you do about this? Unfortunately, not much – without changing the client application interface to the data (meaning – use stored procedures).

However, what you might benefit from (even if you have the option set) is periodically clearing the cache. However, you don’t necessarily need to clear all of the cache – you can just clear the Adhoc plan cache.

To clear just the “SQL Plans” from the plan cache, use:

DBCC FREESYSTEMCACHE(‘SQL Plans’)

If you want to clear all of the cache, you can use:

DBCC FREEPROCCACHE

And, in SQL Server 2005/2008 there are a host of combinations that will allow you to clear as much or as little as you’d like – down to just a single plan (using the plan_handle) in SQL Server 2008. For more information, check out the BOL topic: DBCC FREEPROCCACHE.

But, if you want to be more clever about it, you might want to automate the process of clearing these ‘SQL Plans’ when they’re wasting more than a certain percentage of the memory that SQL Server’s using OR more simply, once the single-use plan cache reaches a certain size (depending on the amount of memory that you have you might choose something as low as 500MB or possibly as high as 2-3GB). To help you out, I decided to write the queries to do this and give you a few options from which to choose. And, it’s definitely easier to write more interesting code in 2008 because of some of the new memory-related DMVs (specifically: sys.dm_os_sys_memory and sys.dm_os_process_memory). However, you can still do this fairly easily in both editions.

So, let me summarize:
1) If you’re running SQL Server 2008 and you have cache being wasted by single-use plans, be sure to use the new “optimize for adhoc workloads.”
2) If you still find that you’re wasting 100s of MB or GB of cache, consider creating a job that programmatically checks cache and then clears the ‘SQL Plans’ from cache based on one of the options/code below.

1. 1. This is probably the easiest and most simplfied option.
   2. This is probably overkill for most folks but probably won’t hurt either, especially if you have a lot of other types of cache being wasted by single-use plans.
   3. This is probably the most interesting and uses some of the new DMVs to see how much of the ACTUAL working cache is going to single-use plans. However, this is only for 2008. Could I write it for 2005, yes, but it’s not really worth it. I’d just go with option 1 (or possibly 2) for 2005.

1. Clearing *JUST* the ‘SQL Plans’ based on *just* the amount of Adhoc/Prepared single-use plans (2005 and higher):

DECLARE @MB decimal(19,3)
        , @Count bigint
        , @StrMB nvarchar(20)

SELECT @MB = sum(cast((CASE WHEN usecounts = 1 AND objtype IN (‘Adhoc’, ‘Prepared’) THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024
        , @Count = sum(CASE WHEN usecounts = 1 AND objtype IN (‘Adhoc’, ‘Prepared’) THEN 1 ELSE 0 END)
        , @StrMB = convert(nvarchar(20), @MB)
FROM sys.dm_exec_cached_plans

IF @MB > 10
        BEGIN
                DBCC FREESYSTEMCACHE(‘SQL Plans’)
                RAISERROR (‘%s MB was allocated to single-use plan cache. Single-use plans have been cleared.’, 10, 1, @StrMB)
END
ELSE
        BEGIN
                RAISERROR (‘Only %s MB is allocated to single-use plan cache – no need to clear cache now.’, 10, 1, @StrMB)
— Note: this is only a warning message and not an actual error.
        END
go

2. Clearing *ALL* of your cache based on the total amount of wasted by single-use plans (2005 and higher):

DECLARE @MB decimal(19,3)
        , @Count bigint
        , @StrMB nvarchar(20)

SELECT @MB = sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024
        , @Count = sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END)
        , @StrMB = convert(nvarchar(20), @MB)
FROM sys.dm_exec_cached_plans

IF @MB > 1000
        DBCC FREEPROCCACHE
ELSE
        RAISERROR (‘Only %s MB is allocated to single-use plan cache – no need to clear cache now.’, 10, 1, @StrMB)
go

3. Stored Procedure to report/track + logic to go into a job based on percentage OR MB of wasted cache (2008 and higher):

This I put into a script that you can play with here (sp_SQLskills_CheckPlanCache.sql (3.67 kb)).

Enjoy!
kt