InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 02:00:33 GMT20http://www.sqlservercentral.com/Forums/Topic1381722-146-1.aspxthanks 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"Wed, 07 Nov 2012 12:02:50 GMTDesmoShanehttp://www.sqlservercentral.com/Forums/Topic1381722-146-1.aspx[quote][b]sanket kokane (11/7/2012)[/b][hr][quote][b]Bhuvnesh (11/7/2012)[/b][hr][quote][b]DesmoShane (11/6/2012)[/b][hr]will the resulting non-clustered indexes will they be rebuilt automaticaly and be an online operation[/quote] 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 [/quote]I don't thinks so.look at the Paul's blog ... Myth No.3[url]http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-19-Misconceptions-around-index-rebuilds-%28allocation-BULK_LOGGED-mode-locking%29.aspx[/url][/quote] thanksWed, 07 Nov 2012 04:44:41 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1381722-146-1.aspx[quote][b]Bhuvnesh (11/7/2012)[/b][hr][quote][b]DesmoShane (11/6/2012)[/b][hr]will the resulting non-clustered indexes will they be rebuilt automaticaly and be an online operation[/quote] 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 [/quote]I don't thinks so.look at the Paul's blog ... Myth No.3[url]http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-19-Misconceptions-around-index-rebuilds-%28allocation-BULK_LOGGED-mode-locking%29.aspx[/url]Wed, 07 Nov 2012 04:24:57 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1381722-146-1.aspx[quote][b]DesmoShane (11/6/2012)[/b][hr]will the resulting non-clustered indexes will they be rebuilt automaticaly and be an online operation[/quote] 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 Wed, 07 Nov 2012 03:52:25 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1381722-146-1.aspxyou can also opt [b]SORT_IN_TEMPDB = ON[/b] to divert the IO load to tempdb tooWed, 07 Nov 2012 03:46:22 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1381722-146-1.aspx[quote][b]DesmoShane (11/6/2012)[/b][hr]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 thissql version is 2008 R2 EnterpriseThe 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 scriptUSE [Database_Name]GOCREATE 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]GOCan 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 fashionwill the resulting non-clustered indexes will they be rebuilt automaticaly and be an online operation[/quote]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.Tue, 06 Nov 2012 22:43:26 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1381722-146-1.aspxI 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 thissql version is 2008 R2 EnterpriseThe 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 scriptUSE [Database_Name]GOCREATE 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]GOCan 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 fashionwill the resulting non-clustered indexes will they be rebuilt automaticaly and be an online operationTue, 06 Nov 2012 14:55:11 GMTDesmoShane