TSQL – get sizes of unused indexes

Here’s a TSQL snippet that will give you the sizes of the indexes that the query optimiser isn’t using. ┬áNote that the NULL values are SINCE THE LAST RESTART. ┬áSo if you just restarted your server then this is rather a waste of time.

SELECT object_name(ui.object_id), si.name as [Index Name],ui.index_id, ui.last_user_seek, ui.last_user_scan, ui.user_seeks, ui.user_scans,
8 * sa.used_pages AS [Index Size]
from sys.dm_db_index_usage_stats ui
inner join sys.indexes si on ui.object_id=si.object_id and ui.index_id=si.index_id
sys.partitions sp ON si.object_id = sp.OBJECT_ID AND si.index_id = sp.index_id
INNER JOIN sys.allocation_units AS sa ON sa.container_id = sp.partition_id
where database_id=7
and object_name(ui.object_id) not like ‘sys%’
and ui.last_user_seek is NULL
and ui.last_user_scan is NULL
ORDER by 10 desc

Leave a Comment

Your email address will not be published.