Determing the index size on table
Getting all the relevant information needed to see your indexes on the table:
select
ps.database_id
,ps.object_id
,o.name
,o.type_desc
,ps.index_id
,b.name as index_name
,ps.avg_fragmentation_in_percent
,ps.avg_page_space_used_in_percent
,ps.avg_fragment_size_in_pages
,ps.fragment_count
,ps.page_count
,ps.record_count
,ps.index_type_desc
,ps.min_record_size_in_bytes
,ps.max_record_size_in_bytes
,ps.avg_record_size_in_bytes --in bytes
,ps.record_count*ps.avg_record_size_in_bytes as index_size_in_bytes
,(ps.record_count*ps.avg_record_size_in_bytes)/1024 as index_size_in_KiloBytes
,(ps.record_count*ps.avg_record_size_in_bytes)/1048576 as index_size_in_MegaBytes
,(ps.record_count*ps.avg_record_size_in_bytes)/1073741824 as index_size_in_GigaBytes
,ps.alloc_unit_type_desc
,ps.partition_number
,ps.index_level
,ps.index_depth
,b.fill_factor
,b.allow_row_locks
,b.allow_page_locks
from
sys.dm_db_index_physical_stats (16, 926626344, null, null, 'SAMPLED') as ps -- (databaseID, ObjectID,null,null,'SAMPLED')
inner
join sys.indexes as b
on ps.object_id = b.object_id
and ps.index_id = b.index_id
inner
join sys.all_objects as o
on o.object_id = ps.object_id
where
ps.database_id = 16 --db_id(N'') --DatabaseID
and
b.object_id = 926626344 --ObjectID
order
by ps.object_id