Filegroups
3 posters
Page 1 of 1
Filegroups
Hello all,
I have a couple of question about filegroups.
1) A long time ago, I learned that you should put dimensions in one filegroup, facts in another filegroup and indexes/PK constraints in yet another. Though I understand there's no one solution that will work for everyone, is this still considered to be a good practice in general?
2) I've done some research and found that READ ONLY filegroups might make sense for my dimensions. Have any of you had a positive experience with this?
3) I also read that files within a filegroup should ideally be the same size and occupy more than one drive, and that an index filegroup will only really help if the file(s) are on a separate drive with its own controller. We have virtual machines on a SAN, so I wonder whether we meet that criteria (I assume not). But I'm assuming that we'll still see benefits from filegroups in general given our hardware configuration?
I have a couple of question about filegroups.
1) A long time ago, I learned that you should put dimensions in one filegroup, facts in another filegroup and indexes/PK constraints in yet another. Though I understand there's no one solution that will work for everyone, is this still considered to be a good practice in general?
2) I've done some research and found that READ ONLY filegroups might make sense for my dimensions. Have any of you had a positive experience with this?
3) I also read that files within a filegroup should ideally be the same size and occupy more than one drive, and that an index filegroup will only really help if the file(s) are on a separate drive with its own controller. We have virtual machines on a SAN, so I wonder whether we meet that criteria (I assume not). But I'm assuming that we'll still see benefits from filegroups in general given our hardware configuration?
anna.rwfh- Posts : 6
Join date : 2013-05-15
Re: Filegroups
From my experiences with SAN, the physical database arrangement, beyond table partitioning and indexing, is not terribly important. Any grouping you do in the physical schema is more logical than anything else. At best you can save time with backups if you don't backup the indexes (which is probably not a good idea anyway). I mean, there really isn't a notion of physical 'drives'. Its all RAID and the SAN system pretty much manages where things go on it's own... It's kind of like one really big disk drive.
As far as dimensions go, don't you need to update them on a regular basis?
As far as dimensions go, don't you need to update them on a regular basis?
Re: Filegroups
*Sigh* That's what I was afraid of.ngalemmo wrote:From my experiences with SAN, the physical database arrangement, beyond table partitioning and indexing, is not terribly important. Any grouping you do in the physical schema is more logical than anything else. At best you can save time with backups if you don't backup the indexes (which is probably not a good idea anyway). I mean, there really isn't a notion of physical 'drives'. Its all RAID and the SAN system pretty much manages where things go on it's own... It's kind of like one really big disk drive.
Once a week at most.ngalemmo wrote:As far as dimensions go, don't you need to update them on a regular basis?
anna.rwfh- Posts : 6
Join date : 2013-05-15
Re: Filegroups
Since you update the dimensions, it would not make sense to put them in a 'read only' group. DBMS's usually have this feature to support archived data sitting on read-only optical media.
As far as your disappointment with SAN, I consider it a blessing. Less things to worry about and their performance is generally far superior to traditional disks.
As far as your disappointment with SAN, I consider it a blessing. Less things to worry about and their performance is generally far superior to traditional disks.
Re: Filegroups
I read a blog post that suggests doing this for dimensions that are not updated often. I haven't tested it yet so I can't say from experience, but I'm assuming it's easy to change a filegroup's settings back and forth.ngalemmo wrote:Since you update the dimensions, it would not make sense to put them in a 'read only' group. DBMS's usually have this feature to support archived data sitting on read-only optical media.
I'm hoping that happens for us, but it hasn't yet. It might be due to bandwidth limitations between here and the data center. We shall see.ngalemmo wrote:As far as your disappointment with SAN, I consider it a blessing. Less things to worry about and their performance is generally far superior to traditional disks.
Thanks very much for your feedback!
anna.rwfh- Posts : 6
Join date : 2013-05-15
Re: Filegroups
As far as your disappointment with SAN, I consider it a blessing. Less things to worry about and their performance is generally far superior to traditional disks.
mickle666- Posts : 1
Join date : 2015-03-17
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum