Identifying and removing unused indexes
Hi all, I'm planning to identify and remove unused indexes from my production databases in order to reduce the database sizes.
This would be my first time performing this activity & I did some google around and found some guide online on how to do it. I gone through and summarize the steps as below.
- Identify top 100 largest indexes & turn on monitoring.
- Leave it for couple of months with monitoring on.
- Check the indexes to see which of the indexes are used or not used.
- Turn off monitoring for indexes which are used.
- Check those unused indexes for constraints.
- Set the unused indexes to invisible for a few weeks/months.