Database Filegroups and Performance

Filegroups allow you to decide where on the disk a particular object will be placed. You can do this by defining a filegroup within your database, extending the database onto a different drive or set of drives, and then placing a database object on the new filegroup.

Filegroups are most often used in high-performance environments to isolate key tables or indexes on their own set of disks, which are in turn typically part of a high-performance RAID array. Assuming that you start with a database with just a PRIMARY file group (the default), this example shows how you would add an index filegroup on a new drive and move some nonclustered indexes over to it. Moving the indexes to a separate RAID array minimizes I/O contention by spreading out the I/O generated by updates to the data that affect data rows and require changes to index rows as well.


Because the leaf level of a clustered index is the data page, if you create a clustered index on a file group, the entire table moves from the existing file group to the new file group. If you want to put indexes on a separate file group, reserve this space for nonclustered indexes, only.

-- add the file group 
alter database Grocer
      add filegroup FG_INDEX

-- Create a new database file and add it to the FG_INDEX filegroup
alter database Grocer
add file(
        NAME = Grocer_Index,
       FILENAME = 'g:\Grocer_Index.ndf',
       SIZE = 2048MB,
       MAXSIZE = 8192MB,
       FILEGROWTH = 10%
) to filegroup FG_INDEX

create nonclustered index xOrderDetail_ScanDT
    on OrderDetail(ScanDT)
    on FG_INDEX

With your indexes on a separate filegroup, you get the following advantages:

  • Index scans and index page reads come from a separate disk group, so they need not compete with other database processes for disk time.

  • Inserts, updates, and deletes on the table are spread across two separate disk arrays.

  • The clustered index, including all the table data, is on a separate array from the nonclustered indexes.

  • You can target your budget dollars more precisely because faster disks will improve system performance more if they are given to the index filegroup rather than the database as a whole.

The next section on RAID will give specific recommendations on how to architect a hardware solution based on using separate filegroups for data and indexes.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features