SQL SERVER – DMV – For finding the Page count, index level, record count of an Index

This is a small post for finding the page count, index level and record count of an index. This script will definitely help you in Performance Tuning projects.

 

select i.name,i.type_desc,s.page_count,s.record_count,s.index_level from
sys.indexes i
inner join sys.dm_db_index_physical_stats(DB_ID(N'DatabaseName'),
object_id(N'TableName'),null,null,'Detailed')as s
on i.index_id = s.index_id
where i.object_id = object_id(N'dbo.TableName')