Filegroups, CPU, & RAID

Not sure if this is the right forum...
I have a table that has its own filegroup on a dedicated RAID 10 array.
Somewhere in Books Online I read that you should create a file in the
filegroup for each CPU in the server. My understanding is that in SQL 2000,
this triggered the server to spawn multiple threads to handle the I/O. My
questions are:
1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
2 - If not, being on a RAID 10 array, does it help in any way to have
multiple files in a group?
Thanks,
-- Brian"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
>
It doesn't matter. You don't want multiple threads for IO hitting a single
volume. One thread is quite enough to saturate your IO channel. A second
thread issuing IO requests over the channel isn't going to improve
performance.

> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
>
Multiple files per filegroup and RAID striping both accomplish the same
thing, and there is no need to use both. Both are mechanisms to spread IO
operations targeting a single table or index over a number of spindles.
David|||The "filegroup/file per CPU" rule went out with SQL 7.0. SQL 2000 will spin
up more IO threads as necessary until the controller is saturated. Multiple
files may make it easier to manage moving files due to later growth but will
give zero performance increase if the files are on the same underlying RAID
container. I personally prefer to keep files sizes doen to 100GB or smaller
on all but the largest systems.
There are some "File/CPU" considerations for tempdb for both SQL 2000 and
SQL 2005, but those apply on only the highest volume systems.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
> Thanks,
> -- Brian
>