Filegroups and multi-processor threading improvements

From: Kevin Hammond (kghammond_at_nrscorp.com)
Date: 05/17/04 

Date: Mon, 17 May 2004 10:03:55 -0500

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

Relevant Pages

  • Re: file system (UFS2) consistancy after -current crash? (fwd)
    ... The problem that we have been having with many of the RAID ... systems is that they give an I/O completion interrupt after ... they copy the change into their memeory, but before the I/O ... disk, it proceeds to the next step. ...
    (freebsd-current)
  • Re: SCSI vs S-ATA
    ... > How does SATA perform relative to SCSI in a multi-user environment? ... SCSI HD may queue up a number of disk I/O requests onboard and choose the ... The host CPU issue is relevant for RAID. ...
    (microsoft.public.windows.server.sbs)
  • Re: How analyze the system bottleneck using shell tools
    ... and your disk I/O would drop. ... perhaps your system is I/O limited. ... You mean the benchmark tool can give the ... A RAID is a Redundant Array of Inexpensive Disks. ...
    (comp.unix.shell)
  • Re: Suggestions for managing large tables
    ... A table can only reside in a single filegroup but a filegroup can ... RAID array to your server, creating a logical volume on that array and ... logical volume should work fine (if an increase in disk available to ... Also, while RAID 10 is pretty quick in terms of write performance, it's ...
    (microsoft.public.sqlserver.server)
  • Re: disk failure
    ... if i run a wrkdsksts all 8 drives show activity. ... i/o. ... so it seems that a disk has failed yet all 8 show activity. ... raid protection stopped too, so the raid status shows that 5 disk are running ...
    (comp.sys.ibm.as400.misc)
Quantcast