Follow SQLMaster on Twitter
Welcome to SqlServer-QA.net Sign in | Help

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

Published Friday, February 01, 2008 5:30 AM by SQL Master

Comments

# 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

Friday, February 01, 2008 5:36 AM by Other SQL Server Blogs around the Web

# 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

Friday, February 01, 2008 6:06 AM by SSQA.net - SqlServer-QA.net
Anonymous comments are disabled