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)
This one is going in my scripts folder, thanks!
This is going to be really helpful on Monday…for “that” client and their 32GB RAM 600GB of databases
I totally agree sir.
What about execution plan cache?
It has a NULL field with some MB calculated on that. Why is it so?
Why a COUNT(1) in the SELECT clause and a COUNT(*) in the ORDER BY clause?