Indexes Scripts SQL Server

How to move Indexes (Cluster and Non Cluster) from Primary File group to New File group?

As a DBA, many times there are requirements to segregate data in a Database into multiple file groups. There can be many reasons like below:

  • Performance issues (Disk I\O)
  • Disk space issues
  • Data archiving etc

There are multiple solutions to resolve such issues:

  • Move the big tables to other File groups in which frequency of transactions are higher
  • Segregate the table’s data and it’s Indices (specially Cluster Indices) into different File groups and place the File groups to different drives
  • Move the database having high volume of transactions to other disk drives etc

Here I will discuss about a step by step approach of moving the Indexes (Cluster and Non Cluster) from Primary File group to New File group. I did it once and it worked well on SQL Server 2008 R2.

What all do you need? 

  • Database: AdventureWorks
  • SQL Server : SQL Server 2008 R2 (I tested on Development Edition)

Steps:

1. If you are planning to perform these steps on any other database, I would suggest, take a Full backup of the database after bringing down all the Applications down so that there is no connectivity on the database.
2. Add File group to the database (Open SSMS)

USE [master]
GO
ALTER DATABASE [AdventureWorks] ADD FILEGROUP [FG_INDEX]
GO

3) Add File to the newly added File group

USE [master]
GO
ALTER DATABASE [AdventureWorks] ADD FILEGROUP [FG_INDEX]
GO
ALTER DATABASE [AdventureWorks] 
ADD FILE ( NAME = N'AdventureWorks2008_Index', 
FILENAME = N'H:\MSSQL\DATA\AdventureWorks2008_Index.ndf' , 
SIZE = 5120MB , 
FILEGROWTH = 0) 
TO FILEGROUP [FG_INDEX]
GO

3. Generate Create Index script using script. Run this script on AdventureWorks database.

4. Save the results in an excel sheet for future comparison and copy the Index_Create_Statement column data in a notepad and replace the Filegroup name from Primary to FG_INDEX

Now it must look something like below:
Before:

 Create Unique Clustered Index [PK_ProductInventory_ProductID_LocationID] On [Production].[ProductInventory] ([ProductID] Asc,[LocationID] Asc) With (Drop_Existing = ON, SORT_IN_TEMPDB = ON, Fillfactor = 90, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = NONE) On [PRIMARY];

After:

 Create Unique Clustered Index [PK_ProductInventory_ProductID_LocationID] On [Production].[ProductInventory] ([ProductID] Asc,[LocationID] Asc) With (Drop_Existing = ON, SORT_IN_TEMPDB = ON, Fillfactor = 90, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = NONE) On [FG_NDEX];

You may also change other Index creation parameters as well if it’s required.

5.Now execute the altered scripts from notepad on the database and verify the successful execution.
Note: Please ignore the errors related to XML indices and run the next SQL statements.

6.Verify the Indices File group by running the step 3 script once again & compare.

You are all set. All the Indices in the database are moved to the new File group.

Avatar

Technical Editor

About Author

SQL Server DBA, Trainer, Blogger, Author

Leave a comment

You may also like

Installations & Configuration SQL Server Troubleshooting

SQL Server Database mail common issue

Problem: SQL Server Database Mail common issue We always use SQL Server Database mail feature to create email notification for
Installations & Configuration SQL Server Technology Virtualisation

How to use Microsoft Virtual Labs for Practical implementation

One of the important questions often asked by my fellow mates is how we can implement practical scenarios without configuring