File groups

File Group Type:
Every database has the PRIMARY file group which always contains the primary data file. In addition, the PRIMARY file group can also contain secondary files which are not specifically assigned to another file group. Normally it’s recommended to leave only the primary file in the PRIMARY file group because this file contains internal meta-data about the database and its objects and it should be stored separately from application data.

Each database can also have user-defined file groups. It is convenient and recommended to group multiple files into file groups based on usage of objects stored in those files. For example, if you know that a particular set of tables will be used for read-only purposes, you can store them in a read-only file group. You might also have some summary tables that are updated only once a month or once a quarter which could be assigned to another file group. Then you could store the remaining tables (which perhaps are heavily queried and continuously updated) in the other file group.

ALTER DATABASE [eastern_sales]  MODIFY FILEGROUP [secondary_default] DEFAULT

File Group Setting:
Each database file can only belong to a single file group. Once you assign a file to a file group, you cannot move it from the current file group to another. Logically you can create tables on a file group that doesn’t have any files associated with it; however, you will not be able to populate such objects until you create files that belong to this file group. Transaction log files are not associated with any file group – they’re stored separately from data files.

Performance consideration:
In order to achieve performance improvement by adding data files, you need to place such files on different physical disks or disk arrays. You can gain performance benefits by spreading disk I/O operations across multiple spindles because SQL Server can perform many read/write operations in parallel. This reduces total query time. If you place multiple file groups on a single disk or disk array, you might only see a marginal performance benefit at best.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s