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

NOTE DBCC MEMORYSTATUS provides a lot of useful information about SQL
Server's memory state but you'll i nd that DMVs provide far more l exibility with
the output, so try to get used to i nding the same information from DMVs
whenever possible. The following DMVs are a good place to start:

➤

sys.dm_os_memory_nodes

➤

sys.dm_os_memory_clerks

➤

sys.dm_os_memory_objects

➤

sys.dm_os_memory_cache_counters

➤

sys.dm_os_memory_pools

The following example script uses sys.dm_exec_cached_plans to show the number of cached
plans and the total size in MB:

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

Running this on a production SQL Server 2012 instance with Max Server Memory set to 32GB pro-
duced the following results:

Number of Plans Plan Cache Size (MB)
14402 2859

NOTE This server-level option can help to reduce plan cache bloat by
not caching single-use ad-hoc plans. You can read about it in the section
“Optimizing SQL Server Memory Coni guration” later in the chapter.

The maximum size for the plan cache is calculated by SQL Server as follows:

➤

75% of server memory from 0-4GB +

➤

10% of server memory from 4GB-64GB +

➤

5% of server memory > 64GB

Therefore, a system with 32GB of RAM would have a maximum plan cache of 3GB + 2.8GB =
5.8GB.