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.

Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: