I was reading about creating multiple File Groups on SQL Server. I read in a few places that one of the main reasons to create additional file groups is for performance reasons:
The reason this improves performance according to what I've read is that if you have different tables belonging to different filegroups, they can be on different drives, thus improving read/write throughput.
However, with a single filegroup, there can be multiple Database Files, which could be on different drives. So wouldn't that have the same performance benefit? (i.e. One FileGroup spread over multiples drives vs. Multiple FileGroups spread over multiple drives)
you are right but there is more to it than just that, like many things in life the best answer is : it depends. One cannot dogmatically say this is the only way.
Scenario #1
For example let us say you have a physical server that has one physical disk partitioned into C, D and L.
You have OS on C, data files on D and log files on L. Would this situation improve performance. Yes it could but it is on just one drive. So that one drive could get slammed hard and affect performance because it is one drive.
Scenario #2
But now on same physical server you have 3 distinct disks on the server itself: C, D and L. Would this improve performance? Again it depends. C drive might be the latest SSD but D and L could be old school drives. But this setup could be better than scenario #1? Or could you have C as regular drive for OS, D and L as SSD drives and another SSD drive dedicated to indices??
Scenario #3
But now for same physical server you have 2 distinct virtual disks: D and L are on a nimble storage or NAS or whatever. Would this improve performance? Again it depends. How is your network setup, what are other things are hitting these nimble storage devices.
And then there are things such as table schema design, indices, stored procedures etc that can affect performance.
Depending on your environment/topology and discussing things with your storage team, network team you can come up with a nice architecture that makes things purring nice and blazing fast.
1 Like