Tyring To Figure Out What's In The Buffer Pool

Hi, I'm trying to chase down some bottlenecks, and am currently tyring to figure out what's actually in our data buffer pool.

We've recently upgraded to SQL Server 2005 (sp2a); there's 4GB memory on the box (an active/passive cluster) with the /3GB switch set. I'm working on the learning curve for

sys.dm_os_buffer_descriptors and sys.allocation_units [and boy I sure wish SSMS's query windows wouldn't "copy" in HTML]. Based on BOL and some poking around, I've come up with the following query to list pages used within a given database:



SELECT

count(*) cached_pages_count

,isnull(obj.name, '<unidentified object>') TableName

,obj.index_id

,ind.name

from sys.dm_os_buffer_descriptors bd

left outer join (select

object_name(object_id) name

,object_id

,index_id

,allocation_unit_id

from sys.allocation_units au

inner join sys.partitions p

on au.container_id = p.partition_id -- 2005 compatible, but maybe not in future versions

) obj

on bd.allocation_unit_id = obj.allocation_unit_id

left outer join sys.indexes ind

on ind.object_id = obj.object_id

and ind.index_id = obj.index_id

where bd.database_id = db_id()

group by obj.name, obj.object_id, obj.index_id, ind.name

order by cached_pages_count desc



This would appear to list how many pages are sitting in our buffer pool for which objects for the currently selected database. The thing is, for our "main" database, the vast majority of pages fall in that "unidentified" bucket -- their allocation_unit_ids are not in sys.allocation_units (or tempdb, I checked there just in case).

My question is: what are these pages? Where is this data coming from? Might these somehow be related with our execution/query cache, which appears to be larger than our data cache?

As may be obvious, this is all new to me, and any help would be greatly appreciated!





t