GilaMonster (10/13/2015)
don.schaeffer (5/12/2011)
I'm wondering just what the benefit is to filegroups when your data is stored on a SAN or NetApp appliance? I believe I read something about a benefit when restoring a corrupt database, but otherwise are there benefits?I keep meaning to write a nice complex article on splitting across filegroups, looking at splitting for performance and splitting for recoverability. I keep not getting around to it. Maybe someday.
Short answer to your question is 'maybe' 🙂
If all the filegroups are on LUNs from the same disk pool, using the same storage path, then probably you won't see a performance benefit. Recoverability-wise, it's about whether you can restore sub-sets of the database in case of either corruption or a complete DB loss. That kind of filegroup split tends to need to be carefully designed to get the benefit.
Wow, it's like SQL Server Central is reading my mind today. I had the exact same question because we just migrated to NetApp storage and one of the first things I learned about SQL Server is that I should ALWAYS split my primary data, staging data, index data, and log data into multiple filegroups across multiple disks. With specific explicitly on keeping my logs on fast drives.
So, the question on whether or not I receive benefit from doing so with NetApp greatly depends on the disk pool. Therefore, I've kind of narrowed my request to the system admins to a simple, "Can you give me dedicate I/O that is not shared per filegroup?"
I'm wondering if that would be the right question to really simplify down the ask for those not as familiar with filegroups with SQL Server and what's trying to be achieved with I/O optimization?