I listened to a .NET Rocks podcast recently on database design, and although I found most of the content near worthless … they did mention in passing some dynamic management views that are built into SQL Server that I was unaware of. They turned out to be immensely helpful, so I thought I would share them.
Some of the dynamic management views are designed to provide usage info about the indexes you currently have in place in a particular database. Often times we create indexes that seem like they would be helpful for some functionality, but if the query that is actually ran against the database varies slightly from what we had in mind … SQL might not even be able to use the index. There are also times were the data a particular index is built on is updated much more frequently than it is used, and is therefore causing a huge amount of maintenance overhead for an index that is rarely used … and is therefore robbing performance.
Here is a simple T-SQL script I created that helps me find these types of indexes that should be candidates to be removed. It finds all of the “non-system” indexes that are just simple indexes (not primary keys or unique constraints), which have to be updated at least twice as often they are used.
SELECT OBJECT_NAME(S.object_id, S.database_id) AS 'Table',
I.Name AS 'IndexName',
S.user_seeks,
S.user_scans,
S.user_lookups,
S.user_updates,
S.last_user_seek,
S.last_user_scan,
S.last_user_lookup,
S.last_user_update
FROM sys.dm_db_index_usage_stats S
INNER JOIN sys.indexes I ON I.object_id = S.object_id AND I.index_id = S.index_id
WHERE S.database_id = DB_ID()
AND NOT OBJECT_NAME(S.object_id, S.database_id) LIKE 'sys%'
AND NOT OBJECT_NAME(S.object_id, S.database_id) LIKE 'fulltext%'
AND NOT I.Name IS NULL
AND NOT I.is_primary_key = 1
AND NOT I.is_unique_constraint = 1
AND S.user_updates > ((S.user_seeks + S.user_scans) * .5)
ORDER BY S.user_updates DESC
You can see in the WHERE clause the “* .5” text. You can change that to be lower to be more stringent on how bad an index has to be before you consider removing it.
For more info on index usage stats, see these MSDN articles: