As a DBA who is always seeking solutions to performance bottlenecks, amidst the

daily rituals of validating backups and other regular tasks, profiting from

File Groups on differing disk subsystems, in this case for Indexes, arose as

flavor of the week. With this option, note that I am assuming one has setup the

SQL Server instance with several disks available. If you are not on Microsoft SQL

Server Enterprise Edition the usual Index option, ONLINE=ON, will unfortunately not be available, however if you

perform this task during a maintenance window with Standard edition, this

problem is moot.  The distinction must be

made that this is not a regular maintenance task, and this is not to rebuild an index, but to recreate an index. We are replacing the

index location on the disk subsystem with the useful option DROP_EXISTING=ON while referring to a different

Filegroup location (ON [Indexes]) at the end of the script.

Here is the example:

/****** Object:  First Add an Index File

Group Script Date: 06/06/2012 4:55:16 PM ******/

Create FILEGROUP [Indexes]

FILENAME = N'DiskName:\DataFileFolder\DatabaseName_Indexes.ndf' ,

= 10GB , MAXSIZE = 50GB , FILEGROWTH = 1GB )

/****** Object:  Index [SampleTableName _IDX_000]   

Script Date: 06/06/2012 4:55:16 PM ******/

NONCLUSTERED INDEX [SampleTableName_IDX_000] ON [dbo].[ SampleTableName]

ASC,

ASC,

STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Indexes]

Reading up on Index options, I noticed, way down at the very bottom of the Alter Index page, another option to save disk space and improve performance (SQL 2008+),

was that you can compress Indexes also:

ALTER INDEX IX_INDEX1

T1

( DATA_COMPRESSION = PAGE )