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.
Click through for the script.