I have inherited a large production database in which one of the tables contains 72 million lines and is inserted / updated thousands of times a day. These series have built up over 3 years and are currently growing every 4 weeks by about 2 million.
There are 7 indices (3 heavily used and least fragmented / 4 mostly unused, but highly fragmented at around 75-85% and the columns are heavily updated). There was one SP that was supposed to archive record stubs in another table, but we had to disable this because it bothered the backups and caused failover and failures on another system.
Ideally we only want data worth 1 year in this table (about 25-30 m lines) and I want to disable the 4 unused indexes. I read that they should be restored before I disable them, but I am not sure if I should do this before or after archiving the data for the last 2 years.
A redesign has been planned for a few years, but I am now a team of 1 with limited knowledge of indexing / SQL tuning, etc. and trying to do my best with very limited resources.
The table is heavily used between 7am and 1am, so most of the work needs to be done on-site or early in the morning to limit downtime for users.
Suggestions would be welcome. Thank you very much.