How much memory is a SQL Server database using

An interesting request came in the other day, is there a way to tell how much memory each database is using on an instance. You can always use properties to see how much memory is allocated to the SQL Server and there are performance counters available to track how much memory an instance is using and determine if you need to allocate more by monitoring for a low buffer cache hit ratio. But to actually see how much memory each database on an instance is using, is a question I had never been asked. So out to Google I went and was amazed at the information I found. There is a DMV that is available that will give you this information” sys.dm_os_buffer_descriptors”.

So, off to analyze what information is really available in this DMV. First I took a look at the msdn description of the DMV.  Not only did I find it would give me information about the data pages loaded into the buffer pool for the database but it would also give me details by object and type. Nice!

My next step was to look at the data itself and see how useful it can be in the raw format. Having experience with other DMV I knew that just a simple select might not give me the information in a format that would be easy to read for an end user.

select database_id ,
 file_id ,
 page_id ,
 page_level ,
 allocation_unit_id ,
 page_type ,
 row_count ,
 free_space_in_bytes ,
 is_modified ,
 numa_node
 from sys.dm_os_buffer_descriptors

Yep, I was right… First let’s look at the database_ID field. Most people do not pay attention to the number SQL Server has assigned to the database; they always refer to the database by name. So, I know I will need to convert this information to something useful.  The function DB_Name() should come in handy here.

select DB_NAME(database_id)
 from sys.dm_os_buffer_descriptors

Hmmm… using the function did convert a lot of the database_ID to actual names, but I have all these null values. What does that represent? After a bit more research I find the null value from the function and the database_ID of 32767 represents the Resources Database internal to SQL server.  Time for a case statement in the SQL script.

select case
 when database_ID = 32767 then 'Resource Database'
 else DB_NAME(database_ID)
 end 'DBName'
 from sys.dm_os_buffer_descriptors

Now I can get a count of all the data pages that are loaded into the buffer pool by database.

select case
 when database_ID = 32767 then 'Resource Database'
 else DB_NAME(database_ID)
 end 'DBName'
 ,COUNT(1) 'Number of Pages'
 from sys.dm_os_buffer_descriptors
 group by database_ID

And then it is time for a little math to complete my calculations. Each data page is 8k in size. And there is 1024k in each MB (my sizing of choice) so I issue the follow SQL statement:

select case
 when database_ID = 32767 then 'Resource Database'
 else DB_NAME(database_ID)
 end 'DBName'
 ,COUNT(1) 'Number of Pages'
 ,COUNT(1) *8/1024 'Size'
 from sys.dm_os_buffer_descriptors
 group by database_ID

Now I have the information I need for the customer. They have a script they can run at different times to capture how much data is loaded into the buffer pool for each database at the time the query is executed.