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 Hammond
Hi 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
>