Filegroups and multi-processor threading improvements

I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
parallel I/O is a mute issue if you setup a proper disk sub-system, at least
for small to medium sized databases.
But, it seems like everyone is skimming over the SMP benefits of filegroups,
if they exist. I came across one other posting referring to the number of
threads that SQL 2000 will use per filegroup.
Let me see if I understand this correctly? SQL 2000 will issue one thread
per filegroup for reads and writes. Thus if you have one large database on
a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
should thread across both CPU's at the RAID controller level. If you create
two PRIMARY filegroups for the database, then you SQL 2000 will dish out two
threads for disk I/O thus using two of your CPU's. These will both be
issued to the RAID controller which should thread out your disk I/O once
again. So, if my understanding is correct, will there be a performance gain
by creating one filegroup per CPU?
Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
RAID 0 for logs, but this is a read-only SQL server so logs are irrelevant.
This also limits us from doing any filegroup optimizations such as placing
the indexes on another disk sub-system. So the only performance benefit we
are looking at is potential better SMP handling of disk I/O.
Thank You,
Kevin HammondHi Kevin,
I'm afraid your understanding is completely incorrect.
You do not have to have more files to get better IO performance, except when
using backup/restore. Multiple files are generally used for easier
management.
The access methods code is totally oblivious to the number of files in all
respects. Likewise, lazy-writing and checkpointing are both indifferent to
the number of files. SQL Server can have hundreds of outstanding read and/or
writes per file, not just one. More file does not equal more IO.
Please let me know if you have any further questions.
Thanks and regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c8ak86$6m9$1@.grandcanyon.binc.net...
> I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
> parallel I/O is a mute issue if you setup a proper disk sub-system, at
least
> for small to medium sized databases.
> But, it seems like everyone is skimming over the SMP benefits of
filegroups,
> if they exist. I came across one other posting referring to the number of
> threads that SQL 2000 will use per filegroup.
> Let me see if I understand this correctly? SQL 2000 will issue one thread
> per filegroup for reads and writes. Thus if you have one large database
on
> a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
> should thread across both CPU's at the RAID controller level. If you
create
> two PRIMARY filegroups for the database, then you SQL 2000 will dish out
two
> threads for disk I/O thus using two of your CPU's. These will both be
> issued to the RAID controller which should thread out your disk I/O once
> again. So, if my understanding is correct, will there be a performance
gain
> by creating one filegroup per CPU?
> Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
> our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
> RAID 0 for logs, but this is a read-only SQL server so logs are
irrelevant.
> This also limits us from doing any filegroup optimizations such as placing
> the indexes on another disk sub-system. So the only performance benefit
we
> are looking at is potential better SMP handling of disk I/O.
> Thank You,
> Kevin Hammond
>