How to Find SQL Server Memory Use by Database and Objects? – Interview Question of the Week #121

Question: How to Find SQL Server Memory Use by Database and Objects?

How to Find SQL Server Memory Use by Database and Objects? - Interview Question of the Week #121 memoryusage

Answer: The answer of this question is very simple, we can do this by going to sys.dm_os_buffer_descriptors.

Here is the first query which I use all the time to see which particular database is using the most memory in the SQL Server.

SELECT
[DatabaseName] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
COUNT_BIG(*) [Pages in Buffer],
COUNT_BIG(*)/128 [Buffer Size in MB]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id]
ORDER BY [Pages in Buffer] DESC;

Here is the result of the script listed above, which lists all the databases cached in memory.

How to Find SQL Server Memory Use by Database and Objects? - Interview Question of the Week #121 memoryusagescript1

Now let us see another query which returns us details about how much memory each object uses in a particular database.

SELECT obj.name [Object Name], o.type_desc [Object Type],
i.name [Index Name], i.type_desc [Index Type],
COUNT(*) AS [Cached Pages Count],
COUNT(*)/128 AS [Cached Pages In MB]
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name, object_id
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name, object_id
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
INNER JOIN sys.indexes i ON obj.[object_id] = i.[object_id]
INNER JOIN sys.objects o ON obj.[object_id] = o.[object_id]
WHERE database_id = DB_ID()
GROUP BY obj.name, i.type_desc, o.type_desc,i.name
ORDER BY [Cached Pages In MB] DESC; 

The above query will list all the objects and their type along with how much space they take in the memory.

How to Find SQL Server Memory Use by Database and Objects? - Interview Question of the Week #121 memoryusagescript2

If you ever wondered which object is taking the most memory in your database, you can use the above script for additional details.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, , , ,
Previous Post
What is the Initial Size of TempDB? – Interview Question of the Week #120
Next Post
What is the Difference Between Physical and Logical Operation in SQL Server Execution Plan? – Interview Question of the Week #122

Related Posts

4 Comments. Leave new

  • Ok, I found couple clustered and non clustered indexes taking more spaces, what’s next what do I need to do with those objects?

    Reply
  • You need to strike a balance between space consumed and performance of queries which are using those indexes.

    Reply
  • can you please make it clear that how do I reduce space consumed by CI and NCI?

    Reply
  • there is one join condition missing from “INNER JOIN sys.indexes i ON obj.[object_id] = i.[object_id]”. It should be
    “INNER JOIN sys.indexes i ON obj.[object_id] = i.[object_id] and i.index_id=obj.index_id”

    Reply

Leave a Reply

Menu