SQL SERVER – Script to Identify Memory Used By Each Database

During the last SQL Server Performance Tuning Practical Workshop, I was asked a very interesting question by an attendee. The question goes like if there is any script available which users can use and figure out if how much memory used by each of the databases. Fortunately, I was lucky that I have a script handy which exactly does the same task.

Here is the script:

-- Memory used by each database
SELECT DB_NAME(database_id),
COUNT (1) * 8 / 1024 AS MBUsed
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY COUNT (*) * 8 / 1024 DESC
GO

If you run above script it gives us details about memory used by each of the databases. If you are the curious one, you can run SELECT * and read additional details. The script returns results in MB. Please remember that it retrieves the data from operating system’s buffer and it keeps on changing. However, this gives information at least relevant to the moment when we are running query.

Here is my earlier blog post about memory on this blog:

• What is Memory Grants Pending in SQL Server? – Interview Question of the Week #103
• SQL SERVER – How to Enable Lock Pages in Memory?
• How to Find SQL Server Memory Use by Database and Objects? – Interview Question of the Week #121
• SQL SERVER – Understanding Basic Memory Terms of Physical Memory and Virtual Memory

If you are using any other such query, do let me know and I am looking forward to the same one. I will post your query with due credit to you on this blog post.

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