Thursday 23 February 2012

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

No comments:

Post a Comment