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.

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:

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.

• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

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