Filegroups

[anna.rwfh]

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?

[ngalemmo]

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?

[anna.rwfh]

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.

*Sigh* That's what I was afraid of.

As far as dimensions go, don't you need to update them on a regular basis?

Once a week at most.

[ngalemmo]

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.

[anna.rwfh]

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 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.

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.

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.

Thanks very much for your feedback!

[mickle666]

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.

[Sponsored content]