I'm just going to decorate this post with a much more upbeat photo of Denise Milani in a captivating, top-heavy series of photographs in a Southwest desert setting.
Thinking of how big things are, I recently ran into an issue of an index file group growing pretty large, and I wanted to know which indexes were in it, and how big they were. My biggest concern was non-clustered indexes that were changed to clustered indexes (causing the whole table to move into the index file group).
I have previously found two great scripts, one that shows the size of an index and another that shows what is in a file group. I combined these two together to get a complete picture of what I needed.
Script for Indexes Size and What File Group they are in:
Select FileGroup = FILEGROUP_NAME(a.data_space_id),index_listing.* from (
s.[name] as [schema],
When i.[name] is null
end as name,
space_used_in_mb = (page_count * 8.0 / 1024.0),
space_used_in_kb = (page_count * 8.0),
sys.dm_db_index_physical_stats(db_id(), object_id('.'), null, null, null) P
On I.[object_id] = P.[object_id]
and I.[index_id] = P.[index_id]
on i.[object_id] = o.[object_id]
Inner Join sys.schemas s
on o.[schema_id] = s.[schema_id]
INNER JOIN sys.partitions p on index_listing.object_id = p.object_id
AND index_listing.index_id = p.index_id
Left Join sys.allocation_units a ON a.container_id = CASE WHEN a.type in(1,3)
THEN p.hobt_id ELSE p.partition_id END AND p.object_id > 1024
--where FILEGROUP_NAME(a.data_space_id) = 'Index_data'
-- and index_listing.type_desc = 'Clustered'
Order by space_used_in_mb Desc