Managing unused indexes

• JasonO

  SSC-Addicted

  Points: 494

  More actions

  October 28, 2025 at 2:33 am

  #4678350

  While doing some housekeeping activity on several old but large production databases, I come into a large numbers of unused indexes when querying from the sys.dm_db_index_usage_stats DMV,  these indexes have zero seeks, scans & lookups, i do see these indexes are always updated so this could conclude that SQL constantly had to spend resources updating indexes that are not used. Total disk spaces occupied by the indexes are rather massive about 800GB in total.

  I know that DMV will reset whenever SQL server resets, so I've created a stored proc & scheduled it as a job to run once a week to capture the index usage into another table. I figured by capturing weekly data I should have sufficient and reliable data to work with even if SQL is restarted.

  I will let the job ran & review the result every 6 months, if that particular indexes was consistently flagged as unused every week for the past 26 weeks, then I could assume the index can be safely dropped. After that the next steps is to probably disabling that index & monitor for a while before dropping it.

  Any thing else I could improve or do differently? Appreciate some inputs on this, thanks!