What is filegroup in SQL Server? When to use multiple filegroups?

In my previous post, I have explained file types of a database, Filegroup is essentially a container that can be used to group database objects and database data files for allocation and administrative purposes.

There could only be ONE default file group of secondary data files of a database, you cannot place one database object in multiple file groups. All the system objects are placed in primary file group, all the secondary data files can be moved from one file group to another file group or you can specify file group of a data file manually while creating it.

It is good idea to create a separate file group when:

1-      You have database objects (tables, views) that are being accessed heavily; SQL Server access data using threads, each thread is accountable for retrieving or updating data on specific pages at specific location on disks, if you have multiple file groups and data is spread across the disk, SQL Server can take advantage of parallel threads, this enhances the performance of your database. For example if you have a join operation that involves various tables in a database, if tables are placed under one filegroup, it will keep SQL Server doing its operation in parallel manner (for the most part), however if tables in join resides in different filegroups and placed at different disks or luns, it will enhance execution against heavily accessed tables residing in different file groups as SQL Server can take advantage of parallel threads.

2-      Your database is performing poor while ready or updating the database; Multiple threads can concurrently work to retrieve data spread in different file groups  

3-      You have a table that contains several years of data however you are using few recent years of data; this is very useful when you are going to partition the table and indexes (partitioning will be explained in another post), It makes it easy to archive data and helps in unnecessary scan of those records which are not really important at the moment.

4-      You have a large database and recovery time is not acceptable using full backup or differential backup; if you find database corruption of objects related to a particular filegroup, you can restore only that file group in order to recover data very quickly.

How to create a filegroup?

1-      Using T-SQL

USE master

GO

ALTER DATABASE SQLAge

ADD FILEGROUP SQLAgeFG1

GO

2-      Using management Studio

a)      Right click on the database that you want to create filegroup in and go to properties

b)      Click on Filegroups

c)      Click on Add

d)     Name filegroup appropriately and click ok



Fig 1 - Adding filegroups to an existing database

Fig 2 - Adding filegroups to an existing database

Fig 3 - Adding filegroups to an existing database