TSQL to Determine Plan Cache Size

In SQL Server 2012, there was a change to the dynamic management view that reports on the plan cache, so the query has changed. Here are the before and after queries to determine the plan cache size in SQL Server 2012, and prior to SQL Server 2012.

SQL 2012 Plan Cache Size

-- How big is the plan cache SQL 2012
select name, sum(pages_kb) /1024.0 MBUsed
from sys.dm_os_memory_clerks
where name = 'SQL PLans'
group by name;

Pre-SQL 2012 Plan Cache Size

-- How big is the plan cache pre SQL 2012
select name, SUM(single_pages_kb + multi_pages_kb)/1024.0 MBUsed
from sys.dm_os_memory_clerks
where name = 'SQL PLans'
group by name;

For more details on the plan cache, one time use queries and queries needing parameterization, see the Database Health Project.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!