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.