I'm trying to discover indexes which may be in place which are only adding cost with no value to our database. I've come across sys.dm_db_index_usage_stats and from MSSQL Tips the following:

sys.dm_db_index_usage_stats

This view gives you information about overall access methods to your indexes.  There are several columns that are returned from this DMV, but here are some helpful columns about index usage:

  • user_seeks - number of index seeks
  • user_scans- number of index scans
  • user_lookups - number of index lookups
  • user_updates - number of insert, update or delete operations

Does that mean if there is an index with very few / no seeks+scans+lookups but many updates, then the index is only costing us (have to update index when table is updated) but not gaining us anything?

Second question - Is the following query a decent way to discover this information?

SELECT DB_NAME([database_id]),
       OBJECT_NAME(dm_db_index_usage_stats.[object_id], [database_id]),
       TotalReads = (user_seeks + user_scans + user_lookups),
       Writes = user_updates,
       indexes.[name],
       *
FROM sys.dm_db_index_usage_stats
LEFT JOIN sys.indexes on dm_db_index_usage_stats.[object_id] = indexes.[object_id] and dm_db_index_usage_stats.[index_id] = indexes.[index_id]
ORDER BY (user_seeks + user_scans + user_lookups) ASC, user_updates DESC;
  • 1
    You need to be a bit careful around the timing of your query - the DMV will be reset whenever SQL is restarted so it doesn't necessarily reflect overall usage. If SQL has been restarted recently you could be seeing a partial picture. – George.Palacios May 25 '17 at 15:16
  • 3
    Version will matter here, too. In some versions of SQL Server, rebuilding indexes will reset these metrics. See here for more information: littlekendra.com/2016/05/18/… – sp_BlitzErik May 25 '17 at 15:18

Just throwing this out there, because I remember the bad old days of having a different script for every index ailment you read about on the internet.

I co-author a free stored procedure called sp_BlitzIndex that will tell you about a whole bunch of stuff going on with your indexes all at once.

Some examples:

  • Aggressively locked indexes
  • Duplicates (based on keys)
  • Borderline duplicates (based on first key column)
  • Unused (with differentiation based on number of writes
  • High value missing indexes
  • HEAPs
  • And more!

The simplest example run is probably like this. Just change the database name.

EXEC sp_BlitzIndex @DatabaseName = 'StackOverflow', @Mode = 4

There are a ton more ways to run it, just check out the docs at the GitHub link.

Hope this helps!

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.