Dual Xeon or Dual P4 for SQL Server?
John Dalberg
I am planning to build a server to be used as a SQL Server and web server.
Right now I can only use a single box for both.
I have read some threads were dual processors are having problems with some
parallel queries and the suggestions of having sql server use a single CPU.
My budget is limited so I am debating whether to get 2.6G dual xeon 533FSB
or dual P4 800FSB (DRR@ ram) or stick with a speedy single cpu.
If I get a dual cpu motherboard, is it a good idea to have 1 cpu used for
sql server and the other for everything else?
John Dalberg
John Dalberg
I forgot to mention also what about AMD's Athlon since I can get faster
Athlons for the same amount of money for Intel CPUs? Are dual Athlon
motherboards a good choice for SQL Server?
John Dalberg
Erland Sommarskog
Machine configurations is not my best game, but if you are going to have
both SQL Server and web server on the same box, I would definitely go for
two CPUs. (Or at least one CPU that is hyper-threaded.)
As for SQL Server and parallel queries, yes, there is a potential problem.
I have seen more than once, SQL Server being too optimistic about parallel
query plans, and taken a plan with poor performance. What we usually do,
when we run into these queries is to add the query hint OPTION (MAXDOP 1)
which turns of parallellism for that query.
A more definitive cure is to set the configuration option "Max degress of
parllellism" to 1. Note that this is not the same as confining SQL Server
to one CPU only, but that one single query can only take one processor. Two
users executing different queries can still get one CPU each.
Giving SQL Server only one CPU *may* be a good idea, if you expect the
web server and the rest to need substantial amount of resources. But you
could also get the effect that one processor is mainly idle, while the
other is working around the clock.
So my recommendation would be set "Max degrees of Parallelism" to 1 (Or
to 2, if you have two hyper-threaded CPUs), but let SQL Server use both
CPUs.
Finally, there may be a licensing issue. I vaguely recall that for some
editions that you pay a license per processor, but please check this with
Microsoft. Licensing is definitely not my best game.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
david_...@yahoo.com
Erland Sommarskog wrote:
> John Dalberg (joh...@hotmail.com) writes:
> > I am planning to build a server to be used as a SQL Server and web
server.
> > Right now I can only use a single box for both.