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


Moving Large Table to Different File Group


Moving Large Table to Different File Group

Author
Message
Adrian Hains
Adrian Hains
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
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.
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5964 Visits: 8313
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
Richard Fryar
Richard Fryar
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 1171
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
Adam Seniuk
Adam Seniuk
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 1040
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 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
Pam Brisjar
Pam Brisjar
SSC Eights!
SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)

Group: General Forum Members
Points: 910 Visits: 2804
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.
rtowne-879083
rtowne-879083
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Alexander Karmanov
Alexander Karmanov
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 134
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 Smile, 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 Smile.
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5964 Visits: 8313
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
SQLBOT
SQLBOT
SSChasing Mays
SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)

Group: General Forum Members
Points: 658 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
sayfrend
sayfrend
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 750
Works only in SQL Server 2005 , doesn't work in 2000.

file size mentioned is very low, including growth rate.
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