August 15, 2002 at 7:57 am
Is there a way of placing existing tables/indexes onto different filegroups using TSQL? BOL gives examples using Enterprise Manager only. Thanks
August 15, 2002 at 8:04 pm
You'll need to use a second ON to set the filegroup. For instance:
CREATE INDEX IDX_MyTable_TheColumn
ON MyTable (TheColumn)
ON MyNewFilegroup
Here's the link to the Books Online page which covers CREATE INDEX. The information is there, but there aren't any examples with respect to using a different filegroup from whatever is set as default:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_64l4.asp
K. Brian Kelley
http://www.truthsolutions.com/
K. Brian Kelley
@kbriankelley
August 16, 2002 at 3:37 am
Thanks for the reply Brian.
What I am investigating is whether there are any performance gains from putting tables and indexes from an existing database into different filegroups. The database was created with just the PRIMARY filegroup. I'm creating additional filegroups and placing some of the existing tables and indexes in these new filegroups. What is the TSQL syntax to do this ? For the indexes, I could use drop/create index. For the tables, is there an alternative to using DTS and dropping the table and re-creating it on the new filegroup ?
In BOL "Placing Tables on Filegroups", "To place an existing table on a different filegroup", the example uses Enterprise Manager only
My sql is as follows
alter database dat_1
add filegroup fg_1
alter database dat_1
add file ( /* file details here */ ) to filegroup fg_1
--use Enterprise Manager to place table in fg_1
--can this be done using alter table or alter database ?
August 16, 2002 at 2:15 pm
If you have a clustered index on a table, you can recreate it on a different filgroup. The data is physically stored in the clustered index, so you'll be effectively rebuilding it. I don't think there's an easy way if you aren't using a clustered index.
Certainly if the filegroups are located on different physical sets of drives performance should be increased. If they are located on the same set of drives, you're basically going to the same source to get the information regardless of how you split up the data across filegroups. Since Disk I/O is the slowest part of any data operation, even toggling them as read-only probably doesn't gain anything. I've not tested, so I am speculating. The intent of marking a given filegroup as read-only is to prevent updates.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 20, 2002 at 3:54 am
Tim, If you look at what EM does you will see it generates the T-SQL script. It creates a new temp. table in the destination file group, then copies the data to the new table. It then drops the original table and renames the temp table. That seems to be the only way to do it. It makes sence if we consider that a file group represents a different area on the physical disk.
August 21, 2002 at 8:38 am
The tables I'm looking at have non-clustered indexes only. I'm stuck with the design as its a bought in package.
Lgm - Thanks for the advice. I did have Profiler running whilst making the changes using EM but didn't spot anything. Guess I need some more practice with Profiler !
August 21, 2002 at 10:10 am
This link leads to a DMO based application that can be used to move indexes from various
filegroups.
Look under the Downloads section.
HTH
Steve Hendricks
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
August 22, 2002 at 4:16 am
The best most sure fire way to get it to move properly and not lose triggers or constraints is to use EM as everything is scripted there. Looking in Profiler does seem to miss steps when running and may not help you fully. In EM right click the table in Question and do design. There you can right click and do indexes and table and text columns. All can exist on seperate fiels from each other. I have done this and found marked gain esepcially on large tables and long term gains on small tables that are not altered mouch or at all (no indexing or cleaning of the files need be done so maintainence only has to be done on the large tables). I am too involved with a project currently but when done I plan to revisit and get some benchmarks and such to provide for those in question. The key way thou to gain performance from filegroups is to have on seperate drive(s)/arrays.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 20, 2005 at 7:37 am
There is a reliable script that moves tables between filegroups, and it is available on
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply