Wednesday, 4 April 2012

Moving Index in Separate Filegroup


By default the index are stored in a same file group as the base table on which the index is created. It is important that if we have a very large table with index. To improve the performance of the query we have to move the index in a separate file group (Better in different Drive/Hard disk) to improve I/O performance.

But we can do the following:

1.    Create nonclustered indexes on a filegroup other than the filegroup of the base table.
2.   Partition clustered and nonclustered indexes to span multiple filegroups.
3     Move a table from one filegroup to another by dropping the clustered index and specifying a  
     new filegroup or partition scheme in the MOVE TO clause of the DROP INDEX      statement 
      or by using the CREATE INDEX statement with the DROP_EXISTING clause.
      
Some points should be taken care of:

1.     If a table has a clustered index, moving the clustered index to a new filegroup moves the table to 
     that filegroup.
2.     You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using   
     Management Studio. To move these indexes use the CREATE INDEX statement with the 
    (DROP_EXISTING=ON) option in Transact-SQL.



The scripts gives us an idea, how to move the index in different file group.

USE my_db;
GO
/*
    Creates the TranFG filegroup on the my_db database
*/
ALTER DATABASE my_db
ADD FILEGROUP TranFG;

GO

/*
  Adds the TranFGData file to the TranFG filegroup. Please note that you
  will have to change the
  filename parameter in this statement to execute it without errors.
*/

ALTER DATABASE my_db
ADD FILE
(
    NAME = TranFGData,
    FILENAME = 'C:\Program Files\Microsoft SQL
                Server\MSSQL11\MSSQL\DATA\TranFGData.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP TranFG;

GO
/*
  Creates the IX_Employee_OrganizationLevel_OrganizationNode index
  on the TransactionsPS1 filegroup and drops the original
  IX_Employee_OrganizationLevel_OrganizationNode index.
*/
CREATE NONCLUSTERED INDEX IX_Cust
    ON My_Cust (CustId)
    WITH (DROP_EXISTING = ON)
    ON TranFG;
GO


Hope you like it.


Posted by: MR. JOYDEEP DAS

1 comment: