I've inherited a large production database in which one of the tables has 72 million rows and is inserted/updated thousands of times during a day. These rows have built up over 3 years and they are currently growing at approx 2 million every 4 weeks.
There are 7 indexes (3 heavily used and least fragmented/ 4 mostly unused but heavily fragmented at about 75-85% and the columns are heavily updated). There was an SP that was scheduled to archive record stubs to another table but we have had to turn this off as it was interfering with the backups and causing a failover and outages on another system.
Ideally we only want 1 years worth of data in this table (approx 25-30m rows) and i'd like to disable the 4 unused indexes. I've read that before i disable them they should be rebuilt but i'm not sure whether to do this before or after i've archived the previous 2 years worth of data.
A redesign has been on the cards for a few years but i'm now a team of 1 with limited knowledge of indexes/SQL tuning etc and trying to do my best with very limited resources.
The table is heavily used between 7am and 1am so most work needs to be a site outage or done in the early hours of the morning to limit downtime to users.
Any suggestions would be welcome. Thanks.