“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)
Hey Pinal! It’s been too long!
So what happens when you run this on a production server with lots of RAM? ;-)
… it takes forever to return the data!