SQL Server maps a database over a set of operating-system files. Data and log information are never mixed in the same file, and individual files are used only by one database. One DB can have multiple filegroups and\or multiple files in a filegroup. The use of Filegroup is limited to Data Files only. Database objects and files can be grouped together in filegroups for allocation and administration purposes. Now before making changes to files or filegroups you should have a clear understanding of each of them.
Let’s discuss them one by one:
Why to use Multiple Files in a Single Filegroup
Multiple files are created in a single filegroup to achieve better performance provided you place each file on a different disk, since SQL Server uses proportional fill method the data will get split and then written to Files.
Note: In most cases, using the striping capabilities of RAID provides much of the same performance gain.
Why to use Multiple Filegroups in a DB
Multiple Filegroups are created to have better Data Placement Control.
There are few other benefits also like:
- Effective Backup Restore strategy.
- Provides a good way to physically separate data (System tables, User Tables & Indexes) with different access needs.
- Provide manageability and some performance benefits.
Hope this will help you.