Understanding Memory - SQL Server 2012 Internals and Troubleshooting - page 75

For example, recall this script used earlier in the chapter:
SELECT count(*) AS 'Number of Plans',
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)'
FROM sys.dm_exec_cached_plans
The preceding code produced the following results on a SQL Server 2012 instance with Max Server
Memory set to 32GB:
Number of Plans Plan Cache Size (MB)
14402 2859
Almost 3GB of memory is being used to cache plans, so it's signii cant enough to investigate the
usage details. The following script breaks down the plan cache size by cached object type:
SELECT objtype AS 'Cached Object Type',
count(*) AS 'Number of Plans',
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)',
avg(usecounts) AS 'Avg Use Count'
FROM sys.dm_exec_cached_plans
GROUP BY objtype
The results are as follows:
Cached Object Type Number of Plans Plan Cache Size (MB) Avg Use Count
UsrTab 10 0 222
Prepared 286 72 4814
View 216 20 62
Adhoc 13206 2223 39
Check 30 0 7
Trigger 22 5 1953
Proc 738 554 289025
As you can see, most of the plan cache is taken up with ad-hoc plans, with an average use of 39,
which is quite low; therefore, you now want to determine how many of those are single-use plans by
modifying the earlier cache sizing script:
SELECT count(*) AS 'Number of Plans',
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)'
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1
AND objtype = 'adhoc'
Here are the results:
Number of Plans Plan Cache Size (MB)
12117 332
This indicates that 332MB of cache is being used for plans that aren't being reused, which isn't a huge
amount on this server, but it's completely wasted, so there's no reason not to get rid of these plans.