Are more CPUs better than fewer CPUs for the best performance from SQL Server?

Question

All other things being equal, which of the following CPU options is the better choice for heavy-duty queries in SQL Server 2000:

--A single Xeon CPU running at 2.4 GHz (with 512KB L2 cache)
--Quad Xeon running at 550 MHz (with 1MB L2 cache)

Also assume that the server would have 1GB of RAM.

Answer

Assuming that your server is running heavy-duty queries that can take advantage of SQL Server's ability to use query parallelism, I would go for the quad Xeon. Multiple processors allow your queries (and some other SQL Server tasks) to run in parallel, and the larger L2 cache in the quad Xeon CPU example you gave also offers performance gains.

But the answer to your question may not be this clear cut. If your server is very heavily loaded, running heavy-duty queries most of the time, then the quad Xeon is the better choice. But if your server is not very heavily loaded, or if not too many of the queries can run parallel, then the faster, single CPU, may actually be faster.

If you are confident that your assumptions are correct, let that be your guide. But if you aren't confident in your assumptions, then you may (if possible) try to run some tests to see how your particular application fares under both configurations.

From another perspective, keep in mind that the most common bottlenecks that SQL Server runs into are memory and I/O, not CPU. For example, if cost is a big issue, you might want to get the single, fast Xeon CPU with 4 GB of RAM instead of a quad Xeon server with only 1 GB of RAM. Or, you might consider getting a server with room for quad CPUs, but only add one or two now, and only add the others if you find that your server has a CPU bottleneck.

Also, you may want to consider the Intel's Duo Core CPUs. They give you the full benefit of two CPUs for only one slot and a single SQL Server CPU licensing fee.