[Indexes] Size of unused indexes

Deprecated: use http://thebakingdba.blogspot.com/2011/09/index-size-usage-and-location-of-your.html
which includes the filegroup and location.


Was reading an old post of mine, and wanted to revisit it and add the ability to check the sizes of unused indexes. Pretty easy.

Updated this from a subquery to an inner join, which gives you the usage stats as well.

select

-- i.[object_id],

-- i.index_id,

 o.name as Table_Name,

 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)],

 s.user_seeks,

 s.user_scans,

 s.user_lookups

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.OBJECTS O

 ON I.OBJECT_ID = O.OBJECT_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)

INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S

  ON S.OBJECT_ID = I.OBJECT_ID

  AND I.INDEX_ID = S.INDEX_ID

  AND DATABASE_ID = DB_ID(DB_NAME())

and o.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE')           -- No system tables!

AND (ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0)) < 100

WHERE    

 OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1

 AND i.TYPE_DESC <> 'HEAP'

 and i.type <> 1 -- clustered index

order by

o.NAME, i.name

GO