articles >> performance tuning >> Optimizing SQL Server Performance Using Files and ...

Optimizing SQL Server Performance Using Files and Filegroups

By : Alexander Chigrik Jan 31, 2003

There are no devices or segments in SQL Server 7.0 and SQL Server 2000, as there were in SQL Server 6.5. Now databases reside on operating-system files. There are three types of such files:

• primary
• secondary
• log

Each database consists of at least two files: one is a primary data file (by default, with the .mdf extension), the other is a log file (by default, with the .ldf extension). There are also optional secondary data files (by default, with the .ndf extension).

A database can have only one primary data file, zero or more secondary data files, and one or more log files. Each database file can be used by only one database. So there is no such situation (as in SQL Server 6.5 was), when you can store both databases and their logs on the same device (on the same file with the .dat extension).

The data files (.mdf and .ndf) are combined into filegroups. A filegroup is just a collection of one or more database files. Each database file can be a member of only one filegroup. Log files, on the other hand, are not members of filegroups, but are managed separately.

There are three types of filegroups:

• primary
• user-defined
• default

Each database has only one primary filegroup, only one default filegroup, and zero or more user-defined filegroups. If you don't specify user-defined filegroups, your database will contain only a primary filegroup, which will be also the default filegroup. The primary filegroup contains the primary data file with all system objects in it (system tables, system stored procedures, extended stored procedures and so on). You cannot remove system objects from the primary filegroup, but you can create user objects in the user-defined filegroups for allocation, performance, and administration purposes.

To create a user-defined filegroup, you should use the CREATE DATABASE or the ALTER DATABASE statement with the FILEGROUP keyword. The default filegroup is the filegroup where all the new user objects will be created. You can change the default filegroup (from the Primary filegroup) by using ALTER DATABASE statement with the DEFAULT keyword.

SQL Server database files can be configured to grow and shrink automatically to reduce the need for active database management and eliminate many of the problems that can occur when logs or databases run out of space. The Autogrow feature is set on by default for all versions of SQL Server 7.0 and 2000, but the Autoshrink feature is set on by default only for the Desktop Edition.

When you create a database, you must set an initial size for both the data and log files. If you want to set database files to grow automatically, you should also specify the Autogrow increment in megabytes, kilobytes, gigabytes, terabytes, or percent, the default is MB. You can also specify a maximum file size to prevent disk drives from running out of space.