SQL Server unused indexes

Updating an index that is never used for searching is just a waste of cpu and diskspace, the query below can be run in a database and shows you which are not used and the size of the index.

Note, this is from the last instance reboot, if you have recently restarted than maybe you see wrong data. Or maybe you have an index that is only used for monthly reporting…or something, don’t just drop the indexes without carefully checking why it is created and its purpose.

Anywho, here’s the code…

SELECT d.name + '.' + s.name + '.' + o.name AS objectName
, i.name AS indexName
, MAX(ius.user_updates) AS userUpdates
, MAX(ius.user_seeks) AS userSeeks
, MAX(ius.user_scans) AS userScans
, MAX(ius.user_lookups) AS userLookups
, CASE
    WHEN SUM(a.used_pages) * 8 < 1024 THEN CONVERT(VARCHAR(32), SUM(a.used_pages) * 8) + ' kB'
    WHEN SUM(a.used_pages) * 8 / 1024 < 1024 THEN CONVERT(VARCHAR(32), SUM(a.used_pages) * 8 / 1024) + ' MB'
    ELSE CONVERT(VARCHAR(32), SUM(a.used_pages) * 8 / 1024 / 1024) + ' GB'
END AS indexSize
FROM sys.indexes i
INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.dm_db_index_usage_stats ius ON ius.[object_id] = i.[object_id]
                             AND i.index_id = ius.index_id 
INNER JOIN sys.databases d ON d.database_id = ius.database_id
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 p.[partition_id] = a.container_id
WHERE ius.user_updates > 0
AND ius.user_seeks + ius.user_scans + ius.user_lookups <= 0
AND i.is_primary_key = 0
GROUP BY d.name, s.name, o.name, i.name
ORDER BY MAX(ius.user_updates) DESC;
GO

Leave a Reply

Your email address will not be published. Required fields are marked *