November 13, 2002 at 5:52 pm
You learn something new everyday, but this one shocked me. Here is what I am refering to :
"One of the reasons for having multiple files in a filegroup is so that you have a form of SQL Server based RAID(0). The data will be spread out evenly among all the files in a filegroup. "
Is that true?
November 14, 2002 at 1:43 am
Raid 0 does not mean mirrored. Raid 0 is striping (Raid 1 is mirroring).
The Books online say the following:
<quote>Filegroups allow files to be grouped together for administrative and data allocation/placement purposes. For example, three files (Data1.ndf, Data2.ndf, and Data3.ndf) can be created on three disk drives, respectively, and assigned to the filegroup fgroup1. A table can then be created specifically on the filegroup fgroup1. Queries for data from the table will be spread across the three disks, thereby improving performance. The same performance improvement can be accomplished with a single file created on a RAID (redundant array of independent disks) stripe set. Files and filegroups, however, allow you to easily add new files on new disks.</quote>
So it is true.
Joachim.
November 14, 2002 at 2:47 am
Having mulitple files in a filegroup does not provide any redundancy. It allows the query engine to issue i/o request on each file in a query. Therefore the more files the better, I have not looked into this in depth but thats the theory.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 14, 2002 at 4:44 am
RAID 0 is not redundant in anyway, you lose part of the set the whoel thing is gone.
As for creating mulitple files for a single filegroup, unless you have IDE drives (no raid available other than mirroring at this time) then you get better performance for most databases on a single file in a filegroup on an array of SCSI drives in RAID 0. However, you usually want redundancy so most are configured RAID 5 or RAID 10. If however, you have a hgih transaction database there may be benniffits to doing this across multiple arrays. Usually thou, you only add a file to a filegroup to extend the database to another drive when the first fills up.
I thing of note software RAIDS are never as fast as hardware RAIDS but a SQL file filegroup may outperform a software based NT RAID array, but I have not tested. A software based RAID is where you have mutiple disks on a controller(s) that do not support hardware RAID and you use NTs ability to create a RAID from them.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply