Using Database Filegroups

You can group data files for a database into filegroups for allocation and administration purposes. In some cases, you can improve performance by controlling the placement of data and indexes into specific filegroups on specific disk drives. The filegroup containing the primary data file is called the primary filegroup. There is only one primary filegroup, and if you don't specifically ask to place files in other filegroups when you create your database, all your data files will be in the primary filegroup.

In addition to the primary filegroup, a database can have one or more user-defined filegroups. You can create user-defined filegroups by using the FILEGROUP keyword in the CREATE DATABASE or ALTER DATABASE statement.

Don't confuse the primary filegroup and the primary file:

• The primary file is always the first file listed when you create a database, and it typically has the file extension MDF. The one special feature of the primary file is that its header contains information about all the other files in the database.
• The primary filegroup is always the filegroup that contains the primary file. This filegroup contains the primary data file and any files not put into another specific filegroup. All pages from system tables are always allocated from files in the primary filegroup.

The Default Filegroup

One filegroup always has the property of DEFAULT. Note that DEFAULT is a property of a filegroup and not a name. Only one filegroup in each database can be the default filegroup. By default, the primary filegroup is the also the default filegroup. A database owner can change which filegroup is the default by using the ALTER DATABASE statement. The default filegroup contains the pages for all tables and indexes that aren't placed in a specific filegroup.

Most SQL Server databases have a single data file in one (default) filegroup. In fact, most users will probably never know enough about how SQL Server works to know what a filegroup is. As a user acquires greater database sophistication, she might decide to use multiple devices to spread out the I/O for a database. The easiest way to accomplish this is to create a database file on a RAID device. Still, there would be no need to use filegroups. At the next level up the sophistication and complexity scale, the user might decide that she really wants multiple files—perhaps to create a database that uses more space than is available on a single drive. In this case, she still doesn't need filegroups—she can accomplish her goals using CREATE DATABASE with a list of files on separate drives.

More sophisticated database administrators (DBAs) might decide that they want to have different tables assigned to different drives. Only then will they need to use filegroups. The easiest way to accomplish this goal is to use SQL Server Enterprise Manager to create the database. SQL Server Enterprise Manager will create the necessary filegroups, and the user still doesn't have to learn anything about filegroup syntax. Only the most sophisticated users who want to write scripts that set up databases with multiple filegroups will need to know the underlying details.

Why Use Multiple Files?

You might wonder what the reason would be for creating a database on multiple files located on one physical drive. There's no performance benefit in doing so, but it gives you added flexibility in two important ways.

First, if you need to restore a database from a backup because of a disk crash, the new database must contain the same number of files as the original. For example, if your original database consisted of one large 12-GB file, you would need to restore it to a database with one file of that size. If you don't have another 12-GB drive immediately available, you cannot restore the database! If, however, you originally created the database on several smaller files, you have added flexibility during a restoration. You might be more likely to have several 4-GB drives available than one large 12-GB drive.

Second, spreading the database onto multiple files, even on the same drive, gives you the flexibility of easily moving the database onto separate drives if you modify your hardware configuration in the future. Microsoft's internal SAP system uses a SQL Server database created on 12 files. Microsoft has found that this provides the ultimate flexibility. They could separate the files into two groups of six, six groups of two, four groups of three, and so on, which would allow them to experiment with performance enhancements gained as files are spread over different numbers of physical drives.

You can also use filegroups to allow backups of only parts of the database at one time. However, if you create an index in a filegroup that's different from the filegroup the table resides in, you must back up both filegroups (the filegroup containing the table and the filegroup containing the index). If you create more than one index in a filegroup that's different from the filegroups in which the corresponding tables reside, you must immediately back up all filegroups to accommodate these differing filegroups. The BACKUP statement detects all these filegroup situations and communicates to the user the minimum filegroups that must be backed up.

When you add space to objects stored in a particular filegroup, the data is stored in a proportional fill manner, which means that if you have one file in a filegroup with twice as much free space as another, the first file will have two extents (or units of space) allocated from it for each extent allocated from the second file. I'll discuss extents in more detail later in this chapter.

A FILEGROUP CREATION Example

This example creates a database named sales with three filegroups:

• The primary filegroup with the files Spri1_dat and Spri2_dat. The FILEGROWTH increment for these files is specified as 15 percent.
• A filegroup named SalesGroup1 with the files SGrp1Fi1 and SGrp1Fi2.
• A filegroup named SalesGroup2 with the files SGrp2Fi1 and SGrp2Fi2.

 CREATE DATABASE Sales ON PRIMARY ( NAME = SPri1_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri1dat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), ( NAME = SPri2_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri2dat.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), FILEGROUP SalesGroup1 ( NAME = SGrp1Fi1_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi1dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), ( NAME = SGrp1Fi2_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi2dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), FILEGROUP SalesGroup2 ( NAME = SGrp2Fi1_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi1dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), ( NAME = SGrp2Fi2_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi2dt.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = 'Sales_log', FILENAME = 'c:\program files\microsoft sql server\mssql\data\saleslog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )