SQL Server - More about Filegroups

Unused perks of SQL Server Filegroups

Overview

The expected and fairly well understood use-case is…

• load balancing the disk activity.

Filegroup usage Scenarios

Here are some scenarios where you might have filegroups.

• You can put some busier tables into a filegroup that has faster disks.
  Then when you are have heavy IO on the main filegroup, these tables will be unaffected.

• You can put the indexes into a separate filegroup
  This has the same impact as the previous as an index is essentially a special type of table.

• You have a “cell” with large data. That column can be stored in another filegroup.
  Column types [n]varchar(max), [n]varbinary(max), image, [n]text, xml are use LOB allocations.
  This filegroup will only get hit when it pulls the data, presuming the column is not in the where-clause or join-clause.

• You partition the table and have the partitions in new filegroups.

3 benefits other than load balancing IO

• Backup and Restore by filegroup!
  This can be magic when you have a large database that can not be restored anywhere 'test' or 'dev' like because of disk space.
  Instead you restore the DB without the "Audit" filegroup, or the "history", "Reports" filegroups.

Note: If you have SQL that touches tables that exist in these FG's, you will need to rename then and create proxies. (You can not drop them! Thanks Microsoft)

• Read-Only filegroups.
  This can be useful to "lock down" static data or previous day's data (or similar). Partition tables are key if that is your plan.

If this is used on Azure - you can then "stretch" this filegroup to the cloud - freeing up local storage.

• Archiving / Table Maintenance / Cleanup.
  DBA's get the responsibility to 'delete old rows' from massive-massive tables.
  This isn't as simple as you might think.
  Sometimes its easier to copy the data you want to keep into a new table, drop the old, rename the new to the original - I’ve done this myself sadly.
  However, if you instead add partitions, these rows can be moved to a new table in single operation (or just dropped).

Magic - Partition Sliding

It is possible to add a partition to an existing table such that it can Never have any rows. Then you run SQL to update the partition function such that rows get copies from the original to new partition.

The magic happens when you can specify in the SQL how many rows you would like to shift. This trickery allows us to move large tables between filegroups (and therefore volumes) piecemeal, and trickling in the background.

I have even registered an event to fire when CPU is low for a sustained period of time. This allows the SQL to run during quite times, and limiting the rows means it runs for short period each time.