{"id":505,"date":"2010-04-26T06:15:00","date_gmt":"2010-04-26T06:15:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat.aspx"},"modified":"2021-11-11T16:07:45","modified_gmt":"2021-11-12T00:07:45","slug":"plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat\/","title":{"rendered":"Plan cache, adhoc workloads and clearing the single-use plan cache bloat"},"content":{"rendered":"

In my post describing the new SQL Server 2008 configuration option [optimize for adhoc workloads] titled: Plan cache and optimizing for adhoc workloads<\/a>, 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:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\u00a0CacheType<\/span><\/strong><\/td>\nTotal Plans\u00a0<\/span><\/strong><\/td>\nTotal MBs<\/span><\/strong><\/td>\n\u00a0Avg Use Count\u00a0<\/span><\/strong><\/td>\n\u00a0Total MBs – USE Count 1\u00a0<\/span><\/strong><\/td>\n\u00a0Total Plans – USE Count 1\u00a0<\/span><\/strong><\/td>\n% of Total<\/span><\/strong><\/td>\n<\/tr>\n
Prepared<\/span><\/td>\n\n

1,541 <\/span><\/p>\n<\/td>\n

\n

7,925.85 <\/span><\/p>\n<\/td>\n

\n

1,245 <\/span><\/p>\n<\/td>\n

\n

7,858.43 <\/span><\/p>\n<\/td>\n

\n

1,448 <\/span><\/p>\n<\/td>\n

\n

99.15 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc <\/span><\/td>\n\n

86,624 <\/span><\/p>\n<\/td>\n

\n

8,592.48 <\/span><\/p>\n<\/td>\n

\n

5 <\/span><\/p>\n<\/td>\n

\n

7,332.59 <\/span><\/p>\n<\/td>\n

\n

76,145 <\/span><\/p>\n<\/td>\n

\n

85.34 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared <\/span><\/td>\n\n

148,527 <\/span><\/p>\n<\/td>\n

\n

7,428.82 <\/span><\/p>\n<\/td>\n

\n

444 <\/span><\/p>\n<\/td>\n

\n

5,074.25 <\/span><\/p>\n<\/td>\n

\n

94,851 <\/span><\/p>\n<\/td>\n

\n

68.30 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc <\/span><\/td>\n\n

63,471 <\/span><\/p>\n<\/td>\n

\n

4,565.00 <\/span><\/p>\n<\/td>\n

\n

1,328 <\/span><\/p>\n<\/td>\n

\n

4,161.54 <\/span><\/p>\n<\/td>\n

\n

50,737 <\/span><\/p>\n<\/td>\n

\n

91.16 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc <\/span><\/td>\n\n

1,358 <\/span><\/p>\n<\/td>\n

\n

2,704.27 <\/span><\/p>\n<\/td>\n

\n

3,676 <\/span><\/p>\n<\/td>\n

\n

2,673.25 <\/span><\/p>\n<\/td>\n

\n

425 <\/span><\/p>\n<\/td>\n

\n

98.85 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/td>\n\n

48,140 <\/span><\/p>\n<\/td>\n

\n

6,233.64 <\/span><\/p>\n<\/td>\n

\n

173 <\/span><\/p>\n<\/td>\n

\n

2,106.05 <\/span><\/p>\n<\/td>\n

\n

20,493 <\/span><\/p>\n<\/td>\n

\n

33.79 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared <\/span><\/td>\n\n

18,639 <\/span><\/p>\n<\/td>\n

\n

2,590.82 <\/span><\/p>\n<\/td>\n

\n

127 <\/span><\/p>\n<\/td>\n

\n

2,027.72 <\/span><\/p>\n<\/td>\n

\n

13,614 <\/span><\/p>\n<\/td>\n

\n

78.27 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared <\/span><\/td>\n\n

18,010 <\/span><\/p>\n<\/td>\n

\n

3,237.18 <\/span><\/p>\n<\/td>\n

\n

354 <\/span><\/p>\n<\/td>\n

\n

1,944.48 <\/span><\/p>\n<\/td>\n

\n

9,561 <\/span><\/p>\n<\/td>\n

\n

60.07 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc <\/span><\/td>\n\n

17,392 <\/span><\/p>\n<\/td>\n

\n

2,417.38 <\/span><\/p>\n<\/td>\n

\n

100 <\/span><\/p>\n<\/td>\n

\n

1,787.33 <\/span><\/p>\n<\/td>\n

\n

7,741 <\/span><\/p>\n<\/td>\n

\n

73.94 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared <\/span><\/td>\n\n

6,276 <\/span><\/p>\n<\/td>\n

\n

1,875.71 <\/span><\/p>\n<\/td>\n

\n

59,370 <\/span><\/p>\n<\/td>\n

\n

1,560.66 <\/span><\/p>\n<\/td>\n

\n

4,020 <\/span><\/p>\n<\/td>\n

\n

83.20 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared <\/span><\/td>\n\n

16,832 <\/span><\/p>\n<\/td>\n

\n

2,844.61 <\/span><\/p>\n<\/td>\n

\n

10,829 <\/span><\/p>\n<\/td>\n

\n

1,507.63 <\/span><\/p>\n<\/td>\n

\n

5,732 <\/span><\/p>\n<\/td>\n

\n

53.00 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared <\/span><\/td>\n\n

3,075 <\/span><\/p>\n<\/td>\n

\n

1,681.56 <\/span><\/p>\n<\/td>\n

\n

46,262 <\/span><\/p>\n<\/td>\n

\n

1,427.33 <\/span><\/p>\n<\/td>\n

\n

604 <\/span><\/p>\n<\/td>\n

\n

84.88 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc <\/span><\/td>\n\n

29,047 <\/span><\/p>\n<\/td>\n

\n

1,923.84 <\/span><\/p>\n<\/td>\n

\n

39 <\/span><\/p>\n<\/td>\n

\n

1,362.09 <\/span><\/p>\n<\/td>\n

\n

22,827 <\/span><\/p>\n<\/td>\n

\n

70.80 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc\u00a0 <\/span><\/td>\n\n

17,028 <\/span><\/p>\n<\/td>\n

\n

1,255.60 <\/span><\/p>\n<\/td>\n

\n

185 <\/span><\/p>\n<\/td>\n

\n

1,160.85 <\/span><\/p>\n<\/td>\n

\n

15,845 <\/span><\/p>\n<\/td>\n

\n

92.45 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc <\/span><\/td>\n\n

118,838 <\/span><\/p>\n<\/td>\n

\n

2,073.25 <\/span><\/p>\n<\/td>\n

\n

2,813 <\/span><\/p>\n<\/td>\n

\n

1,128.47 <\/span><\/p>\n<\/td>\n

\n

41,212 <\/span><\/p>\n<\/td>\n

\n

54.43 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/td>\n\n

13,895 <\/span><\/p>\n<\/td>\n

\n

1,300.72 <\/span><\/p>\n<\/td>\n

\n

11 <\/span><\/p>\n<\/td>\n

\n

875.45 <\/span><\/p>\n<\/td>\n

\n

9,351 <\/span><\/p>\n<\/td>\n

\n

67.31 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared\u00a0 <\/span><\/td>\n\n

8,266 <\/span><\/p>\n<\/td>\n

\n

476.90 <\/span><\/p>\n<\/td>\n

\n

31 <\/span><\/p>\n<\/td>\n

\n

462.82 <\/span><\/p>\n<\/td>\n

\n

8,031 <\/span><\/p>\n<\/td>\n

\n

97.05 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc\u00a0 <\/span><\/td>\n\n

8,865 <\/span><\/p>\n<\/td>\n

\n

507.63 <\/span><\/p>\n<\/td>\n

\n

6 <\/span><\/p>\n<\/td>\n

\n

293.98 <\/span><\/p>\n<\/td>\n

\n

4,738 <\/span><\/p>\n<\/td>\n

\n

57.91 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc\u00a0 <\/span><\/td>\n\n

10,066 <\/span><\/p>\n<\/td>\n

\n

401.54 <\/span><\/p>\n<\/td>\n

\n

9 <\/span><\/p>\n<\/td>\n

\n

282.87 <\/span><\/p>\n<\/td>\n

\n

5,717 <\/span><\/p>\n<\/td>\n

\n

70.45 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc\u00a0 <\/span><\/td>\n\n

18,676 <\/span><\/p>\n<\/td>\n

\n

506.66 <\/span><\/p>\n<\/td>\n

\n

12,463 <\/span><\/p>\n<\/td>\n

\n

260.48 <\/span><\/p>\n<\/td>\n

\n

7,938 <\/span><\/p>\n<\/td>\n

\n

51.41 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc\u00a0 <\/span><\/td>\n\n

2,113 <\/span><\/p>\n<\/td>\n

\n

223.62 <\/span><\/p>\n<\/td>\n

\n

142 <\/span><\/p>\n<\/td>\n

\n

207.36 <\/span><\/p>\n<\/td>\n

\n

2,024 <\/span><\/p>\n<\/td>\n

\n

92.73 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc\u00a0 <\/span><\/td>\n\n

10,107 <\/span><\/p>\n<\/td>\n

\n

240.27 <\/span><\/p>\n<\/td>\n

\n

76 <\/span><\/p>\n<\/td>\n

\n

157.51 <\/span><\/p>\n<\/td>\n

\n

6,073 <\/span><\/p>\n<\/td>\n

\n

65.55 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/td>\n\n

1,888 <\/span><\/p>\n<\/td>\n

\n

154.91 <\/span><\/p>\n<\/td>\n

\n

4 <\/span><\/p>\n<\/td>\n

\n

125.08 <\/span><\/p>\n<\/td>\n

\n

1,438 <\/span><\/p>\n<\/td>\n

\n

80.74 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/td>\n\n

1,561 <\/span><\/p>\n<\/td>\n

\n

195.27 <\/span><\/p>\n<\/td>\n

\n

14,761 <\/span><\/p>\n<\/td>\n

\n

123.70 <\/span><\/p>\n<\/td>\n

\n

1,089 <\/span><\/p>\n<\/td>\n

\n

63.35 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared\u00a0 <\/span><\/td>\n\n

742 <\/span><\/p>\n<\/td>\n

\n

125.30 <\/span><\/p>\n<\/td>\n

\n

14,517 <\/span><\/p>\n<\/td>\n

\n

109.39 <\/span><\/p>\n<\/td>\n

\n

593 <\/span><\/p>\n<\/td>\n

\n

87.30 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared\u00a0 <\/span><\/td>\n\n

22,566 <\/span><\/p>\n<\/td>\n

\n

838.79 <\/span><\/p>\n<\/td>\n

\n

357 <\/span><\/p>\n<\/td>\n

\n

99.87 <\/span><\/p>\n<\/td>\n

\n

505 <\/span><\/p>\n<\/td>\n

\n

11.91 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc\u00a0\u00a0\u00a0 <\/span><\/td>\n\n

478 <\/span><\/p>\n<\/td>\n

\n

109.01 <\/span><\/p>\n<\/td>\n

\n

2 <\/span><\/p>\n<\/td>\n

\n

92.62 <\/span><\/p>\n<\/td>\n

\n

423 <\/span><\/p>\n<\/td>\n

\n

84.96 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc\u00a0 <\/span><\/td>\n\n

2,917 <\/span><\/p>\n<\/td>\n

\n

173.43 <\/span><\/p>\n<\/td>\n

\n

26,651 <\/span><\/p>\n<\/td>\n

\n

82.73 <\/span><\/p>\n<\/td>\n

\n

1,313 <\/span><\/p>\n<\/td>\n

\n

47.70 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc\u00a0 <\/span><\/td>\n\n

2,906 <\/span><\/p>\n<\/td>\n

\n

122.81 <\/span><\/p>\n<\/td>\n

\n

24 <\/span><\/p>\n<\/td>\n

\n

77.08 <\/span><\/p>\n<\/td>\n

\n

1,911 <\/span><\/p>\n<\/td>\n

\n

62.76 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared\u00a0 <\/span><\/td>\n\n

609 <\/span><\/p>\n<\/td>\n

\n

98.06 <\/span><\/p>\n<\/td>\n

\n

106 <\/span><\/p>\n<\/td>\n

\n

75.59 <\/span><\/p>\n<\/td>\n

\n

360 <\/span><\/p>\n<\/td>\n

\n

77.09 <\/span><\/p>\n<\/td>\n<\/tr>\n

Adhoc\u00a0\u00a0\u00a0 <\/span><\/td>\n\n

638 <\/span><\/p>\n<\/td>\n

\n

75.74 <\/span><\/p>\n<\/td>\n

\n

24 <\/span><\/p>\n<\/td>\n

\n

52.04 <\/span><\/p>\n<\/td>\n

\n

414 <\/span><\/p>\n<\/td>\n

\n

68.71 <\/span><\/p>\n<\/td>\n<\/tr>\n

Prepared\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/td>\n\n

322 <\/span><\/p>\n<\/td>\n

\n

23.12 <\/span><\/p>\n<\/td>\n

\n

8 <\/span><\/p>\n<\/td>\n

\n

17.82 <\/span><\/p>\n<\/td>\n

\n

275 <\/span><\/p>\n<\/td>\n

\n

77.09 <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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

Another thing to notice is that the only interesting nmumbers came from these two types of cache: Adhoc<\/strong> and Prepared<\/strong>. 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).<\/p>\n

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

