SQL SERVER – Knowing Which Database is Consuming My Memory

I have been fortunate enough to be at conferences around the world and it is always refreshing to see how people come up with some really common yet never answered questions from time to time. The classic questions I get asked ever since I started working with databases and SQL Server is – why does SQL Server take all the memory and not return it? Even more bizarre is the question – Can I know how much memory is my databases using?

I always tell them, memory is a big topic and we need to use a number of commands like DBCC MEMORYSTATUS to know the internal working. The much more interesting way is to find out what are the pages in our buffer pool for our various databases. This can be got using DMVs as shown below:

--List the Number of pages in the buffer pool by database and page type
SELECT DB_NAME(database_id),
page_type,
COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
WHERE database_id! = 32767
GROUP BY database_id, page_type
ORDER BY number_pages DESC
GO
--List the number of pages in the buffer pool by database
SELECT DB_NAME(database_id),
COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
WHERE database_id! =32767
GROUP BY database_id
ORDER BY database_id
GO

As you can see in the above output, we can see the amount of data pages and index pages that are loaded into our SQL Server memory.

A small variation of the above query can be to scan the buffer pool based on the type of pages that are loaded into memory. Below is a typical query fired against the same DMV.

--List the number of pages in the buffer pool by page type
SELECT page_type, COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
GROUP BY page_type
ORDER BY number_pages DESC
GO
--List the number of dirty pages in the buffer pool
SELECT COUNT(page_id) AS number_pages
FROM sys.dm_os_buffer_descriptors
WHERE is_modified = 1
GO

In the above query, I have also shown the dirty pages that are in memory and are yet to be flushed out.

This DMV is super useful when you have a number of databases that are running on our server and want to find out who is consuming the server memory. Do let me know your thoughts and what output are you seeing in your environment. Is there anything strange that you fine? Let me know via your comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)