One of the best DMVs I've seen in SQL Server 2008
This will give you all the information about the pages in your database which are in buffer.
This is indeed an excellent way to find out the pages of your tables you are querying frequently are in buffer or not.
I just love this DMV and 'll start using it for my troubleshooting
more info at here:
http://msdn.microsoft.com/en-US/library/ms173442%28v=SQL.90%29.aspx
Output will be something like this:
database_id file_id page_id page_level allocation_unit_id page_type row_count free_space_in_bytes is_modified
5 1 13200546 0 72057612741115904 DATA_PAGE 18 680 0
5 1 13200547 0 72057612741115904 DATA_PAGE 18 680 0
5 1 13200548 0 72057612741115904 DATA_PAGE 18 680 0
5 1 13200546 0 72057612741115904 DATA_PAGE 18 680 0
5 1 13200547 0 72057612741115904 DATA_PAGE 18 680 0
5 1 13200548 0 72057612741115904 DATA_PAGE 18 680 0
The below query will give you the table names and the data pages which are in buffer for each database, this excludes objects from system databases.
select db_name(d.database_id),d.page_type,d.row_count,object_name(p.object_id),p.rows,p.index_id
from sys.dm_os_buffer_descriptors d
inner join sys.allocation_units a
on a.allocation_unit_id=d.allocation_unit_id
inner join sys.partitions p
on p.partition_id=a.container_id
where d.database_id not in(1,2,3,4) and db_name(d.database_id) is not null
from sys.dm_os_buffer_descriptors d
inner join sys.allocation_units a
on a.allocation_unit_id=d.allocation_unit_id
inner join sys.partitions p
on p.partition_id=a.container_id
where d.database_id not in(1,2,3,4) and db_name(d.database_id) is not null
No comments:
Post a Comment