For some work I am doing in terms of TempDB growing size, I wanted to see what role the Procedure Cache plays.
We will look at the Procedure Cache in terms of number of entries and actual size.
But, we will not divvy things up in terms of type nor characteristics of the contents.
Here is a bit of tooling on how to get the size of your Procedure Cache.
declare @tblMemoryStatus TABLE ( [id] int not null identity(1,1) , [item] varchar(255) , [value] bigint , [memoryMB] as case when [item] like '%Pages' then [value] /128 else null end , [memoryGB] as cast ( case when [item] like '%Pages' then ( ( [value] * 1.00 / 128) / 1024 ) else null end as decimal(10, 2) ) ) insert into @tblMemoryStatus ( [item] , [value] ) exec ('dbcc memorystatus') select [item] , [value] , [memoryMB] , [memoryGB] from @tblMemoryStatus where [item] in ( 'TotalProcs' , 'TotalPages' , 'InUsePages' )
DBCC PROCCACHE WITH NO_INFOMSGS
SELECT [object_name] , [counter_name] , [instance_name] , [cntr_value] , [cntr_type] , [memoryMB] = case when [counter_name] like '%Pages%' then cntr_value /128 else null end , [memoryGB] = cast ( case when [counter_name] like '%Pages%' then ( ( cntr_value * 1.00 / 128) / 1024 ) else null end as decimal(10, 2) ) FROM sys.dm_os_performance_counters tblDOPC where tblDOPC.object_name like 'SQLServer:Plan Cache%' and tblDOPC.[instance_name] = '_Total' and tblDOPC.[counter_name] in ( 'Cache Pages' , 'Cache Object Counts' , 'Cache Objects in use' ) ;
Using perfmon, add metrics for SQLServer:PlanCache
Within SQL Server someone with sysadmin privileges can use a variety of dbcc commands such as “dbcc memorystatus” and “dbcc proccache” to get the size of the Procedure Size.
Performance Counters are also exposed from within SQL Server via sys.dm_os_performance_counters or through the OS via Performance Counters ( perfmon ).