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]

On Sun, 21 Nov 2004 07:14:17 -0800, John Dalberg wrote:

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

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

[Leythos]

In article <u1*****************************@40tude.net>,
jo*****@hotmail.com says...

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, you can't use Dual P4's any more, Intel fixed that after the P3.

If you want to build a cheap but fast SQL Server, assuming SQL 2000 or
higher, tape a look at the ASUS PC-DL Deluxe motherboard. I have about
30 of these running the following:

ASUS PC-DL Deluxe
Dua Xeon 2.4G (or faster) (make sure they are same lot/part)
2GB or 4GB of RAM (does not require ECC RAM)
It has onboard SATA and IDE, you can use a total of 6 drives between the
connectors without a problem.

The 2GB systems with Dual 250GB SATA drives, video, case, CD-ROM, etc...
are under $1900 each.

If you want a big box, like this:

PC-DL Deluxe
Dual Xeon 2.8ghz
2 x 80GB IDE (OS = 12GB, LOGS = 60GB)
6 x 250GB IDE (for DATA files)
Promise SX6000 Hardware IDE RAID Controller (hot swap IDE drives)
cheap video
Chenbro Server case with Dual 550W PSU's
and Windows 2003 Standard Server 5cal

This box runs about $4000

I run SQL 2000 on Dual P3 and Dual/Quad Xeon without any problems.

If you get a P4, get one that does Hyper-Threading, and use Server 2003
so that you get the most benefit from it.

As for Athlon, faster is a matter of what task is being performed and
how stable you want the system to be. When it comes to business systems
I use Intel CPU's only, but that's a personal preference and has never
cost me a job or stability.
--
--
sp*********@rrohio.com
(Remove 999 to reply to me)

[Erland Sommarskog]

John Dalberg (jo*****@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.

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?

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Leythos]

In article <Xn**********************@127.0.0.1>, es****@sommarskog.se
says...

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.

I was told by the MS People in the Great Lakes MS Office that HT CPU's
still count as 1 CPU for licensing.

One other thing, unless he's using SBS, I was also told that a Web
Server accessing a MS SQL Database, requires a CPU License on the
Database server for each physical CPU the SQL server is permitted to
use.

I have always licensed our SQL Servers in CPU mode when being accessed
by a web server using anonymous connections to the web server.

--
--
sp*********@rrohio.com
(Remove 999 to reply to me)

[david_lynch74]

Erland Sommarskog wrote:

John Dalberg (jo*****@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.
bo*********@rbc.com
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?
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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp