Filegroups

View previous topic View next topic Go down

Filegroups

Post  anna.rwfh on Thu Jun 06, 2013 3:27 pm

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?

anna.rwfh

Posts : 6
Join date : 2013-05-15

View user profile

Back to top Go down

Re: Filegroups

Post  ngalemmo on Thu Jun 06, 2013 7:42 pm

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?

avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Filegroups

Post  anna.rwfh on Fri Jun 07, 2013 7:01 pm

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.
*Sigh* That's what I was afraid of.

ngalemmo wrote:As far as dimensions go, don't you need to update them on a regular basis?
Once a week at most.

anna.rwfh

Posts : 6
Join date : 2013-05-15

View user profile

Back to top Go down

Re: Filegroups

Post  ngalemmo on Fri Jun 07, 2013 7:11 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Filegroups

Post  anna.rwfh on Tue Jun 11, 2013 6:47 pm

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 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: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!

anna.rwfh

Posts : 6
Join date : 2013-05-15

View user profile

Back to top Go down

Re: Filegroups

Post  mickle666 on Tue Mar 17, 2015 7:25 am

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

View user profile

Back to top Go down

Re: Filegroups

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum