New Database Server Advice

We are looking to upgrade our database server to gain additional speed.
I've come up with an idea of what we should get but wanted to get advice
from some experts to make sure my assumptions are accurate. Basically I
don't want to spend a bunch of my boss's money and not see any results. :-)
We are using MS SQL 2K on Window Server 2003
Our current server is a Zeon 3Ghz with 4G RAM
We have two main databases each about 25Gigs
The OS runs on one partition and the entire database runs on a single
partition (part of a Serial ATA RAID)
From my research mostly using perfmon, the bottle neck is always the disk,
we commonly have items queued and when the queue increases performance is
noticeably down. CPU speed has never been an issue. Therefore in a new
server I believe we should:
1. Use the maximum amount of RAM the server supports
2. Partition the databases by putting larger more active tables on they're
own physical drive as well as a sperate drive for SQL logs.
If this seems reasonable, I have a couple questions:
1. Is there a large performance difference between a SCSI RAID and Serial
ATA raid?
2. To evaluate disk systems should I look at drive RPM's, seek time, and
transfer rates? Am I missing something with this?
3. In addition to using separate drives when partitioning the database,
should I try to have separate interfaces to the motherboard. i.e. would it
make more sense to have two SCSI interfaces each with two drives instead of
one SCSI interface with four drives (from a performance standpoint) (Does
this logic apply to SATA systems?)
As a side note: I use the profiler tool with the SQLProfilerTuning Template,
then the index tuning wizard with default settings to generate our indexes.
If there is more I should be doing here, please let me know.
Any advice on any part of this is appreciated!1) Performace of disks has many factors, including the type of PCI bus the
card sits on, drive rpm, drive cache, I/O card cache, etc.
2) See 1 above. Get the maximum I/O card cache the card can have.
3) More interfaces is better up to the point where they exist on the same
PCI bus and saturate it.
4) More spindles is almost always better.
5) Go for at least 2 cores, preferably 4 or more. Do NOT enable
hyperthreading if you get CPUs that have it.
6) Avoid RAID 5 if possible for anything that has high writes, which always
includes tempdb and all transaction logs.
7) I avoid ITW. Have seen too many bad things come out of it.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Bishop" <nospam@.nospam.com> wrote in message
news:Ogk%235o1NIHA.3516@.TK2MSFTNGP02.phx.gbl...
> We are looking to upgrade our database server to gain additional speed.
> I've come up with an idea of what we should get but wanted to get advice
> from some experts to make sure my assumptions are accurate. Basically I
> don't want to spend a bunch of my boss's money and not see any results.
> :-)
> We are using MS SQL 2K on Window Server 2003
> Our current server is a Zeon 3Ghz with 4G RAM
> We have two main databases each about 25Gigs
> The OS runs on one partition and the entire database runs on a single
> partition (part of a Serial ATA RAID)
> From my research mostly using perfmon, the bottle neck is always the disk,
> we commonly have items queued and when the queue increases performance is
> noticeably down. CPU speed has never been an issue. Therefore in a new
> server I believe we should:
> 1. Use the maximum amount of RAM the server supports
> 2. Partition the databases by putting larger more active tables on they're
> own physical drive as well as a sperate drive for SQL logs.
> If this seems reasonable, I have a couple questions:
> 1. Is there a large performance difference between a SCSI RAID and Serial
> ATA raid?
> 2. To evaluate disk systems should I look at drive RPM's, seek time, and
> transfer rates? Am I missing something with this?
> 3. In addition to using separate drives when partitioning the database,
> should I try to have separate interfaces to the motherboard. i.e. would
> it make more sense to have two SCSI interfaces each with two drives
> instead of one SCSI interface with four drives (from a performance
> standpoint) (Does this logic apply to SATA systems?)
> As a side note: I use the profiler tool with the SQLProfilerTuning
> Template, then the index tuning wizard with default settings to generate
> our indexes. If there is more I should be doing here, please let me know.
> Any advice on any part of this is appreciated!
>|||Thank You, this will give me plenty to look into!
Any thoughts on if ECC memory is really worth the money. I have several
servers that don't use ECC memory that get hit hard and never have problems?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13ldpj8nb3i5nf0@.corp.supernews.com...
> 1) Performace of disks has many factors, including the type of PCI bus the
> card sits on, drive rpm, drive cache, I/O card cache, etc.
> 2) See 1 above. Get the maximum I/O card cache the card can have.
> 3) More interfaces is better up to the point where they exist on the same
> PCI bus and saturate it.
> 4) More spindles is almost always better.
> 5) Go for at least 2 cores, preferably 4 or more. Do NOT enable
> hyperthreading if you get CPUs that have it.
> 6) Avoid RAID 5 if possible for anything that has high writes, which
> always includes tempdb and all transaction logs.
> 7) I avoid ITW. Have seen too many bad things come out of it.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Bishop" <nospam@.nospam.com> wrote in message
> news:Ogk%235o1NIHA.3516@.TK2MSFTNGP02.phx.gbl...
>|||I think ECC memory is more about keeping your data integrity. If you
consider that important it is indeed worth the money. :-)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Bishop" <nospam@.nospam.com> wrote in message
news:uWbQ%23H4NIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Thank You, this will give me plenty to look into!
> Any thoughts on if ECC memory is really worth the money. I have several
> servers that don't use ECC memory that get hit hard and never have
> problems?
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13ldpj8nb3i5nf0@.corp.supernews.com...
>