filegroups

Question

filegroups

Tim Ellison

SSChasing Mays

Points: 609
More actions
August 15, 2002 at 7:57 am

#57864

Is there a way of placing existing tables/indexes onto different filegroups using TSQL? BOL gives examples using Enterprise Manager only. Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

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

bkelley@sqlservercentral.com

http://www.truthsolutions.com/

K. Brian Kelley
@kbriankelley

Tim Ellison SSChasing Mays Points: 609 More actions · Answer 2

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 ?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

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

lgm Newbie Points: 3 More actions · Answer 4

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.

Tim Ellison SSChasing Mays Points: 609 More actions · Answer 5

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 !

shendricks SSChasing Mays Points: 635 More actions · Answer 6

This link leads to a DMO based application that can be used to move indexes from various

filegroups.

http://www.ocsqlsug.org/

Look under the Downloads section.

HTH

Steve Hendricks

shendricks@afsconsulting.com

Steve Hendricks
MCSD, MCDBA
Data Matrix

shendricks@afsconsulting.com
(949) 588-9800 x15

Antares686 SSC Guru Points: 125444 More actions · Answer 7

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)

Omri Bahat-228920 Mr or Mrs. 500 Points: 540 More actions · Answer 8

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.