Wednesday, March 2, 2011

sys.dm_os_buffer_descriptors

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

No comments:

Post a Comment