What is best way to move large number of existing indexes into new file-group - DB Size: 700GB

Question

I have requirement to move all the indexes into different storage space (file-group) within existing SQL Server 2014 Database (Current Size: ~700GB)

I'm planning for approach where i can drop existing indexes and recreate them on new file-group. I prepared a script to do the same activity (re-creating indexes one by one) with cursor, but after executing the script, my database is very slow, and it's running for more than 20 hours.

Any advise/recommendations to accomplish this activity in production database where it's got to manage more than 1,000 users requests.

Answer 1

The answer is: you cannot run heavy scripts and not slow down the DB server.

I quess all you have to do is to pick kind of light-workload-hours and REBUILD indexes one by one with your own hands monitoring blocking and overall server load.

General advice would be to use

ALTER INDEX <IndexName> REBUILD;

instead of recreating them.

Also consider ALTER INDEX options here such as:

• ONLINE
• RESUMABLE
• MAX_DURATION
• MAXDOP
• WAIT_AT_LOW_PRIORITY

Have a look at these useful links:

• one
• two

Take care about free space when rebuilding online, as stated here.

Answer 2

Consider moving clustered indexes into different file-group means the table itself moved new file-group. I believe, your scope is to move NON Clustered indexes into new file-group to improve the performance in SQL Server.

In that case you can use following query, which moves the existing index into new file-group. For more details..

CREATE NONCLUSTERED Index NonClusteredIndexName ON TableName (Column1, Column2)
WITH (DROP_EXISTING = ON) 
on NewFileGroup;

As it mentioned by "George K" answer you need find a maintenance windows to perform this activity in production server as it required additional resources from the server that eventually effects on performance.

To make scope simpler, you may script-out existing indexes and plan for batch-wise move. Following query might help you to start with.

This query produce the Create Index statement but without columns, i was planning use STRING_AGG to do that, Unfortunately that's not applicable to your SQL Version:

        SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],  
          T.[name] AS [table_name], 
          I.[name] AS [index_name], 
          AC.[name] AS [column_name],  
          ic.is_included_column,
          collation_name,
          I.[type_desc] AS IndexType,
          'CREATE ' +  I.[type_desc] + ' Index ' + 
          I.[name] collate SQL_Latin1_General_CP1_CI_AS + 
          ' ON ' + 
          T.[name] AS SQLStatement

        FROM sys.[tables] AS T  
          INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]  
          INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] 
          INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] 
        WHERE T.[is_ms_shipped] = 0 AND I.type = 2