How to find out what's in Buffer Cache, what's consuming the most memory| SQL Server

In this post we will see and learn what databases or tables/indexes were consuming the most memory/buffer in your database. When we talk about memory usage in SQL Server, we are often referring to the buffer cache.

Buffer Cache

The buffer cache in SQL Server is the memory that allows you to query frequently accessed data quickly. When data is written to or read from a SQL Server database, the buffer manager copies it into the buffer cache (aka the buffer pool). When it’s full, older or less frequently used data pages are moved to the hard disk.

Monitoring Buffer Cache

Memory use can have a significant impact on performance. When there is insufficient memory, data pages get purged frequently from the buffer cache. This slows down queries because SQL Server has to go to the disk to find the data page, restore it to the buffer cache, and then read the page before it can return query results.

There are many reasons why queries start to run slowly. But if you want to rule out memory trouble, look at what is going on inside the buffer cache. A peek inside it will identify which database, table, or index is hogging memory and putting pressure on the buffer.

Query to find out the current state of memory usage in SQL Server by checking the sys.dm_os_sys_info DMV

SELECT
physical_memory_kb,
virtual_memory_kb,
committed_kb,
committed_target_kb
FROM sys.dm_os_sys_info;

Query to find what database consumes the most memory:

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;

Query to find what table/index consumes the most memory:

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; 

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com .