Cleveland DBA

SQL Server administration in Cleveland, OH

How is SQL Server using all that memory, anyway?

I want to stick with the subject of memory because I think it’s probably the most misunderstood (and sometimes downright mysterious) component of SQL Server. I know I, for one, could benefit from a better understanding of its inner workings. So today I’d like to share that little bit of code I mentioned last week, that I find very handy for getting an overall picture of how memory is being used by a SQL instance. Personally I run it as a complete script, but I’ll break it down here and provide a little description of each section. I should say, too, that I didn’t write any of this from scratch. For most of it I started with code found here, here, and here, and modified it to suit.

So, without further ado, let’s get started. The first section provides an big-picture look at how SQL is currently allocating memory between the database page cache, procedure cache, and miscellaneous memory usage. It also provides the total memory usage, and I added in the maximum available workspace memory.


declare @plan_cache_size float, @obj_data_size float , @avail_workspace_size float

-- plan cache size

select @plan_cache_size = (cntr_value*8)/1024.0 FROM sys.dm_os_performance_counters
	WHERE object_name like '%Plan Cache%' and counter_name = 'Cache Pages' and instance_name = '_Total'


select @obj_data_size = (cntr_value*8)/1024.0  FROM sys.dm_os_performance_counters
	WHERE object_name like '%Buffer Manager%' and counter_name like 'Database pages%'

-- Maximum workspace available for sorts, hashes, etc

select @avail_workspace_size = (cntr_value)/1024.0 FROM sys.dm_os_performance_counters
	WHERE object_name like '%Memory Manager%' and counter_name = 'Maximum Workspace Memory (KB)'

select   @obj_data_size [Database_Page_Cache_MB]
 , @plan_cache_size [Procedure_Cache_MB]
 , [Misc_Memory_Usage_MB]
 , [Misc_Memory_Usage_MB] + @obj_data_size + @plan_cache_size [Total_Memory_Usage_MB]
 , @avail_workspace_size [Maximum_Workspace_MB]
 select sum(cntr_value)/1024.0 [Misc_Memory_Usage_MB]
 from sys.dm_os_performance_counters
 where object_name like '%memory%'
 and (
   counter_name like '%Connection Memory (KB)%'
   counter_name like '%Granted Workspace Memory (KB)%'
   counter_name like '%Lock Memory (KB)%'
   counter_name like '%Optimizer Memory (KB)%'
   counter_name like '%SQL Cache Memory (KB)%'
) x

The output:

The next section uses sys.dm_os_memory_cache_counters to examine how the procedure cache is being broken down.

-- How the Procedure cache is being used

	LEFT([name], 20) as [name],
	LEFT([type], 20) as [type],
	([single_pages_kb] + [multi_pages_kb])/1024 AS cache_mb,
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC

The output:

The third part of the script goes deeper into the procedure cache and displays the top 25 cached plans by size. Because it displays some application code, I’m not going to include the results here.

-- Top cached plans by size

select top 25
, ObjType
, (pagesUsed * 8192)/1024.0/1024.0 [space_used_MB]
, db_name(dbid) [database_name]
, object_name(objid, dbid) [object_name]
, [sql]
from master..syscacheobjects (nolock)
order by pagesUsed desc

And, finally, the last section shows us how much of the buffer pool is being utilized by each database, including the dirty and clean page counts, sorted by total memory.

-- Buffer Pool Memory Per DB

(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS DirtyPageCount,
SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS CleanPageCount,
count(*)AS TotalPageCount,
cast(count(*) * 8192.0 / (1024.0 * 1024.0) as decimal(8,2)) as BufferPoolMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY TotalPageCount desc

And the output:

And there you have it. I hope you find this useful!


May 2, 2011 - Posted by | General, SQL Tuning, Troubleshooting | , , ,

1 Comment »

  1. […] And there you see the same counters for Buffer Manager that you’d see if you ran Perfmon. This is one of the DMVs I use in my script to see how SQL Server is using its memory;. […]

    Pingback by SQL Server A to Z – DMV « Cleveland DBA | July 21, 2011 | Reply

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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

%d bloggers like this: