03 November,2012 by Jack Vamvas
Moving a Non Clustered Index to another filegroup is straightforward. Sometimes a DBA may decide after careful analysis that excessive contention exists on the disk drives and the solution may be to separate out the Non Clustered Indexes with the data. When data is looked up on a Non Clustered Index , the index is checked first and then related data is retrieved. The theory is if the data and index are on separate disks or LUNs , IO response times may improve , which leads to improved performance.
In an optimally configured system the disk drives tend to be the slowest part of the transactions. Not always! But mainly. There are various perfmon counters to check. Read Performance Monitor Counters for SQL Server performance testing
USE jvTest GO -- Create the FG1 filegroup on the jvTest --It's a new filegroup on separate drive ALTER DATABASE jvTest ADD FILEGROUP FG1; GO -- Adds the FG1dat file >> FG1 filegroup. --replace the filename with your own ALTER DATABASE jvTest ADD FILE ( NAME = FG1dat, FILENAME = 'E:\MSSQLSERVER\MSSQL$INST1\Data\TranFG1dat3.ndf', SIZE = 10MB, MAXSIZE = 1000MB, FILEGROWTH = 5MB ) TO FILEGROUP FG1; GO --Assumption:IX_MyIndex on Table_1 exists in the PRIMARY filegroup USE [jvtest] GO CREATE NONCLUSTERED INDEX [IX_MyIndex] ON [dbo].[Table_1] ( [myval] ASC )WITH (DROP_EXISTING = ON) ON [FG1] GO