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.
-- 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:
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.