select
i.[object_id],
i.index_id,
i.NAME AS Index_Name,
i.type_desc,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a
on (a.type = 2 AND p.partition_id = a.container_id)
OR ((a.type = 1 OR a.type = 3) AND p.hobt_id = a.container_id)
--where
-- i.[object_id] = object_id('dbo.thebakingdba_fanmail') --wishful thinking
-- and i.type = 1 -- clustered index
order by
i.name --p.partition_number
go
Monday, September 15, 2008
[Index] Find the size of your indexes
Something found online (originally by Alejandro Mesa). This is doubly handy now that 2005 can tell us which indexes aren't frequently used. Thanks to N8WEI for the correction on sys.allocation_units.
Subscribe to:
Post Comments (Atom)
2 comments:
Thanks for this! This helped me immensely. I've actually joined this against the output from sys.dm_db_index_physical_stats(), to give me more information about the physical size of the indexes.
Just a quick note... Not sure which version of SQL Server you wrote this for, but the SQL2005 documentation states the following about the container_id field:
If type = 1 or 3, then container_id = sys.partitions.hobt_id.
If type is 2, then container_id = sys.partitions.partition_id.
0 = Allocation unit marked for deferred drop
So I updated your join to read as follows:
INNER JOIN sys.allocation_units AS a
on (a.type = 2 AND p.partition_id = a.container_id)
OR ((a.type = 1 OR a.type = 3) AND p.hobt_id = a.container_id)
It looks like all of the same data came back in my scenereo, but that join statement would be more "correct" according to the documentation...
Figured you could use the comment too... :)
I believe Alejandro wrote this for 2000. The DMV you mentioned should provide some similar stats... but looking, it doesn't actually include the size. Odd, now that I think of it, since that's useful to have.
I'll try that improved join and see how it looks on my tables. And fix it here.
And finally, yes, thanks very much for the comment. Sometimes I think I'm writing for an audience of one - but it appears I'm not! :-D
Post a Comment