October 2, 2009 at 11:37 am
Hello Everyone,
I have two questions.
First one is like this, I have created 3 filegroups , A Primary , B FG_2 , C FG_3. Then i created one table called TEST in filegroup FG_2 , and inserted some data. This TEST table doesn't have any primary key becuase rows are not unique. Now , my question is , I want to move (not copy) this TEST table to filegroup FG_3.
another question is ,
I have created another table called TEST2 on FG_2. TEST2 does have primary key on it and hence it does have clustered index on it. Then I droped this clustered index , and created new clustered index on filegroup FG_3. Now when i see at table property -> storage it shows this table belong to FG_3 not FG_2 !!! Then ,I read at many places that if create index on another FG , it will move my table to that FG. But my question is what if i want to create table in another FG and Indexes,views on another FG to improve performance ?
Thanks in advance for any help.
October 2, 2009 at 11:44 am
Tables and their related indexes should be separated onto separate files and physical disks for optimum performance running queries, but not separate filegroups. If they are in separate filegroups, then you cannot back up and restore them as a single unit.
October 2, 2009 at 1:39 pm
When you create an index, you can specify which filegroup to create it in. If you create the clustered index in a certain filegroup, then that's the table. You can then create the indexes in another.
As pointed out, this isn't necessarily a good idea. But if you want to test it out, that's how you do it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 2, 2009 at 9:45 pm
Ow ya, I think , I missed the point here.
Okey then,How do i put them in seperate physical files ??? I cant find anything like that. All i have heard of is to put tables in different FGs
Can you explaint it in a bit more detail please.
BTW, that was very quick reply.:-)
October 2, 2009 at 10:11 pm
I've always made it a practice to separate table and index data into separate filegroups and therefore separate files. After all, isn't that the only way you can place a table or index on a specific physical file, be creating a file group with a specific file and then placing the object in that file group? That allows reads / writes related to table to pull data from multiple data files simultanously. Of course, backup strategy needs to account for that. Maybe I'm missing something that you others are aware of??
October 4, 2009 at 5:46 am
Sergey Vavinskiy (10/2/2009)
Tables and their related indexes should be separated onto separate files and physical disks for optimum performance running queries, but not separate filegroups. If they are in separate filegroups, then you cannot back up and restore them as a single unit.
Sergey how you want to achieve this goal ? in sql server you cannot place data in "files" you have to use filegroups - so if you want to place an object in separate file it has to be in a different filegroups ...
October 4, 2009 at 6:38 am
just a couple of helping scripts:
http://www.sqlservercentral.com/articles/Administration/64275/
http://www.sqlservercentral.com/scripts/Miscellaneous/31541/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 4, 2009 at 2:59 pm
Marcin Gol [SQL Server MVP] (10/4/2009)
Sergey Vavinskiy (10/2/2009)
Tables and their related indexes should be separated onto separate files and physical disks for optimum performance running queries, but not separate filegroups. If they are in separate filegroups, then you cannot back up and restore them as a single unit.Sergey how you want to achieve this goal ? in sql server you cannot place data in "files" you have to use filegroups - so if you want to place an object in separate file it has to be in a different filegroups ...
You may want to see some details about performance related to filegroups:
http://www.sql-server-performance.com/tips/filegroups_p1.aspx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply