5

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.

3

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.

0

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.

  • Yeah, we have a table driven backup and restore process that lists the 12 data filegroups (not counting PRIMARY) in our oltp databases. It lets us do backups and restores with a single command. – Richard L. Dawson Jan 24 '17 at 19:38
  • Partitioning is not a performance solution, it's a management solution. – Max Vernon Jan 24 '17 at 22:27
  • Edited my post to clarify what I mean by performance as you're right, it does not directly increase performance overall and is a management solution. – Shaulinator Jan 24 '17 at 22:40
  • Partition switching requires the source and destination table be on the same filegroup, so that isn't really a benefit of multiple filegroups. The idea of partition indexing improving performance, however - could filegroups contribute to performance in a way that multiple files could not? – CaptainSlock Jan 25 '17 at 16:59
  • There are three ways I think it can help, or am I misinterpreting and this can be done with files as well? technet.microsoft.com/en-us/library/ms187087(v=sql.105).aspx Ex 1: "Put heavily accessed tables and the nonclustered indexes that belong to those tables on different filegroups." Ex 2: "Use filegroups to enable placement of objects on specific physical disks." and Ex 3: "Put different tables used in the same join queries in different filegroups." – Shaulinator Jan 25 '17 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.