Multiple filegroups vs. multiple files in a single filegroup

Question

From what I've read online, it seems that multiple filegroups offer only two advantages over having multiple files in a single filegroup:

1. The ability to isolate a specific table to a specific drive - the only way to accomplish this is to add a new filegroup with a file on the target drive, and then move the table there.

2. Restores can be sped up by doing a piecemeal restore with a read-only filegroup.

Are there any other advantages that multiple filegroups would have over multiple files in a single filegroup? I'm most interested in performance advantages, but welcome any ideas.

Answer 1

Both multiple files in a filegroup and multiple filegroups are tools for managing your database. The first lets you manage your IO and both will let you manage your backups.

It is possible to backup a single file of a database as well as a single filegroup. Be sure to backup the tail of the transaction log when you do if you are planning on restoring it somewhere.

Database files allow your multi-core CPUs to have multiple read/write streams to the database without hitting higher disk queuing values.

It may help to think of the filegroup as a logical division and the file as a physical division. If you have multiple filegroups you will automatically have multiple files as a file can only belong to one filegroup. It can also help (if you have enough cores on your server) to have multiple files in your filegroups. That can give you the best of both worlds. You assign database objects to a filegroup not a file. You can put the files on different physical disk arrays. When I first started doing database work it was common knowledge that you put your data and log on separate disks. If you had the budget you would put your non-clustered indexes on another disk. It's tough to get that these days with SAN technology everywhere. However, SAN is a management tool not a performance tool.

As you pointed out having different filegroups will allow you to isolate high traffic tables from each other and from lower traffic tables. It will also allow you a limited additional protection from a corrupted database potentially limiting data corruption to a smaller part of the data.

Answer 2

With SQL 2016 Standard, you can take advantage of table partitioning and partition switching which uses filegroups. Partition switching can make data management faster depending the circumstances and can possibly boost the performance of a query if it utilizes the partition key and is properly tuned.

...a whole partition can be switched into the table or switched out, allowing for extremely fast loading and removal of large amounts of data. This is, in my opinion, the biggest benefit of partitioning.

As to performance on partitions:

Queries will perform better when you specify the partitioning key in the criteria (aka the “where clause”). So, although partitioning is “transparent,” for existing applications, query tuning will almost always be required.

Kendra on performance and switching.

One side note is that you would want to script out your restores. Imagine trying to do a restore with 100+ filegroups manually. Not exactly a downside, but something you should be prepared for beforehand.

Brent Ozar on filegroups.