Today’s script is one I’ve taken from a job I use to collect possible poor indexes. I’ve tweeked it slightly so you can choose the database to run it against.
It goes without saying that you should never just remove an index without some considerable investigation doesn't it???!!!
/*
-----------------------------------------------------------------
Possible poor indexes
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Change db context
USE DatabaseNameHere;
GO
-- Get Possible poor indexes
SELECT
OBJECT_NAME(S.[object_id]) AS [Table Name],
I.name AS [Index Name],
I.index_id,
user_updates AS [Total Writes],
(user_seeks + user_scans + user_lookups) AS [Total Reads],
(user_updates - (user_seeks + user_scans + user_lookups)) AS [Difference]
FROM
sys.dm_db_index_usage_stats AS S WITH (NOLOCK)
INNER JOIN sys.indexes AS I WITH (NOLOCK) 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 = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND I.index_id > 1
ORDER BY
[Difference] DESC,
[Total Writes] DESC,
[Total Reads] ASC
OPTION (RECOMPILE);
GO
Enjoy!
Chris
