SQL Server – Determining Size of Your Procedure Cache

Background

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.

 

Instrumentation

Here is a bit of tooling on how to get the size of your Procedure Cache.

SQL Server

dbcc memorystatus

Script


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'
			)

 

Output

dbccmemorystatus__20170124__1149am

 

dbcc proccache

Script


DBCC PROCCACHE WITH NO_INFOMSGS 

Output

dbccproccache-hpalm-20170124-1206pm

Performance Counters

Script


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'                                                                                                            
			   )
				
;  


Output

peformancecounters_hpalm_prod_20170124_1154am

 

Performance Monitor

Using perfmon, add metrics for SQLServer:PlanCache

performancemonitor-hpalm-20170124-1158am

 

Summary

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 ).

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s