Indexes are bad?
- February 15th, 2010
- Write comment
Indexes on your tables are not always a good thing. For example, a clustered index in the wrong place can dramatically slow down your update, insert and delete speeds. Indexes that are not being used sap valuable resources away from your database engine to maintain them. There are many instances where indexes will not enhance performance but for now I just want to deal with redundant ones.
I cannot take credit for this script and I cannot remember where I found it (please leave a comment if you know the original source and I will add the credit in). It finds all indexes (in the context of the database you are connected to) and orders them according to the total of index seeks, scans and lookups against them. Any of those with zero to very little should be dropped if resources are skant. It is the best one I have found to date. Again if you have found a better one please leave a comment.
Enjoy…
SELECT QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS TableName,
i.name AS IdxName,
i.type_desc AS IdxType,
ius.user_seeks,
(ius.user_seeks*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Seek],
ius.user_scans,
(ius.user_scans*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Scan],
ius.user_lookups,
(ius.user_lookups*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Lookup],
ius.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
ON ius.object_id = i.object_id
AND ius.index_id = i.index_id
AND database_id = DB_ID()--Current DB
INNER JOIN sys.tables t
ON t.object_id = i.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.type = 'U'
AND t.is_ms_shipped = 0
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups ASC
