2

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.

0
2

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:

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

0
1

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
2
  • Yes, I move non-clustered indexes. I began to refine the query so that I could divide the work into several parts and do the same in the low traffic times of the day in a few days. – Ali Farzanfard Sep 11 '19 at 12:58
  • @AliFarzanfard, that's good, all the best! – Shekar Kola Sep 11 '19 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.