Filegroups and MSDE

Question

Filegroups and MSDE

karenlopez

SSC Veteran

Points: 232
More actions
December 12, 2003 at 1:00 pm

#65357

Can someone explain the benefits of splitting my MSDE database across two filegroups? Our server has only one drive and since it is MSDE, it small, only half a gig.
All the reading I've done indicates that the benefits are for multiple disks and for selective backups. Since we have such a small DB, we have no plans for selective backups.
Are there any other benefits?
Are there costs for doing this on one drive with a small DB (other than maintaining the spilts when changes happen)?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mngong SSCertifiable Points: 6167 More actions · Answer 1

Probably split io activity..Not sure what benefit this may provide with the size of your database especially if not too much is going on

Mike

fhanlon SSChampion Points: 13131 More actions · Answer 2

Hi Karen. I can't see the benefit if you only have one drive. There is more of a benefit putting index, data and logs on different drives. But if your server only uses one drive, one filegroup is easier to maintain especially if you aren't going to make use of selective backups. I can think of any other performance costs.

Francis

karenlopez SSC Veteran Points: 232 More actions · Answer 3

quote:
Probably split io activity..Not sure what benefit this may provide with the size of your database especially if not too much is going on
Mike

Hmmmm... how does this split I/O activity if it is one disk?

Also, for everyone, is putting parent and child tables on the different filegroups really a benefit? Or should it be based on some other pattern?

We on the architecture team are trying to figure out this recommendation from a team member...he can't explain it; he only cuts and pastes the MSDN article on filegroups that talks about different disks.

So the more opinions we get from all you experts, the better.

Thanks,

Karen

jxflagg Hall of Fame Points: 3817 More actions · Answer 4

Another consideration is that the more files you use, the more threads SQL Server uses to peform reads of the files. Thus if you have very large tables, you can seperate them into different files and have more threads to perform sequential reads. Of course there is a point of diminishing return.

karenlopez SSC Veteran Points: 232 More actions · Answer 5

quote:
Another consideration is that the more files you use, the more threads SQL Server uses to peform reads of the files. Thus if you have very large tables, you can seperate them into different files and have more threads to perform sequential reads. Of course there is a point of diminishing return.

Ok, let's talk how large is large. overall, our entire database will never exceed 2 GB due to the MSDE restriction. I'll just look to see what our largest table is...just over 230,000 rows and will have almost no additions (its fairly static and is basically a lookup table.) Our biggest transactional table is 113,000, but will only hold maybe 200,000 at a time.

I'm used to tables with millions of rows; I'm not sure I consider these large tables.

jxflagg Hall of Fame Points: 3817 More actions · Answer 6

I don't consider that large either. I only made the mention of threads to partially answer your question about io. It's purely academic.

Frankly, I wouldn't consider splitting it into filegroups. Even if you saw performance problems, on a database of this size you would probably be better of reviewing your clustering and indexing strategy.