Find out which database consumes how much memory

Question

I am working with MSSQL Server. I want to find out which of my databases in the server takes how much memory/space. A friend of mine told me that there is a "task" to do it

Answer 1

I had the same issue and this article seems to be the solution.

First,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

Then, 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

You can see from the output, you will be able to see the amount of data pages and index pages that are loaded into your SQL Server memory.

Try to 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

Then 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

You will display the dirty pages that are in memory and are yet to be flushed out.