Finding unused indexes is a proactive measure that DBA’s can take. By removing indexes you can improve performance and free up disk space on a SQL Server.
What are unused indexes?
We would describe unused indexes as those indexes that are not being utilized by any queries.
How to identify unused indexes
The query we use to identify unused indexes is as below. Adapted from MS Sql Tips.
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 AND S.database_id = DB_ID() Order by user_seeks asc
Answer these questions before you remove them.
- Is the index likely to be used in the future?
- Does the product vendor allow the removing of indexes?
- How much overhead is the index taking up?
Risks of removing
The main risk associated with removing an index is if it is ever used. For example there could be reports that are run once a month that use the indexes and will perform poorly without them.
Risks of leaving
None really if you are not having major issues currently. You may suffer a little bit performance wise, and depending on how big the index is, there may be some space issues.
Benefits you can gain by removing
Improved performance because you have removed the overhead of keeping the index updated. Also you will be able to claim back any disk space used by the indexes, which can be a considerable amount. I recently removed an unused index for a client that was taking up 27 GB of disk space.
Always try to remember the saying.. “if it’s not broken don’t fix it”. This can be a great law to live by if you are a DBA.
If you are trying to tune a DB to the greatest potential looking for, and removing unused indexes can be a good move.