TSQL to Determine Plan Cache Size

Download PDF

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.

Tagged with: , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.