There are several different aspects of indexes that can be monitored via DMV’s. One the neatest things is just how much your indexes are used, how they are used or if they are used at all! I’ve put together this stored procedure to gather a ton of useful information on just how your indexes are being used. Using this information you can determine if the index in question is really needed, or if it is truly critical and shouldn’t be messed with. As will all DMV’s if your server was just rebooted you may see quite a number of unused indexes. By sampling data over time we can see when an index is actually called into use. Is it only really used once a quarter or once a year? Maybe we can disable it and then rebuild it before it is needed cutting down on maintenance and lessening the penalty for data modifications over the long term.

IsUsed – Simple, has your index ever been used.

IsExpensive – does it cost a lot to do updates or inserts into this index?

These columns all deal with how your index is accessed and updated. Is it used in a lot of scans, Maybe implying its used mostly for joins? Is it seek heavy, showing that your used are writing good WHERE clauses?

UserSeeks

UserScans

UserLookups

UserUpdates

LastUserSeek

LastUserScan

LastUserLookup

LastUserUpdate

AverageRecordSizeInBytes I look at this one to show me just how wide or narrow a particular index is. I also use it to help do detailed growth analysis, if we add X number of rows what size would the index grow too?

Series to Date SQLDIY: Manage and Monitor SQL Server Yourself

Link to the script Gather Index Usage Statistics

I’m hosting all my scripts from this series on GitHub as I do with all my open source projects.

As always, if you find any bugs please let me know and I will correct them!