SQL Server 2005 TSQL to obtain buffers by object (table, index) in the buffer cache?
As a programmer interacting with SQL Server's cache is not often needed, but when you do need to determine what is going on with the cache, or you simply need to flush the execution plans or data pages to tune a query, you now have the means to do so.
Below is the TSQL I have used to obtain buffers that are used within memory by object (SQL server) to explore the use of the above statements and system tables to round out your knowledge and impress others when there is a problem with the cache and you know exactly what command or system table to use to troubleshoot the error:
select b.database_id, db=db_name(b.database_id)
,p.object_id
,object_name(p.object_id) as objname
,p.index_id
,buffer_count=count(*)
from sys.allocation_units a,
sys.dm_os_buffer_descriptors b,
sys.partitions p
where a.allocation_unit_id = b.allocation_unit_id
and a.container_id = p.hobt_id
and b.database_id = db_id()
group by b.database_id,p.object_id, p.index_id
order by buffer_count desc
