Jeff Moden (3/17/2014)
To be clear, my response was based on the OP's claim of having a single file per filegroup.
He corrected that statement in a later post
msmithson (3/14/2014)
Sorry I wasn't very clear. There are four file groups all residing on one RAID10 volume:Primary: one file
Filegroup2: forty files
Filegroup3: forty files
Filegroup 4: one file
A small number of files per filegroup with multiple filegroups is useful for recovery, possibly backups, performance only to a point. Typically splitting to multiple filegroups for performance and splitting to multiple filegroups for recovery/restore options result in quite a different table-to-filegroup design. My personal preference is to split once it's been decided which one we're after, performance or restore/recovery options.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability