Optimise Your Disk Subsystem I/O with An Index File Group and Index Compression

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]

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

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

/****** Object:  Index [SampleTableName _IDX_000]    Script Date: 06/06/2012 4:55:16 PM ******/

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

(

            [ClientId] ASC,

            [InvNo] ASC,

            [TxNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,

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

GO

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

ON T1

REBUILD

WITH ( DATA_COMPRESSION = PAGE )

GO

Happy recreation of Indexes and I/O balancing across your disks.

Bartholeme Island Boardwalk, Galapagos Islands, Ecuador