Files and Filegroups

In Microsoft SQL Server version 6.5, devices and segments are used to physically allocate space for storing data, indexes, and logs. This can be cumbersome for applications with large amounts of data. In Microsoft SQL Server version 7.0, files and filegroups are used to allocate space for data, indexes, and logs, giving you many more choices for setting up data.

A file is a physical allocation of space and can be one of three types: primary, secondary, or log.

A primary file is the starting point of the database and contains the pointers to the rest of the files in the database. Every database has one primary data file. The recommended file extension for primary data files is .mdf (E:\Mssql7\Data\Master.mdf). Some databases may not have secondary data files, and others may have multiple secondary data files. The recommended file extension for secondary data files is .ndf (E:\Mssql7\Data\Mydata1.ndf). Log files hold all of the log information used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file extension for log files is .ldf (E:\Mssql7\Data\Mylog.ldf).

Database objects can be grouped in filegroups for allocation, performance, and administration purposes. There are two types of filegroups: user-defined filegroups and the primary filegroup. The primary filegroup or any of the user filegroups can be the default filegroup. The primary file is assigned to the primary filegroup. (The preceding illustration shows a primary filegroup assigned as the default.) Secondary files can be assigned to user filegroups or the primary filegroup. Log files are never a part of a filegroup. Log space is managed separately from data space.

Care must be taken with the size of the primary filegroup, especially if it is also the default filegroup. Because the primary filegroup contains all the system tables, if it runs out of space, no new catalog information can be added to the system tables. In contrast, if a user-defined filegroup fills up, only the user tables specifically allocated to that filegroup are affected. The primary filegroup fills if the autogrow feature is off or if the disk holding the primary filegroup runs out of space. If either of these should occur, set autogrow on or move other files off the disk to free more space.

When creating objects such as tables and indexes, you can specify the filegroup to which the tables and indexes are assigned. A table can be assigned to one filegroup, and that table's index(es) can be assigned to a different filegroup. If you do not specify a filegroup, the table or index is assigned to the default filegroup.

Recommendations:

• Carefully plan the size of the primary filegroup or set it to autogrow to avoid running out of space.
• With large systems that use filegroups for administration and performance reasons, create a user-defined filegroup and make it the default filegroup. Create all of the secondary database files in user-defined filegroups so that user objects do not compete with system objects for space in the primary filegroup. This also facilitates the customization of maintenance for each filegroup.

Several questions that you may have about files and filegroups are:

• How many files should I create per filegroup?
• How many filegroups should I have for my database(s)?
• How should I place my data objects (tables/indexes) into filegroups?
• How should I set up my hardware to get the best performance from files and filegroups?
• Do I even need to worry about filegroups if I have a smaller database?

The answers to these questions depend on three factors:

• Maintenance requirements
• Performance requirements
• Hardware I/O layout

Assigning Files to Filegroups
for Maintenance Reasons

Maintenance operations such as backup and restore can be performed at the file or filegroup level. It may be appropriate to perform some operations such as backup, restore, update statistics, or DBCC against some objects (tables) more often than others. By placing tables with similar maintenance requirements in the same filegroup, the maintenance operations can be executed against this specific filegroup.

Recommendations:

• Group tables and indexes with similar maintenance requirements into the same filegroups.

  An example of this is a data warehouse application with 50 tables. Assume three of the tables are modified or refreshed daily and the other 47 tables are modified or refreshed weekly.

By creating two filegroups and assigning tables to them, you can run daily maintenance tasks (backups, DBCC, update statistics, and so on) against the tables in the daily refreshes group and weekly maintenance tasks against the tables in the weekly refreshes group.

• Assign an individual high maintenance table to its own filegroup.

  For example, a specific table is updated frequently and may need to be recovered as an individual table. Because SQL Server can restore both files and filegroups, you can place a table in its own filegroup so you can restore only that table.

• When you work with small databases, using filegroups is not necessary. The primary filegroup can be used for easier administration of small databases.

The primary filegroup (the default, unless explicitly changed) contains the pages for all tables and indexes that are not assigned to a filegroup when they are created. In each database, only one filegroup at a time can be the default filegroup: a useful option for sites that have limited or no database administrator support. (Members of the db_owner fixed database role can switch the default filegroup from one filegroup to another.)