select d.database_id,d.file_id,d.page_id,d.page_type,d.row_count,object_name(p.object_id),a.allocation_unit_id
from sys.dm_os_buffer_descriptors d
left outer join sys.allocation_units a
on d.allocation_unit_id=a.allocation_unit_id
inner join sys.partitions p
on a.container_id=p.partition_id
where d.database_id>4
group by
d.database_id,d.file_id,d.page_id,d.page_type,d.row_count,object_name(p.object_id),a.allocation_unit_id
This query will give you all the objects in Buffer group by Database.
Remember, db_id of 32767 is Resource DB
Also just posted a question to Paul, waiting for a response from him:
When I the run the following query in sql server 2005
select * from sys.dm_os_buffer_descriptors where is_modified<>0
database_id | file_id | page_id | page_level | allocation_unit_id | page_type | row_count | free_space_in_bytes | is_modified |
2 | 1 | 28919 | 0 | 1.48948E+14 | INDEX_PAGE | 0 | 8096 | 1 |
2 | 1 | 143 | 0 | 1.48948E+14 | INDEX_PAGE | 0 | 8096 | 1 |
2 | 1 | 152 | 0 | 5.6295E+14 | INDEX_PAGE | 135 | 3230 | 1 |
2 | 1 | 153 | 0 | 2.81475E+14 | INDEX_PAGE | 3 | 8045 | 1 |
2 | 1 | 154 | 0 | 2.81475E+14 | INDEX_PAGE | 3 | 8045 | 1 |
what does “is_modified” column signify?
Does it mean that the data has been modified and still not committed?
Also I could see rows with file_id=2(ldf files) when I run the below:
select * from sys.dm_os_buffer_descriptors where file_id>1
Tlog doesn’t have pages, so I could see page_id As zero but the page_types are confusing.
database_id | file_id | page_id | page_level | allocation_unit_id | page_type | row_count | free_space_in_bytes | is_modified |
7 | 2 | 0 | 0 | 6488064 | FILEHEADER_PAGE | 2 | 6892 | 0 |
2 | 2 | 0 | 0 | 2.81475E+14 | DATA_PAGE | 5 | 4411 | 0 |
5 | 2 | 0 | 0 | 7.20576E+16 | DATA_PAGE | 16 | 192 | 0 |
4 | 2 | 0 | 0 | 6488064 | FILEHEADER_PAGE | 2 | 6900 | 0 |
10 | 2 | 0 | 0 | 6488064 | FILEHEADER_PAGE | 2 | 6884 | 0 |
1 | 2 | 0 | 0 | 7.20576E+16 | IAM_PAGE | 2 | 6 | 0 |
When I ran the below query to find out the objects these allocations are associated to I didn’t get anything.
select d.database_id,d.allocation_unit_id,
(
select object_id from sys.partitions where
partition_id=(select container_id from sys.allocation_units where allocation_unit_id=d.allocation_unit_id)
)
from sys.dm_os_buffer_descriptors d where file_id>1
database_id | allocation_unit_id | (No column name) |
7 | 6488064 | NULL |
3 | 6488064 | NULL |
4 | 1.92626E+14 | NULL |
10 | 2.81475E+14 | 60 |
2 | 2.81475E+14 | 60 |
1 | 7.20576E+16 | NULL |
5 | 7.20576E+16 | NULL |