Purpose of Filegroups

Question

Hi,

I'm new to the concept of using filegroups to house partitioned data in Azure Synapse. I understand what they are and how to create filegroups but I cannot find anywhere that explains the purpose and benefits of using filegroups. Please any anyone help me understand or point to a blog to explain why someone would use filegroups, supported by partition functions and partition schemes?

Thanks.

Answer 1

I think my confusion is... after defining partitions in a table, then you are required to create partition functions and partition schemes to house/ direct the partitioned data into the filegroups, but essentially partitions in a table can exist within 1 filegroup and do not necessarily need the number of filegroups to match the number of partitions. So I'm wondering what is the point of filegroups?

Since I use Synapse very little, I cannot speak to Synapse, but in the regular SQL Server, filegroups exists for more reasons than just partitioning.

For instance, many consider it to be best practice to only have the system tables in the primary filegroup. That is, once you have created a database, you create a new fliegroup which you make the default filegroup. (Personally, I am less convinced about this. I prefer to stick to two single files.)

You may also create multiple filegroups to spread the load around, although with modern hardware this can easily be done by RAIDing, or having multiple files in the same filegroup on different disks.

But you could have a situation where your fast disks does not suffice for the entire database. At the same time, the database contains a lot of cold data which is accessed rarely. So you could create different filegroups that you place on different disks, and you the archive tables on these slow disks.

Finally, some features require extra filegroups. Those are Filestream and In-memory OLTP. Then again, these "filegroup" are completely different as they are folders and the storage format is different.

Answer 2

Back in the day of spinning disks, it was for performance so you could separate tables into different physical drives.

With SSDs and high speed SANs, this is not very useful.

I do not ever recommend partitioning, unless you are doing heavy "partition swapping" where you are inserting or deleting massive amounts of data in a partition.