From what I've read online, it seems that multiple filegroups offer only two advantages over having multiple files in a single filegroup:
-
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.
-
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.
Best Answer
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.