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.  
    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/2008):

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/2008):

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 only):

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

Enjoy!
kt

 

Related Posts