• 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass