Allocation strategy where tables regularly grow and shrink by large amounts

I have a SQL Server 2014 database with 100+ tables that have a 5% growth rate over the course of a year. Lots of reading, very little writing.

This database is going to expand by another set of 20+ tables that regularly (quarterly) grow overnight by almost as much as the entire rest of the database combined.

Those tables are completely emptied at the end of processing in a few hours. The data in those tables is fairly expendable, but need to be in the same database as the rest for referential integrity.

For some sample numbers, let's say the first DB is 20GB and the subset of tables grow to 15GB and then are emptied. So the actual size of the data fluctuates between 20 and 35 GB throughout the course of a few days.

• What can I do in planning this system to help account for this fluctuation?

• Is this where filegroups would come into play?

• What advantages / problems would I get moving this set of tables to their own filegroup?

Putting those staging tables on their own filegroup allows you to do piece-meal restores. This helps get the database up-and-running more quickly in the event of a disaster recovery effort.

Having those tables in their own filegroup will allow you to place them on their own set of disks which may increase performance.

StackExchange Database Administrators Q#112642, answer score: 2