Memory Consumption by Object

SQL Server caches object data in memory in the buffer pool. Understanding memory consumption by object can be crucial for performance. For instance, you may have a large logging table consuming 90% of the buffer pool. Moving older rows out of the logging table might allow you to reduce memory consumption if you really only care about the last 2 weeks of log records. The script below takes a snapshot of sys.dm_os_buffer_descriptors, then links it to the objects in each database on the instance. The output shows which objects are in memory, along with how much memory is being consumed by each object.

Edouard Cortès - Rue du Temple et la Place de la Republique - I bet Parisians at the time weren't concerned with memory consumption by object!

Edouard Cortès – Rue du Temple et la Place de la Republique – I bet Parisians at the time weren’t concerned with memory consumption by object!

The script returns two result sets showing memory consumption by object. The first set contains a summary of total megabytes consumed by database and page-type. The second set shows the same details down to the object level.

Let me know if you have a question about this script, or if you notice something wrong.

Check out the rest of our posts on SQL Server Performance.