select top 20
db_name(a.database_id) as 'DBName', object_name(b.object_id, a.database_id) as 'OBJName'
, b.index_id, isnull(max(c.name), 'Heap') as 'IndexName', a.page_type
, left(cast(count(*) * 1.0 / max(b.used_pages) as varchar(100)), 6) as 'Ratio'
, count(*) as 'BufferPageCNT', max(b.used_pages) as 'UsedPageCNT', max(b.total_pages) as 'TotalPageCNT'
, sum(case when is_modified = 1 then 1 else 0 end) as 'ModifyCNT'
, count(*) * 8 / 1024.0 as 'BufferSize(MB)', sum(a.free_space_in_bytes) / 1024.0 / 1024.0 as 'FreeSpace(MB)'
, max(b.used_pages) * 8 / 1024.0 as 'TotalUsed(MB)', max(b.total_pages) * 8 / 1024.0 as 'TotalReserved(MB)'
, case when a.page_type = 'TEXT_MIX_PAGE' then cast(max(b.rows) * (count(*) * 1.0 / max(b.used_pages)) as bigint) else sum(a.row_count) end as 'BufferRowCNT'
, max(b.rows) as 'TotalRowCNT'
from sys.dm_os_buffer_descriptors a with(nolock)
inner join ( select a.allocation_unit_id, a.type_desc, a.total_pages, a.used_pages, b.object_id, b.index_id, b.rows
from sys.allocation_units a with(nolock)
inner join sys.partitions b with(nolock) on a.container_id = b.hobt_id and (a.type = 1 or a.type = 3)
union all
select a.allocation_unit_id, a.type_desc, a.total_pages, a.used_pages, b.object_id, b.index_id, b.rows
from sys.allocation_units a with(nolock)
inner join sys.partitions b with(nolock) on a.container_id = b.partition_id and a.type = 2
) b on a.allocation_unit_id = b.allocation_unit_id
left outer join sys.indexes c on b.object_id = c.object_id and b.index_id = c.index_id
where a.database_id = db_id() and b.used_pages > 0
group by a.database_id, b.object_id, b.index_id, a.page_type
order by count(*) desc
--order by Ratio desc