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:

 CacheType Total Plans  Total MBs  Avg Use Count   Total MBs – USE Count 1   Total Plans – USE Count 1  % of Total
Prepared

1,541

7,925.85

1,245

7,858.43

1,448

99.15

Adhoc

86,624

8,592.48

5

7,332.59

76,145

85.34

Prepared

148,527

7,428.82

444

5,074.25

94,851

68.30

Adhoc

63,471

4,565.00

1,328

4,161.54

50,737

91.16

Adhoc

1,358

2,704.27

3,676

2,673.25

425

98.85

Adhoc                

48,140

6,233.64

173

2,106.05

20,493

33.79

Prepared

18,639

2,590.82

127

2,027.72

13,614

78.27

Prepared

18,010

3,237.18

354

1,944.48

9,561

60.07

Adhoc

17,392

2,417.38

100

1,787.33

7,741

73.94

Prepared

6,276

1,875.71

59,370

1,560.66

4,020

83.20

Prepared

16,832

2,844.61

10,829

1,507.63

5,732

53.00

Prepared

3,075

1,681.56

46,262

1,427.33

604

84.88

Adhoc

29,047

1,923.84

39

1,362.09

22,827

70.80

Adhoc 

17,028

1,255.60

185

1,160.85

15,845

92.45

Adhoc

118,838

2,073.25

2,813

1,128.47

41,212

54.43

Adhoc                

13,895

1,300.72

11

875.45

9,351

67.31

Prepared 

8,266

476.90

31

462.82

8,031

97.05

Adhoc 

8,865

507.63

6

293.98

4,738

57.91

Adhoc 

10,066

401.54

9

282.87

5,717

70.45

Adhoc 

18,676

506.66

12,463

260.48

7,938

51.41

Adhoc 

2,113

223.62

142

207.36

2,024

92.73

Adhoc 

10,107

240.27

76

157.51

6,073

65.55

Prepared            

1,888

154.91

4

125.08

1,438

80.74

Prepared            

1,561

195.27

14,761

123.70

1,089

63.35

Prepared 

742

125.30

14,517

109.39

593

87.30

Prepared 

22,566

838.79

357

99.87

505

11.91

Adhoc   

478

109.01

2

92.62

423

84.96

Adhoc 

2,917

173.43

26,651

82.73

1,313

47.70

Adhoc 

2,906

122.81

24

77.08

1,911

62.76

Prepared 

609

98.06

106

75.59

360

77.09

Adhoc   

638

75.74

24

52.04

414

68.71

Prepared             

322

23.12

8

17.82

275

77.09

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. 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

13 thoughts on “Plan cache, adhoc workloads and clearing the single-use plan cache bloat

  1. Sorry for replying in old thread but topic is evergreen :)

    So my question is,
    If we seeing very high memory usage by adhoc plans, is it advisable to schedule this “adhoc plan cleanup” with every 15 min log backup?

    My one of prod SQL is on sql 2005 where I dont have option to configure “optimize for adhoc workoads” and Adhoc plan are always on top. After clearing the adhoc cache, they filling it again with roughly speed of 10mb per min.

    1. Hey there Ashish – Yes, there are many environments where the cache check/flush is quite frequent (even every 15-30 mins). 15 mins is 1.5 GB (at your numbers) so clearing when you’re wasting 1.5GB sounds reasonable. It doesn’t have to correspond to log backups though. It should really be a separate job. I’d probably run it so that it doesn’t run at the same time as others (or, right before because it’s quick and log backups aren’t as quick).

      Hope that helps!
      kt

      1. Many thanks for the reply.
        I am big follower of you guys on pluralsight, sqlskills. If possible, please post your classes on web so that we can subscriber to it like pluralsight and get benefited with more valuable knowledge.

  2. Very useful and detailed information. Thanks for the writeup…it helped me update our internal tool, SCA, (that I have written) with new counter related to AdHoc Cache.

    rgds
    Sunit

  3. This is great. Just curious though: Won’t sql do this cleanup automatically in it’s own time when needed? What is the benefit to doing this on demand? Thanks
    Brian

    1. Yes but not until they’ve reached the plan cache limit.

      SQL considers plan pages to be more expensive to put into cache than data pages. So, SQL steals from the buffer pool for plan cache – up to a point. But, that point is higher than most people thing. And, in the end, you often end up wasting 2, 3, 4, (possibly 10-20) GB before they start kicking out the really old and only used once plans. So, instead of letting the single-use plan cache bloat get that large, you’re clearing it before it does. I usually recommend clearing at 5-10% of the process memory OR something like 2GB. You can modify the script to do this!

      hth,
      kt

  4. @Kimberly Tripp … great post and info but I was wondering if there is something for SQL 2012 and how things look now. Is there an updated post or code that uses best practices and techniques for SQL 2012.

    Thanks :-)

    1. Actually, they haven’t changed the algorithm for how much memory is allocated since 2008 / R2 so no need for a new post – all of these techniques to evaluate and resolve plan cache bloat still apply! hth, kt

  5. I have had a problem where plan cache eviction chooses poorly, and evicts plans of low cost such as sp_GetAppLock, instead of evicting older single use plans. This stops sp_GetAppLock even getting a chance to stay in the cache long enough, resulting in very high compilations/sec.

    I managed to make the plan cache last another 30 minutes by using this technique.

  6. sp_configure ‘max server memory’ now reports config_value and run_value in MB, so some of the calculations in the stored procedure need to be altered.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched

Explore

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.