In 2012, do you get any performance improvement by using multiple
file groups for your database, if all file groups reside on the same physical
drive? Thanks, Jack
You could. It depends on the capabilities of your hardware. Presumably you've divided the physical drive into multiple logical drive letters, and each logical drive gets its own I/O queue.
2 Likes
Its not a situation that I have had to grapple with, but presumably if you get to the point of wanting to partition the database into filegroups, for performance, it would be better to put them on separate drives?
If that is the case, and performance is the issue here, then I would expect the cost of the extra drives to be less than whatever "cost" is associated with the performance issue.
So my conclusion is that separating a database into filegroups, for performance, should also mean "separate drives"
But, as I said "never had to do this", I'd be interested in any other opinions.
Thank you Kristen and Scott. I DO have multiple physical drives so I will use those for the partitioned tables.
As you do have multiple physical drive (I assume it will be different logical drive, compares to forming a RAID for a single logical drive). Then yes, you should get better IO performance.
With partitioned tables, you will need to be more careful on how you locate them to get the performance improvement you expected.
hope this helps.