A Script A Day - Day 17 - Possible Poor Indexes

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.co.uk

      -----------------------------------------------------------------

      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