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 ««12345»»»

Moving Large Table to Different File Group Expand / Collapse
Author
Message
Posted Thursday, October 16, 2008 8:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 1, 2012 1:18 PM
Points: 110, Visits: 261
I have designed a few tables that we expected to be very actively inserted/deleted against and grow as the business grows. In these cases I created each table on it's own filegroup/file to start with.
When first rolling out the features we were able to place them on existing drives. As i/o performance and capacity needs increased for any of the tables it was a quick offline operation to move the file to dedicated drives.
Post #587007
Posted Thursday, October 16, 2008 8:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 4,308, Visits: 6,095
I think several things were missing from the article:

1) You should cover the need to handle foreign keys to the PK

2) You should cover a best practice of dropping the NC indexes prior to dropping the C index, then recreating same after the move.

3) You should DEFINITELY not recommend shrinking a database!!! The fragmentation caused by this is horrendous, and the database will simply fragment more (and at the OS file level) as it grows back up.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #587054
Posted Thursday, October 16, 2008 9:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, May 25, 2014 10:09 AM
Points: 283, Visits: 1,114
When you created the new file in the new filegroup you set a very small initial size with small growth increment.

To avoid excess fragmentation, you should set the initial size so it is big enough for the new data, and for medium term future growth.



Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
Post #587083
Posted Thursday, October 16, 2008 10:26 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:15 AM
Points: 509, Visits: 854
I have actually found if you split the tables into multiple files it will decrease the time it takes to have a table grow. I also have noticed a flaw when it comes to table growth, if you hit the wall on a table and it needs to become larger it will lock the file group into a single cpu which will slow everyone down not just people using this table when using only a single file group. If you use different file groups it seems not lock to a single cpu unless you are accessing tables inside that file group.

I would also suggest moving your temp tables into its own file group as they are the ones growing and shrinking a lot.



Over 11yrs in IT and 9yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012
- Oracle 8/9/10
- MySQL 4/5
Post #587135
Posted Thursday, October 16, 2008 11:04 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 1, 2014 7:26 AM
Points: 908, Visits: 2,804
This is a hack, plain and simple. If you really want to move the table, the best way is to create a "temp: table on the new filegroup. move the data, drop the existing table, rename the "temp" table and then re-create all of the FK's, constraints, and indexes.

It's a bit of a PITA but it's the only way to ensure the whole thing gets moved properly. Besides, that's pretty much what SQL Server is doing behind the scenes anyhow.

By best practices the nc indexes should (probably) be on their own filegroup anyhow but that's a different issue.
Post #587170
Posted Thursday, October 16, 2008 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 12, 2009 3:50 PM
Points: 4, Visits: 16
there is a problem (aka, bug) with this if your table has certain qualities.
For example,
I had a table that had a text field, so it had LOB data - I converted it to varchar(max). After this, I then tried to move it to a different filegroup as show in this article, however, the LOB data still stays on the old filegroup. If anyone else has experienced this and found a workaround other than selecting all the old data out into a new table please let me know..

Thanks
Robert
Post #587176
Posted Thursday, October 16, 2008 11:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 1, 2014 1:58 PM
Points: 3, Visits: 117
rtowne (10/16/2008)
there is a problem (aka, bug) with this if your table has certain qualities.
For example,
I had a table that had a text field, so it had LOB data - I converted it to varchar(max). After this, I then tried to move it to a different filegroup as show in this article, however, the LOB data still stays on the old filegroup. If anyone else has experienced this and found a workaround other than selecting all the old data out into a new table please let me know..

Thanks
Robert


That's not a bug, but a lack of control over the BLOB/statistics data placement.
Or, from other hand :), all that Clustered Index recreation is just a hack as we were told already up above in this discussion.

It is quite unusual requirement to move tables between the filegroups. Check out the link I gave before - that was a really nasty requirement to re-arrange ALL tables in multiple databases into new filegroups.
But I still had to move tables with BLOB separately. Thanks god SSMS/EM generated the script for those few table in a second for me :).
Post #587180
Posted Thursday, October 16, 2008 1:25 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 4,308, Visits: 6,095
Pam Brisjar (10/16/2008)
This is a hack, plain and simple. If you really want to move the table, the best way is to create a "temp: table on the new filegroup. move the data, drop the existing table, rename the "temp" table and then re-create all of the FK's, constraints, and indexes.

It's a bit of a PITA but it's the only way to ensure the whole thing gets moved properly. Besides, that's pretty much what SQL Server is doing behind the scenes anyhow.

By best practices the nc indexes should (probably) be on their own filegroup anyhow but that's a different issue.


Can you please give justification for why you call this a 'hack' and why you feel your mechanism is the 'best way'??

Per 2005 BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/bff3a4bf-4bac-40be-a9bf-e75b1c978711.htm), microsoft recommends this:

Move a table from one filegroup to another by dropping the clustered index and specifying a new filegroup or partition scheme in the MOVE TO clause of the DROP INDEX statement or by using the CREATE INDEX statement with the DROP_EXISTING clause.

That isn't exactly what the author specified, but same intent/results.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #587273
Posted Thursday, October 16, 2008 3:21 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, Visits: 836
Somebody mentioned the CREATE INDEX WITH DROP EXISTING, and I remember trying that and the answer is yes... and in EE you can do it online... it's pretty sweet... except the necessity of dropping all primary and foreign keys.

At the end of the day you need to do it offline so as to keep referential integrity.

pity.


Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #587338
Posted Thursday, October 16, 2008 8:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:42 PM
Points: 89, Visits: 748
Works only in SQL Server 2005 , doesn't work in 2000.

file size mentioned is very low, including growth rate.


Post #587400
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse