Moving Large Table to Different File Group
-
Comments posted to this topic are about the item Moving Large Table to Different File Group
-
how would we move a table without clustered index to a different filegroup ?
-
Yeah Jayant has a valid question for removing table without cluster Index.
If a table has non-clustered index then I believe you will remove them first and recreate them after moving the table into different space.
-
Would Create Index WITH Drop_Existing work for moving non-clustered indexes.
"Keep Trying"
-
I think my question is little confusing, let me make it more clear
If we have a table with cluster index then by simply recreating the clustered index on different file group will move the table also on that file group
now I have a table with no index (for simplicity) and I would like to move that table to a newly created filegroup, how would I do that?
-
jayant (10/16/2008)
I think my question is little confusing, let me make it more clearIf we have a table with cluster index then by simply recreating the clustered index on different file group will move the table also on that file group
now I have a table with no index (for simplicity) and I would like to move that table to a newly created filegroup, how would I do that?
Not sure I understand how not having a clustered index makes things simpler. Every table should have a clustered index. I would suggest creating a clustered index which would then move your data to the new filegroup.
If you really really don't want the index you can drop it again - however a clustered index does not add any overhead to the table so you should definitely have one.
-
Clustered indexes causes page splits, and columns from it are included into any other index, so it adds overhead.
-
Vitali Lisau (10/16/2008)
Clustered indexes causes page splits, and columns from it are included into any other index, so it adds overhead.Ok yes granted, that is true, but will depend on the fill factor specified when creating the index.
In the context of the question I don't think it's relevant as [presumably] the table in question that is "simple" and doesn't need any indexes will have very few rows and is probably for lookup only, so page splits wouldn't be a factor.
For page splits to become an issue the table would have to have a reasonable number of inserts performed regularly and if that's that case, it should definitely be indexed!
-
Hi Raj,
Nice article. In the attachment there is a script that moves multiple tables in different databases at once. The original description of the script is here:
http://tsql.ca/Resources/Articles/MoveTablesbetweenFilegroups/tabid/610/Default.aspx
Unfortunately at the publishing time the some characters disappeared from the script, so I attached it here.
The idea was the same as yours - recreate the clustered index on another filegroup.
So, in my script (new) filegroups are assumed to be in place. All you need is to create a list of database/table/filegroup elements. Each table will be checked if it is in a proper filegroup, if not - it's moved to the proper one.
There are few more moments one should consider, especially if you plan to free and remove the original filegroup:
1. BLOB data is not moved this way, it resides in the original location. That means for the tables with BLOBs one has to create new table in the another filegroup(possibly specifying different filegroup for the BLOB data); copy the data over; drop original table and rename the new table.
2. Statistics is not moved this way. It should be detected by the table name and then dropped. When recreated - it will be placed in the same [new] filegroup as the table.
Such scenarios I handle in separate scripts delivered along with the attached one through my incremental build mechanism described here:
http://www.simple-talk.com/sql/database-administration/deploying-database-developments/
-
To move a table to another filegroup, simply pick a (numeric) column, or create a new rowID column and create an clustered index on one of those columns.
The procedure is simple, however when the table becomes bigger you need to check your disk space and make sure that you have space equal to ~2 times the size of the table that you want to move or the operation will fail and you wasted a lot of time and resources.
-
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.
-
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 on googles mail service -
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.
-
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.
- SQL 2008/R2/2012/2014/2016/2017
- Oracle 8/9/10/11
- MySQL 4/5 and MariaDB -
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.
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply