ricardo_chicas (11/20/2015)
Jeff Moden (11/17/2015)
ricardo_chicas (11/9/2015)
ThanksI will do something similar, add a new file group and move those tables over there
What is the table used for? For example, is it an audit or closed invoice table where once a row is written or closed, it's never updated ever again?
It is a table that is mostly used for history, that is why it was ok for me to move it to another FG at a different drive
If that's the case, then it might be worthwhile to partition it by month using a separate FileGroup and File for each month for the older months. It's a bit of a pain to pull off correctly but the benefits are...
1. Absolutely zero index maintenance on the Read_Only months.
2. Except for a "final" backup, absolutely no addition backups of the Read_Only months.
3. Creates the possibility of "Piecemeal Restores" incase just one part of the table goes corrupt.
4. Done correctly, allows for nearly instant removal of older months for final archive purposes. No DELETEs required. Log file loves it.
5. Makes auditors really, really happy.
The only real disadvantage is that (with the understanding that required month-end code to prep old, current, and new months will need to be automated), since each partition will have it's own B-TREE (no matter whether you use a "Partitioned View" or "Partitioned Table"), queries can run a tiny bit slower but usually not so slow that most would notice.
--Jeff Moden
Change is inevitable... Change for the better is not.