SQL SERVER – Cached Data Per Object in Memory

“How to build a list of cached data per object in memory pool?” I just received this question from my client who hired me for of Comprehensive Database Performance Health Check. My client had already read my earlier blog post about SQL SERVER – Script to Identify Memory Used By Each Database.

SQL SERVER - Cached Data Per Object in Memory cachedobject-800x233

Here is the script to list Cached Data Per Object in Memory.

SELECT COUNT (1) * 8 / 1024 AS MBUsed, 
    OBJECT_SCHEMA_NAME(object_id) SchemaName, 
	name AS TableName, index_id   
FROM sys.dm_os_buffer_descriptors AS bd   
    INNER JOIN   
    (  
        SELECT object_name(object_id) AS name   
            ,index_id ,allocation_unit_id, object_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     
            ,index_id, allocation_unit_id, object_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  
WHERE database_id = DB_ID()  
GROUP BY OBJECT_SCHEMA_NAME(object_id), name, index_id   
ORDER BY COUNT (*) * 8 / 1024 DESC
GO

When you run the script, it will give you output similar to the following:

SQL SERVER - Cached Data Per Object in Memory cacheddata

Each index is an object of its own and it is quite possible that queries have used any index instead of the base table. That is why the query also includes the id of the index, which you can look up in the sys.index to get further details about that particular index.

This query can be also used to identify unused indexes, but I will rather use SQL SERVER – Unused Index Script – Download instead of this query. Well, that’s it for today. If you have any questions, please do reach out to me on Twitter.

Let me know if you are interested to know more about this topic and I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

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

Buffer Pool, SQL DMV, SQL Memory, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Disable Adaptive Joins
Next Post
SQL SERVER – Top 1 and Index Scan

Related Posts

2 Comments. Leave new

  • Hey Pinal! It’s been too long!

    So what happens when you run this on a production server with lots of RAM? ;-)

    Reply

Leave a Reply