Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
DesmoShane
DesmoShane
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 454
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
sanket kokane
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
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
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
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
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
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
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
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
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
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
DesmoShane
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 454
thanks for your help Smile 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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search