October 28, 2025 at 2:33 am
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!
October 28, 2025 at 9:17 am
Keep in mind a unique constraint is materialized as a unique index.
You'll need to contact your devs and get their accord.
Don't just disable/drop without consent
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply