You are here
Home > SQL Server

Index Size and Usage

Thought I’d share something I threw together for a client today.  They were looking for index a list of indexes in a database with their associated sizes and usage.

SELECT object_name(i.object_id) as table_name
,COALESCE(, space(0)) as index_name
,Cast((ps.reserved_page_count * 8)/1024. as decimal(12,2)) as size_in_mb
,COALESCE(ius.user_seeks,0) as user_seeks
,COALESCE(ius.user_scans,0) as user_scans
,COALESCE(ius.user_lookups,0) as user_lookups
FROM sys.all_objects t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id ANDi.index_id = ps.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = db_id() ANDi.object_id = ius.object_id AND i.index_id = ius.index_id
ORDER BY object_name(i.object_id),

Using this WHERE statement allowed them to identify 38GB of index space that was being allocated unnecessarily for indexes.

WHERE i.type_desc NOT IN ('HEAP', 'CLUSTERED')
AND i.is_unique = 0
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND COALESCE(ius.user_seeks,0) <= 0
AND COALESCE(ius.user_scans,0) <= 0
AND COALESCE(ius.user_lookups,0) <= 0 &#91;/sourcecode&#93; 

As with most DMVs these stats are accumulated since the last server start.  This should just give a good starting point for finding unnecessary indexes and determining by their size if dropping the index would have a significant impact on database space.  It would not be advisable to do a bit more research before taking any action.

Also, I ran the following query and got some interesting results.

&#91;sourcecode language='sql'&#93;
select * from users where clue > 0

(0 row(s) affected)

OK, I saw it on a shirt… Hehe…

No Clue

  • singhswat

    Great Stuff, Thanks!