Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Move a clustered index from one filegroup to another on a production database Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2012 2:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:33 PM
Points: 47, Visits: 414
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

Post #1381722
Posted Tuesday, November 6, 2012 10:43 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
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.


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1381793
Posted Wednesday, November 7, 2012 3:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1381889
Posted Wednesday, November 7, 2012 3:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1381891
Posted Wednesday, November 7, 2012 4:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
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


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1381905
Posted Wednesday, November 7, 2012 4:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1381911
Posted Wednesday, November 7, 2012 12:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:33 PM
Points: 47, Visits: 414
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"
Post #1382115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse