using one data file vs using multiple data files

Question

using one data file vs using multiple data files

john p

SSC Eights!

Points: 873
More actions
March 2, 2004 at 2:27 pm

#65998

Hello,
I just recently took over administration of a 38GB database. This database is made up of only 1 data file and 1 log file and resides on a RAID 5 disk array. After speaking with some members of the development team it seems that the database can be expected to grow around 5GB in a year from now.
The logic so far has been that since we are on an NTFS file system with a RAID 5 disk array… I/O is spread across the physical disks naturally and there is no need to break up the data files for performance gain. Is this logic correct?
Is it good practice to have a database this large(that continues to grow) that only uses one .mdf file? Can using one data file lead to performance issues(or any other issues for that matter)?
I do not have much experience with dbs over 20GB and would appreciate any advice!
Thanks!
John

Viewing 5 posts - 1 through 4 (of 4 total)

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

You have the log and data files on the same RAID5 array? I would recommend getting the logs off to another array (RAID1) first and foremost, that's a huge performance gain right there, the other thing that you could do would be to move off the Indexes.

If you have no option but to stay on the RAID5 single array I would still break the table down and put the indexes on a seperate filegroup and even look at using multiple filegroups for the data.

Shamless self promotion - read my blog http://sirsql.net

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 2

I'm not a big fan of multiple file groups from an admin perspective (restores, etc), but they can offer a performance benefit IF YOU NEED one. Separate threads will open for each file and this can be helpful, but if you are not having perfromance issues, I wouldn't change this.

I would try to move logs to a separate physical raid device.

chrisleonard SSCertifiable Points: 5030 More actions · Answer 3

I agree with Steve and stacenic, but would add that if your database gets so big that you can't get your backups done inside your target timeframe, then multiple filegroups can give you one administrative advantage - allowing you to backup one filegroup at a time. That can be a big help for really large databases, since you no longer have to backup the whole database each night / weekend / whatever.

But to re-emphasize my agreement with Steve and stacenic, get those logs off of RAID 5 - log IO is one of the few synchronous waits in SQL Server (in many disk configurations, anyway), and you don't want to calculate parity everytime the log writes. And yes, as Steve says, you get additional handles or threads for each file, but if your files are contending for the same physical resources, this might not wind up helping a lot, I think.

Cheers,

Chris

john p SSC Eights! Points: 873 More actions · Answer 4

My logs do reside on the same array as my data files. I havn't heard of any performance issues yet...but I will start to plan to move them to a seperate array just to be safe.

Thank you all for your help!

John