Purpose of Filegroups

Casey 116 Reputation points
2022-03-18T15:58:42.623+00:00

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.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,497 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,934 questions
0 comments No comments
{count} vote

Accepted answer
  1. Erland Sommarskog 102.4K Reputation points
    2022-03-22T21:57:18.04+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-03-18T19:11:51.397+00:00

    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.