Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2005-07-26 : 04:13:55
|
| MorningI have never used Filegroups before, so am a little confused as to the best solution. I have read that the indexes, data and system tables should all be placed on their own filegroup. However, after creating the tables, I then create the primary keys (as a clustered index). Should I place these constraints onto the data filegroup or the filegroup for the indexes?ThanksHearty head pats |
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-07-26 : 07:28:26
|
| >>Should I place these constraints onto the data filegroup or the filegroup for the indexes?you can't move clustered index to different file group from your table, because clustered index is the table itself, clustered index just defines the phisical order of rows in the table |
 |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-07-26 : 21:12:09
|
| Correct me if I'm wrong, if you are using the same phyical HDD for your filegroups, you don't really get any improvement at all even if you seperate your indexes (non-clustered) and tables. It only helps when you have different phyical HDD (or raid). |
|
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-07-27 : 08:16:38
|
| >>if you are using the same phyical HDD for your filegroups, you don't really get any improvement at all of cause, but there maybe other reasons to have several filegroups, for example you can backup only one filegroup but not all of them. It can be usefull if you have large database, this will speed up backup and restore process if you need to backup often only part of the database |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-08-02 : 02:43:40
|
| Yup, thats the only reason I guess to use filegroup for a given HDD, apart from that I can't really think of any other reasons. |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-02 : 10:37:36
|
quote:
Originally posted by Westley
Yup, thats the only reason I guess to use filegroup for a given HDD, apart from that I can't really think of any other reasons.
What about on a SAN? Our Net admin said it doesnt matter how the SAN is configured because the SAN manages everything dynamically. Looking at this from a general hardware standpoint it doesnt make sence to me. You still have a basic MAX IO per disk times the number of disks, right? |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-02 : 13:18:28
|
quote:
Originally posted by SQLServerDBA_Dan
quote:
Originally posted by Westley
Yup, thats the only reason I guess to use filegroup for a given HDD, apart from that I can't really think of any other reasons.
What about on a SAN? Our Net admin said it doesnt matter how the SAN is configured because the SAN manages everything dynamically. Looking at this from a general hardware standpoint it doesnt make sence to me. You still have a basic MAX IO per disk times the number of disks, right?
No SAN admins reading our posts? Hehe. Anyone know enough about SANs to explain them to me a bit? I'd love to be able to move the file groups around to speed things up.DanielSQL Server DBA |
|
|
|
|
|
|
|
|
Filegroups and data placement