1

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.

Improve this question
3
  • 2
    You have to rebuild the indexes after you enable them, so rebuilding them before you disable them would be pointless. As for making the removal of old data better, I've found wonderful success with kejser.org/table-pattern-rotating-log-ring-buffer and have used it in production. Getting there could be painful (rebuild the entire table) but once there, dropping old data will be extremely fast.
    – Jonathan Fite
    Commented Nov 14, 2019 at 16:32
  • The archiving/deleting process would probably work better if it did a limited number of rows (small batches) and ran on a schedule that doesn't block the app. Maybe even fire increased log backups as necessary to control the .ldf size
    – Kevin3NF
    Commented Nov 14, 2019 at 16:44
  • @JonathanFite Can you briefly explain this. The link is broken.
    – uriDium
    Commented May 6 at 17:59

1 Answer 1

Reset to default
3

Here some thoughts on your situation:

1) if 4 indexes are not used at all, and you are not planning using them in future, consider dropping them completely, instead of just disabling it

2) to disable an index, no need to rebuild it before disabling it. To enable it back, you have to "rebuild" https://blog.sqlauthority.com/2007/05/17/sql-server-disable-index-enable-index-alter-index/

3) if you are willing to archive/delete previous 2 years of data, better drop unused indexes before archiving data -> this way archiving data will be less resource-intensive as those indexes will not need to be maintained during archive/delete

4) as already mentioned in comments, archive/delete data in small batches (1000,500,100 etc.) - 

"delete top (n) from [YourTable] where [Date] < 'some date' "

to prevent escalating row/page level locks to table-level locks
There must be 1 to few seconds break between each batch deletion
You can write a t-sql code that does all this for you

5) You can also setup Extended Events session and monitor for "lock_escalation" events, by doing this you can find out what batch size is not causing escalation of locks to table-level when you run your archive/delete process, and use this batch size going forward - this will make archiving/deleting painless - there will be no need for outage/downtime for your applications/users

Improve this answer
2
  • 1
    "delete top (n) from [YourTable] Where date < [somedate]" :)
    – Kevin3NF
    Commented Nov 14, 2019 at 18:12
  • 1
    yep Kevin is right :)
    – Aleksey Vitsko
    Commented Nov 14, 2019 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.