Ok Jeff, I'll interrupt my Friday evening... 🙂
There's two main reasons to split up a database into multiple filegroups.
2) Restore times/recoverability.
Splitting for performance will only get you useful results if:
- There is an IO bottleneck
- The drives that the files are being split onto are on separate devices, ideally separate IO paths (depending where the bottleneck is)
- Tables and indexes are split in such a way that the objects on different filegroups are frequently read from disk (or written to disk) at the same time.
Splitting for recoverability requires identifying objects that are critical to the app and putting them (and their indexes) into one filegroup and putting objects that are not as important into one or more other filegroups. This way, in a disaster, you can restore just the critical components, get the app up and running, and restore the rest later.
I really should actually write the article on this that I've been considering for years.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass