Home Forums SQL Server 2008 SQL Server 2008 Administration Filegroup and number of files per FG questions - Redesign of 2 TB DB with 2,000+ batches per second RE: Filegroup and number of files per FG questions - Redesign of 2 TB DB with 2,000+ batches per second

  • Jeff Kelly-310227 (12/5/2012)


    Are there a recommended number of files per Filegroup –some mathematical formula to # of cores/etc…?

    Nope.

    There's two main reasons for going multiple files/multiple filegroups

    1) IO throughput. This requires that different files/filegroups are on independent drives. You've confirmed that in your situation this is not the case

    2) Backup/restore. You can take file or filegroup backups hence spreading out the impact of backups. You can set some filegroups read only and avoid having to back that data up. Taking full database backups can be faster if there are multiple filegroups on independent drives as that allows parallel reads and more backup buffers (again full advantage requires different IO channels)

    There's also the option of tiering your storage so important tables go on fast drives and less important on slow drives

    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