If you have ever wondered what database or tables/indexes were consuming the most memory/buffer in your database, this post can help with that.

How do I see this?

The key DMV’s to view this  are sys.dm_os_buffer_descriptors, sys.allocation_units, and sys.partitions.  The key piece to identifying what is currently in the buffer cache is sys.dm_os_buffer_descriptors.  The use of sys.allocation_units and sys.partitions are used for decoding the objects (i.e. tables/indexes) associated with the buffers.

How to see what database consumes the most memory

Here’s the query:

SELECT
CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS database_name, COUNT(1)/128 AS megabytes_in_cache
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,database_id
ORDER BY megabytes_in_cache DESC;

Here is an example of the output:

How to see what table/index consumes most memory

You must run this query in the database you would like to inspect.  Typically I would use the top database name from the query above.  Here’s the query:

SELECT COUNT(1)/128 AS megabytes_in_cache
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id
ORDER BY megabytes_in_cache DESC;

Here is an example of the output:

Caveats

This information is only good for the current point in time.  Both of these queries are only displaying what is currently in memory.

Parting thoughts

If one database or object is consuming a majority of your memory it doesn’t always indicate a problem.  If your system is not under buffer pressure (i.e. PAGEIOLATCH waits being prevalent), large amounts of the buffer being consumed by one particular database or object probably isn’t an issue.  If you find a non-clustered index using large amounts of space, you might want to explore if the index is unused.