Filegroups in SQL Server

Filegroup :

        Filegroups are named collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.,
        By Default every db consists of Primary Filegroup .
        We can add our own file groups .
        File groups are provided unique ids start from 1.


How to view Filegroups :

Use <Database Name >

GO

Select * from sys.filegroups ;


Advantages of Filegroup :

        a.  Better Performance i.e by splitting a database across multiple filegroups permits you to take advantage of the I/O bandwidth for each physical device / path that you've placed file groups onto . But these should place on different physical disks if you keep all are in same disk there will not be any advantage.

    b. To take backup of more than one file at a time .

    c. To implement data partitioning .

    e . To store a table data into required file .

    f.  To implement online restore .

Recomended to create multiple file groups :

• When application and users are facing the issues with access performance .
• If one table table size is >= 10% of total database size then create multiple file groups for faster access .
• When Index size and table size are equal then create separate filegroup for indexes .
• If read writes are equal on the database then keep read tables on separated file group .
• When insufficient time is available to perform database maintenance
  • If this is the case, consider splitting the large tables into separate filegroups on different underlying disks and perform maintenance in parallel
• When the business or application will be changing significantly and the data is going to grow at a much higher rate
  • If this is the case, consider working with the users to understand the potential growth
• When archived data resides in the same database as the production data 

Possible filegroup configurations :

• Option 1
  • Data filegroup
  • Index filegroup
• Option 2
  • Read only tables filegroup
  • Read-write tables filegroup
  • Index filegroup
• Option 3
  • Read only tables filegroup
  • Read-write tables filegroup
  • Index filegroug
  • Key table 1 filegroup
  • Key table 2 filegroup
  • Key table 3 filegroup 

Note: Based on your application, filegroups can be created to resolve IO performance problems by spreading the database over additional disks.