sys.dm_os_buffer_descriptors

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

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