We are considering moving from a single 2-way system sql
server to a multi-processor cluster. However, we can't
seem to get an answer from anyone on what will provide
better performance: a cluster with two 4-way nodes with
16gb of memory and access to a SAN or four 2-way nodes
with the same total amount of memory and access to a SAN.
The assumption is that all processors will be Xeon MP 2.8
or 3.0.

Has anyone seen any comparisons or knows of any data
providing guidance on scaling SQL Server on this
particular issue?

Corrupter,

In a fail-over cluster, SQL Server is only running on one machine at a time.
Since it is only running on one machine at any given time, running on a
4-proc machine will give better performance than running on a machine with 2
procs.

--
Paul Wehland
www.SQLArch.com


"Corrupter" <anonymous (AT) discussions (DOT) microsoft.com> wrote

This depends on your SQL Instance and application architecture. If you are running more than 2 SQL Instances with high load on each then you may go with a 4-node cluster and run an active/active/active/active configuration which will give you a higher level of availability (more than one node can fail and you are still in business, howbeit with higher response times). Be careful about comparing model x with 2 processors with model y with 4 processors. Also, say you have 16GB of RAM for one application on on 4-CPU node it will allow you to use AWE and improve performance on a "busy" node. This is of almost no benefit when running a 2-CPU node with say 4GB as you're pretty much stuck with the 2.7GB limit per SQL Instance (assuming you have /PAE /3G switches enabled)

I'd [almost] always go for the 4P 16GB 2-node config over the 2P 4/8GB 4-node config. But it really depend on how many high-load SQL Instances you have and what level of server redundancy you need. If you are considering 2P nodes you probably have relatively light SQL load - which may also sway your decision.

Yes and no. With 4 proc machines, you have 2 more processors to use than
with 2 proc machines. BUT, it depends upon your application whether you
will get better performance or throughput. If you have massive DSS queries
running and parallelism turned on full blast, the 4 proc machine will
probably only get you marginally better performance since the DSS queries
will monopolize the processors. If you are running an OLTP style
application, you will be much better throughput on the 4 proc machine than
you will on the 2 proc machine.

--

Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com