To clear just the “SQL Plans” from the plan cache, use:<\/p>\n

DBCC FREESYSTEMCACHE(‘SQL Plans’) <\/span><\/span><\/span><\/span><\/p>\n

If you want to clear all of the cache, you can use:<\/p><\/blockquote>\n

DBCC FREEPROCCACHE<\/p><\/blockquote>\n

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

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\u00a0a 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<\/span><\/span><\/span><\/a>\u00a0and sys.dm_os_process_memory<\/span><\/span><\/span><\/a>). However, you can still do this fairly easily in both editions.<\/p>\n

So, let me summarize:
\n<\/strong>1) If you’re running SQL Server 2008 and you have\u00a0cache being wasted by single-use plans,\u00a0be sure to use the new “optimize for adhoc workloads.”
\n2) 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.<\/p>\n

    \n
  1. \n
      \n
    1. This is probably the easiest and most simplfied option.<\/li>\n
    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.<\/li>\n
    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.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

      1.\u00a0Clearing *JUST<\/u>* the ‘SQL Plans’ based on *just* the amount of Adhoc\/Prepared single-use plans (2005\u00a0and higher):<\/strong><\/p>\n

      DECLARE @MB decimal(19,3)
      \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , @Count bigint
      \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , @StrMB nvarchar(20) <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

      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
      \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , @Count = sum(CASE WHEN usecounts = 1 AND objtype IN (‘Adhoc’, ‘Prepared’) THEN 1 ELSE 0 END)
      \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , @StrMB = convert(nvarchar(20), @MB)
      \nFROM sys.dm_exec_cached_plans <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

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

      2.\u00a0Clearing *ALL<\/u>* of your cache based on the total amount of wasted by single-use plans (2005\u00a0and higher):<\/strong><\/p>\n

      DECLARE @MB decimal(19,3)
      \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , @Count bigint
      \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , @StrMB nvarchar(20) <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

      SELECT @MB = sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))\/1024\/1024
      \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , @Count = sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END)
      \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , @StrMB = convert(nvarchar(20), @MB)
      \nFROM sys.dm_exec_cached_plans <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

      IF @MB > 1000
      \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DBCC FREEPROCCACHE
      \nELSE
      \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RAISERROR (‘Only %s MB is allocated to single-use plan cache – no need to clear cache now.’, 10, 1, @StrMB)
      \ngo <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p><\/blockquote>\n

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

      This I put into a script that you can play with here (sp_SQLskills_CheckPlanCache<\/a>.sql (3.67 kb)<\/a>).<\/p><\/blockquote>\n

      Enjoy!
      \nkt<\/p>\n","protected":false},"excerpt":{"rendered":"

      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 – […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[50,53,65,66],"tags":[],"class_list":["post-505","post","type-post","status-publish","format-standard","hentry","category-optimizing-procedural-code","category-plan-cache","category-sql-server-2005","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/505","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=505"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/505\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=505"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=505"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=505"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}