Database Filegroups

You can group data files for a database together 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 . Three types of filegroups in a SQL Server database are possible:

• Primary filegroup 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.
• User -defined filegroup You can create user-defined filegroups by using the FILEGROUP keyword in the CREATE DATABASE or ALTER DATABASE statement.
• Default filegroup This filegroup contains the pages for all tables and indexes that aren't placed in a specific filegroup. Only one filegroup in each database can be the default filegroup. A database owner can change which filegroup is the default by using the ALTER DATABASE statement. If no default filegroup was speci-fied, the primary filegroup is also the default filegroup.

Most SQL Server databases will 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 steps up the level of 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 (redundant array of independent disks) device. Still, there would be no need to use filegroups. Move another level up the sophistication and complexity scale, where she decides that she really wants multiple files. Perhaps she wants to add disk space on another drive, or perhaps she just wants to create a database that uses more space than is available on a single drive. She still doesn't need filegroups and can accomplish her goals using CREATE DATABASE with a list of files on separate drives.

Why Use Multiple Files?

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

First, if you need to restore a database from a backup because of a disk crash, you should be aware that 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 into a database with one file of that size . If you don't have another 12-GB drive immediately available, you won't be able to 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 onto 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 7 database created on 12 files. Microsoft thought this would give it the ultimate flexibility. They could separate the files into two groups of six, six groups of two, four groups of three, and so on. This allows them to experiment with performance enhancements gained as files are spread over different numbers of physical drives.

More sophisticated 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, still without having 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.

You can also use filegroups to allow a DBA to back up only parts of the database at one time. However, if you create an index in a filegroup that's different from the one 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 filegroup in which the table resides, 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 adding 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. We'll discuss extents in more detail later in this chapter.

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:\mssql7\data\SPri1dat.mdf',  SIZE = 10,  MAXSIZE = 50,  FILEGROWTH = 15% ),  ( NAME = SPri2_dat,  FILENAME = 'c:\mssql7\data\SPri2dat.mdf',  SIZE = 10,  MAXSIZE = 50,  FILEGROWTH = 15% ),  FILEGROUP SalesGroup1  ( NAME = SGrp1Fi1_dat,  FILENAME = 'c:\mssql7\data\SG1Fi1dt.mdf',  SIZE = 10,  MAXSIZE = 50,  FILEGROWTH = 5 ),  ( NAME = SGrp1Fi2_dat,  FILENAME = 'c:\mssql7\data\SG1Fi2dt.mdf',  SIZE = 10,  MAXSIZE = 50,  FILEGROWTH = 5 ),  FILEGROUP SalesGroup2  ( NAME = SGrp2Fi1_dat,  FILENAME = 'c:\mssql7\data\SG2Fi1dt.mdf',  SIZE = 10,  MAXSIZE = 50,  FILEGROWTH = 5 ),  ( NAME = SGrp2Fi2_dat,  FILENAME = 'c:\mssql7\data\SG2Fi2dt.mdf',  SIZE = 10,  MAXSIZE = 50,  FILEGROWTH = 5 )  LOG ON  ( NAME = 'Sales_log',  FILENAME = 'c:\mssql7\data\saleslog.ldf',  SIZE = 5MB,  MAXSIZE = 25MB,  FILEGROWTH = 5MB )