Move a clustered index from one filegroup to another on a production database

Question

Move a clustered index from one filegroup to another on a production database

DesmoShane

Mr or Mrs. 500

Points: 551
More actions
November 6, 2012 at 2:55 pm

#284530

I would like to move a clustered index from one filegroup to another on a production database without causing any disruption for users and was wondering if this is the recommended process for doing this
sql version is 2008 R2 Enterprise
The table has non clustered indexes in a different filegroup so the clustered index is the only data in this filegroup. The options i think should work are are in the attached script
USE [Database_Name]
GO
CREATE CLUSTERED INDEX [Clustered_Index_Name] ON [dbo].[Table_Name]
(
[Column_1_Name] ASC,
[Column_2_Name] ASC,
[Column_3_Name] ASC,
[Column_4_Name] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = ON, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO
Can someone advise if doing this (apart from cpu\memory usage) will cause any unexpected issues with the user experience on the database that this is being run on and if i am aproaching this in a recommended or best practice fashion
will the resulting non-clustered indexes will they be rebuilt automaticaly and be an online operation

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sanket kokane Hall of Fame Points: 3465 More actions · Answer 1

DesmoShane (11/6/2012)
I would like to move a clustered index from one filegroup to another on a production database without causing any disruption for users and was wondering if this is the recommended process for doing this
sql version is 2008 R2 Enterprise
The table has non clustered indexes in a different filegroup so the clustered index is the only data in this filegroup. The options i think should work are are in the attached script
USE [Database_Name]
GO
CREATE CLUSTERED INDEX [Clustered_Index_Name] ON [dbo].[Table_Name]
(
[Column_1_Name] ASC,
[Column_2_Name] ASC,
[Column_3_Name] ASC,
[Column_4_Name] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = ON, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO
Can someone advise if doing this (apart from cpu\memory usage) will cause any unexpected issues with the user experience on the database that this is being run on and if i am aproaching this in a recommended or best practice fashion
will the resulting non-clustered indexes will they be rebuilt automaticaly and be an online operation

why you need to move clustered indexes to new filegroup ?

Moving clustered indexes to another filegroup is nothing but you are moving entire table to the new filegroup.

-----------------------------------------------------------------------------
संकेत कोकणे

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

you can also opt SORT_IN_TEMPDB = ON to divert the IO load to tempdb too

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

DesmoShane (11/6/2012)
will the resulting non-clustered indexes will they be rebuilt automaticaly and be an online operation

YEs. any change to the cluster keys forces all the non-clustered indexes to be rebuilt after the new clustered index has been created. but try to perform this on off-peak hours

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sanket kokane Hall of Fame Points: 3465 More actions · Answer 4

Bhuvnesh (11/7/2012)
DesmoShane (11/6/2012)
will the resulting non-clustered indexes will they be rebuilt automaticaly and be an online operation
YEs. any change to the cluster keys forces all the non-clustered indexes to be rebuilt after the new clustered index has been created. but try to perform this on off-peak hours

I don't thinks so.

look at the Paul's blog ... Myth No.3

http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-19-Misconceptions-around-index-rebuilds-%28allocation-BULK_LOGGED-mode-locking%29.aspx

-----------------------------------------------------------------------------
संकेत कोकणे

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

sanket kokane (11/7/2012)
Bhuvnesh (11/7/2012)
DesmoShane (11/6/2012)
will the resulting non-clustered indexes will they be rebuilt automaticaly and be an online operation
YEs. any change to the cluster keys forces all the non-clustered indexes to be rebuilt after the new clustered index has been created. but try to perform this on off-peak hours
I don't thinks so.
look at the Paul's blog ... Myth No.3
http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-19-Misconceptions-around-index-rebuilds-%28allocation-BULK_LOGGED-mode-locking%29.aspx

thanks

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

DesmoShane Mr or Mrs. 500 Points: 551 More actions · Answer 6

thanks for your help 🙂 so it looks like it shouldn't rebuild all the non-clustered indexes (sql 2008 r2) and as per always would pay to do this "out of hours if possible"