Thursday, June 9, 2011

Identify Unused Indexes

Unused indexes have all sorts of negative affects on your database environment, from disk space consumption to performance related issues.  As queries and stored procedures change over time, so may the usage of indexes, often times to the point where they are only maintained (inserts/updates) but hardly read from.  Identifying these indexes can be done using the following script - you can adjust it to suit your needs.


-- Possible bad Indexes (writes > reads)
DECLARE @dbid INT
SELECT @dbid = DB_ID()

SELECT 'Drop Statement' = 'DROP INDEX ' + i.name + ' ON [dbo].'
+ OBJECT_NAME(s.object_id)
,CASE user_seeks + user_scans + user_lookups
WHEN 0 THEN 100
ELSE (ROUND(((user_updates)
/ CAST((user_updates + user_seeks + user_scans
+ user_lookups) AS FLOAT)),4)) * 100
END AS '% Writes'
,'Table Name' = OBJECT_NAME(s.object_id)
,'Index Name' = i.name
,i.fill_factor
,'Total Writes' = user_updates
,'Total Reads' = user_seeks + user_scans + user_lookups
,'Difference' = user_updates - (user_seeks + user_scans + user_lookups)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = @dbid
AND user_updates > (user_seeks + user_
scans + user_lookups)
AND i.index_id NOT IN (0,1)
ORDER BY '% Writes' DESC
,'Total Writes' DESC
,'Total Reads' ASC