Allocation strategy where tables regularly grow and shrink by large amounts

Question

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?

Answer 1

Supplementing to @MaxVernon's answer, there are some optimizations that you can do :

• Use different filegroup for staging tables and make sure the filegroup is on a different physical drive (or LUN).
• Use Instant File Initialization as well as have a proper autogrowth setting. I have a handy script to analyze autogrowth events.
• Since you will be emptying the staging tables after the load, make sure to UPDATE STATISTICS after the load. Refer to the data loading performance guide for more tricks to load data fast and efficiently.

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

The advantage is that you can do a piece meal restore of your main filegroup where your production data resides and then you can restore the filegroup where your staging tables resides.

The only caution that I would take is to have a well tested RESTORE strategy as now you have multiple filegroups that you need to get used to when doing restore in case of disaster.

Answer 2

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.