Moving Index to a separate Filegroup

To improve the query performance we decided to have separate filegroup for indexes. We decided to move the indexes of very large tables indexes to the new filegroup. Our intention was to separate the index from the data i.e. have the table data (Clustered index) in one filegroup and Nonclustered indexes in another separate filegroup to improve I/O.

We have a table with 1.5 million records; the following is the table schema and its present in [Primary] filegroup which is the default:

CREATE TABLE [dbo].[testmember](
[memberid] [BIGINT] IDENTITY(1,1) NOT NULL,
[name] [NVARCHAR](50) NULL,
[emailaddress] [NVARCHAR](100) NULL,
[firstname] [NVARCHAR](50) NULL,
[createddate] [DATETIME] NULL,
[testindex] [VARCHAR](4000) NULL,
CONSTRAINT [PK_testmember] PRIMARY KEY CLUSTERED (
[memberid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)) ON [PRIMARY] 

Please make use of the below T-SQL scrip to create a new filegroup named as [Index] on an existing database called Test:

ALTER DATABASE [Test] ADD FILEGROUP [INDEX]
GO
ALTER DATABASE [Test] ADD FILE ( NAME = N'Test_Index', FILENAME = N'D:MSSQLInfisystemTest_index.ndf' , SIZE = 2097152KB , FILEGROWTH = 10%) TO FILEGROUP [INDEX]
GO 

After creating the new filegroup in D:drive, the database has the primary filegroup in E:drive, log file in F:drive and the new filegroup in D:drive.

Steps to move the index to new filegroup:

To move the Clustered index to the new filegroup there are 2 methods: I prefer the 2nd one as it is quicker.

--Took 3 mins 20 secs
ALTER TABLE [dbo].[testmember] DROP CONSTRAINT [PK_testmember]

The above command will drop the constraint which will also drop the CI index.

--Took 12 mins
ALTER TABLE [dbo].[testmember] ADD CONSTRAINT [PK_testmember] PRIMARY KEY CLUSTERED ( MEMBERID ASC
)
WITH (ONLINE=ON)
ON Index_Temp

The above command will recreate the constraint/clustered index.

In the 2nd method the existing index is dropped and rebuild which is much faster.

--Took 3 mins
CREATE UNIQUE CLUSTERED INDEX pk_testmember ON DBO.TESTMEMBER
(
memberid ASC
)
WITH (DROP_EXISTING=ON, ONLINE=ON)
ON Index_Temp 

For NC index just use the below option:

--Took 2 secs
CREATE INDEX Ix_temp ON DBO.TESTMEMBER
(
NAME ASC
)
WITH (DROP_EXISTING=ON, ONLINE=ON)
ON Index_Temp

Moving the CI will move the table also to new filegroup. So if the table/CI and NCI reside in the same filegroup it wouldnt help much. So always ensure that you have the CI and NCI in different drives.

The main difference between the two methods is that #1 drops the clustered key, turning the table into a heap. All non-clustered indexes must be converted to use the Row ID instead of the clustered key. Then you put the clustered index back, and SQL Server has to rebuild the non-clustered indexes again. Hence it takes 12 minutes.

Whereas in WITH the DROP_EXISTING option (#2nd option), there is no need to convert the table to a heap as the existing index is dropped and rebuild and hence the non-clustered indexes do not need to be modified. This will be the main cause of the performance difference.

While testing the #1st option I performed simultaneous DML operations to see what the impact is. Of course there were blocks being created regardless of ONLINE operation. In the 1st option the DML operations did finish prior to the index creation and didnt have to wait till the index being dropped and recreated. However in the #2nd option the DML were made to wait till the index creation completed. As long as we create these type of resource intensive operations we have to do it ONLY during off-production hours.

To find the objects present in a filegroup please make use of the below query:

SELECT O.Name AS [OBJECT Name], O.[Type], I.name AS [INDEX name], I.Index_Id, I.type_desc AS [INDEX TYPE], F.name AS [Filegroup Name]
FROM sys.indexes I
INNER JOIN sys.filegroups F
ON I.data_space_id = F.data_space_id
INNER JOIN sys.objects O
ON I.[oject_id] = O.[object_id]
WHERE I.data_space_id = 2 --Specify the filegroup's data_space_id available in sys.filegroups
GO