Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Man of Mystery

Wes Brown is a PASS chapter leader and SQL Server MVP. He writes for SQL Server Central and maintains his blog at http://www.sqlserverio.com. Wes is Currently serving as a Senior Lead Consultant at Catapult Systems. Previous experiences include Product Manager for SQL Litespeed by Quest software and consultant to fortune 500 companies. He specializes in high availability, disaster recovery and very large database performance tuning. He is a frequent speaker at local user groups and SQLSaturdays.

SQLDIY: Index Usage Statistics

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!


Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.