SQL Server – Moving a Non Clustered Index to another filegroup

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

 Read More

Window Perfmon scripting, SQL Server perfmon and how to perfmon

SQL Server non-clustered index checklist - SQL Server DBA

SQL Server - List all indexes of all tables


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – Moving a Non Clustered Index to another filegroup


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer