64 bits version of SQL Server 2000

Post by Daniel P.
Is it a better price/performance than the 32 bits version?

Sorry, there are far too many variables here for anyone to give a realistic
yes/no answer.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Daniel P.

2003-12-05 15:57:47 UTC

Let me be more specific:
- it is used for running some stored procedures and calculate bills based on
some algorithm.
- it needs to process over 500,000,000 records from about 5-10 tables.
- the data will be imported/DTSed from files into the tables
- there will not be more than 2-3 users accesing the data
- the output of the process will be 1-2 tables with the result of the
calculation. These two tables will have about 1,000,000 recods together (or
each of them?)
- we need to be able to run the process in less than 1-2 hours

Unfortunately I do not have more than that. The project did not start yet so
we do not know all the details.

Post by Daniel P.
Is it a better price/performance than the 32 bits version?

Sorry, there are far too many variables here for anyone to give a realistic
yes/no answer.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Eric Sabine

2003-12-05 16:28:10 UTC

If your budget is good, you can buy the developer edition of 64 bit, $49 I
believe but you still need a 64 bit server (hence the budget comment). But
basically you would have to test 32 and 64 side by side. Not having seen
the "algorithm" though, this doesn't _seem_ to be somehting a 32 bit
multiprocessor box with good memory can't handle. Good table design, good
indexes, good statistics, and good queries go a LONG WAY.

hth
Eric

Post by Daniel P.
- it is used for running some stored procedures and calculate bills based on
some algorithm.
- it needs to process over 500,000,000 records from about 5-10 tables.
- the data will be imported/DTSed from files into the tables
- there will not be more than 2-3 users accesing the data
- the output of the process will be 1-2 tables with the result of the
calculation. These two tables will have about 1,000,000 recods together (or
each of them?)
- we need to be able to run the process in less than 1-2 hours
Unfortunately I do not have more than that. The project did not start yet so
we do not know all the details.

Post by Daniel P.
Is it a better price/performance than the 32 bits version?

Sorry, there are far too many variables here for anyone to give a

realistic

Post by Aaron Bertrand - MVP
yes/no answer.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Kevin

2003-12-05 16:42:58 UTC

a load and then summary of 500M rows is not something I would characterize
as something a commodity 32-bit server would be able to do in 1-2 hours, but
it does boil down to requirements and design.

Post by Eric Sabine
If your budget is good, you can buy the developer edition of 64 bit, $49 I
believe but you still need a 64 bit server (hence the budget comment).

But

Post by Eric Sabine
basically you would have to test 32 and 64 side by side. Not having seen
the "algorithm" though, this doesn't _seem_ to be somehting a 32 bit
multiprocessor box with good memory can't handle. Good table design, good
indexes, good statistics, and good queries go a LONG WAY.
hth
Eric

Post by Daniel P.
- it is used for running some stored procedures and calculate bills

based

Post by Daniel P.
some algorithm.
- it needs to process over 500,000,000 records from about 5-10 tables.
- the data will be imported/DTSed from files into the tables
- there will not be more than 2-3 users accesing the data
- the output of the process will be 1-2 tables with the result of the
calculation. These two tables will have about 1,000,000 recods together

(or

Post by Daniel P.
each of them?)
- we need to be able to run the process in less than 1-2 hours
Unfortunately I do not have more than that. The project did not start

yet

Post by Daniel P.
we do not know all the details.

Post by Daniel P.
Is it a better price/performance than the 32 bits version?

Sorry, there are far too many variables here for anyone to give a

realistic

Post by Aaron Bertrand - MVP
yes/no answer.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

John Bell

2003-12-05 17:05:15 UTC

Hi

I believe it would violate the eula if you use the developer edition for a
production system. You should check with your supplier what edition is
needed for your system.

John

Post by Eric Sabine
If your budget is good, you can buy the developer edition of 64 bit, $49 I
believe but you still need a 64 bit server (hence the budget comment).

But

Post by Daniel P.
- it is used for running some stored procedures and calculate bills

based

(or

Post by Daniel P.
each of them?)
- we need to be able to run the process in less than 1-2 hours
Unfortunately I do not have more than that. The project did not start

yet

Post by Daniel P.
we do not know all the details.

Post by Daniel P.
Is it a better price/performance than the 32 bits version?

Sorry, there are far too many variables here for anyone to give a

realistic

Post by Aaron Bertrand - MVP
yes/no answer.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Daniel P.

2003-12-05 17:13:52 UTC

I do not want to violate any laws so I need a fully licensed-for-production
system.

Post by John Bell
Hi
I believe it would violate the eula if you use the developer edition for a
production system. You should check with your supplier what edition is
needed for your system.
John

Post by Eric Sabine
If your budget is good, you can buy the developer edition of 64 bit, $49 I
believe but you still need a 64 bit server (hence the budget comment).

But

Post by Daniel P.
- it is used for running some stored procedures and calculate bills

based

(or

Post by Daniel P.
each of them?)
- we need to be able to run the process in less than 1-2 hours
Unfortunately I do not have more than that. The project did not start

yet

Post by Daniel P.
we do not know all the details.

Post by Daniel P.
Is it a better price/performance than the 32 bits version?

Sorry, there are far too many variables here for anyone to give a

realistic

Post by Aaron Bertrand - MVP
yes/no answer.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Eric Sabine

2003-12-05 17:25:21 UTC

John, I think it's pretty clear I was telling him to test the 32 against the
64. There is no evaluation version of the 64 bit, you can only get it as
the developer's edition or the full retail.

Post by John Bell
Hi
I believe it would violate the eula if you use the developer edition for a
production system. You should check with your supplier what edition is
needed for your system.
John

Post by Eric Sabine
If your budget is good, you can buy the developer edition of 64 bit, $49 I
believe but you still need a 64 bit server (hence the budget comment).

But

Post by Daniel P.
- it is used for running some stored procedures and calculate bills

based

(or

Post by Daniel P.
each of them?)
- we need to be able to run the process in less than 1-2 hours
Unfortunately I do not have more than that. The project did not start

yet

Post by Daniel P.
we do not know all the details.

Post by Daniel P.
Is it a better price/performance than the 32 bits version?

Sorry, there are far too many variables here for anyone to give a

realistic

Post by Aaron Bertrand - MVP
yes/no answer.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Aaron Bertrand - MVP

2003-12-05 17:59:04 UTC

Post by John Bell
I believe it would violate the eula if you use the developer edition for a
production system.

I think he meant to kick the tires and do some benchmarking, not to save
money in production.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

joe chang

2003-12-05 19:14:53 UTC

take a look at the execution plan for your query,
you do not need the full data size, but you should have
more the 100k rows
if the plan shows a hash join or hash match,
then i would highly recommend the 64-bit system,
the hash operator requires intermediate results to be
stored, a 32-bit system can only allocate so much address
space for the working set, after which the hash
performance will take a serious hit
64-bit systems should not be constrained on the ability to
use memory for intermediate results

-----Original Message-----
- it is used for running some stored procedures and

calculate bills based on

some algorithm.
- it needs to process over 500,000,000 records from about

5-10 tables.

- the data will be imported/DTSed from files into the

tables

- there will not be more than 2-3 users accesing the data
- the output of the process will be 1-2 tables with the

result of the

calculation. These two tables will have about 1,000,000

recods together (or

each of them?)
- we need to be able to run the process in less than 1-2

hours

Unfortunately I do not have more than that. The project

did not start yet so

we do not know all the details.

Post by Daniel P.
Is it a better price/performance than the 32 bits

version?

Post by Aaron Bertrand - MVP
Sorry, there are far too many variables here for anyone

to give a

realistic

Post by Aaron Bertrand - MVP
yes/no answer.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Daniel P.

2003-12-05 16:52:14 UTC

Thanks everybody for help!

Unfortunately the project is in the inception phase. I only have some pieces
of requirements but no design and of course no code.

I'm pretty good at writing good SQL code and database tuning.

I need to send to my managers a proposal for hardware and software so they
can make the budget for the next year.

in a equal # of CPU configuration, Itanium2 should give
better performance in most applications
I suspect that Xeon has better table scan performance, but
have not been able to verify this in a clean test
environment.
Itanium2 will also have better performance in high-end
systems (>4 CPUs)
also, look at your SQL Server memory utilization, if a
large chunk of the lower 2 (or 3) GB is being used for
other than buffer cache, you may benefit from the 64-bit
address space.
otherwise, if the vast majority of your memory usage is
buffer cache, then 32-bit with AWE works ok

-----Original Message-----
Did anyone use the 64 bit version in production with

large databases?

Is it a better price/performance than the 32 bits version?
Any information if highly appreciated.
Thanks a lot!
.

Eric Sabine

2003-12-05 16:59:22 UTC

this is interesting
" I suspect that Xeon has better table scan performance"
why do you say that?

-----Original Message-----
Did anyone use the 64 bit version in production with

large databases?

Is it a better price/performance than the 32 bits version?
Any information if highly appreciated.
Thanks a lot!
.

joe chang

2003-12-05 19:19:40 UTC

i know that there is atleast one operation that is much
slower on an Itanium2 system than on a Xeon MP, even
though overall the It2 was faster,
unfortunately, that was a production system, and i could
not get exclusive access to specifically pinpoint which
operation was performing very poorly relative to the Xeon

-----Original Message-----
this is interesting
" I suspect that Xeon has better table scan performance"
why do you say that?

in a equal # of CPU configuration, Itanium2 should give
better performance in most applications
I suspect that Xeon has better table scan performance,

but

have not been able to verify this in a clean test
environment.
Itanium2 will also have better performance in high-end
systems (>4 CPUs)
also, look at your SQL Server memory utilization, if a
large chunk of the lower 2 (or 3) GB is being used for
other than buffer cache, you may benefit from the 64-bit
address space.
otherwise, if the vast majority of your memory usage is
buffer cache, then 32-bit with AWE works ok

-----Original Message-----
Did anyone use the 64 bit version in production with

large databases?

Is it a better price/performance than the 32 bits

version?

Any information if highly appreciated.
Thanks a lot!
.

Daniel P.

2003-12-05 17:05:17 UTC

A quick look on TPC shows that 32 bit systems are better than 64 bits. Sould
I rely on their numbers?

http://www.tpc.org/tpch/results/tpch_price_perf_results.asp?resulttype=noncluster&version=2%

Post by Daniel P.
Did anyone use the 64 bit version in production with large databases?
Is it a better price/performance than the 32 bits version?
Any information if highly appreciated.
Thanks a lot!

Aaron Bertrand - MVP

2003-12-05 17:58:02 UTC

Post by Daniel P.
A quick look on TPC shows that 32 bit systems are better than 64 bits. Sould
I rely on their numbers?

http://www.tpc.org/tpch/results/tpch_price_perf_results.asp?resulttype=noncluster&version=2%

Remember that the TPC benchmarks are for very specific applications running
on very specific hardware. Your mileage certainly will vary unless you are
building something similar and are spending the same kind of money on
hardware...

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

joe chang

2003-12-05 19:25:39 UTC

i am not sure which numbers you were looking at,
the best 4P tpc-c for w2k3 + s2k:

it2 1.5G 121k
xeon mp 2.8G 90k
opteron 82k

at >4p, it gets better for It2 relative to Xeon
also, the tpc-c app uses almost all memory for buffer
cache, which works ok for AWE memory
but you will notice that 32-bit SQL Server has a tough
time in tpc-h, which probably better reflect your app

-----Original Message-----
A quick look on TPC shows that 32 bit systems are better

than 64 bits. Sould

I rely on their numbers?
http://www.tpc.org/tpch/results/tpch_price_perf_results.as

p?resulttype=noncluster&version=2%

Post by Daniel P.
Did anyone use the 64 bit version in production with

large databases?

Post by Daniel P.
Is it a better price/performance than the 32 bits

version?

Post by Daniel P.
Any information if highly appreciated.
Thanks a lot!

Kevin Brooks

2003-12-05 19:20:51 UTC

I guess I will chime in. We are running a 4 way 64bit Unysis ES7000 with
16GB of memory on Windows 2003. We are currently using it for an OLAP
implementation, however the RDBMS is on the server is well. The best
benefit is memory, which we will be bumping up into the 30gigish range soon.
You can dynamically adjust memory over 8GB(do not really need AWE) without
rebooting SQL. When we are loading data into the cubes we can give SQL more
and when aggs are being built in the cube give more to OLAP Services.
Beyond that memory ease, we have not really noticed a great difference. In
alot of aspects it is a pain using SQL 64 bit, due to its lack tools and
bugs we have ran into. We have crashed our 64bit many times with the amount
of data we are trying to punch through(billion +). To give you some hard
numbers from our test using it in an OLTP environment, we traced some daily
activity and we ran the delete, insert, updates for the same anount of time
roughly --

32bit 4 way 8GB memory -- 133 Trans/sec
64bit 4 way 8GB memory -- 613 Trans/sec
32bit 8way 8GB memory -- 493 Trans/sec

Daniel P.

2003-12-05 21:29:59 UTC

Finally the kind of answer I was expecting: someone who uses 64 bit every
day.

Thank you very much Kevin!!!

I think I will stick with 32 bit and a lot of memory. I've also notices that
the amount of memory makes a big difference.

BTW: some MS guys came to us about two weeks ago and they said that the OS
(Windows 2000 or Windows 2003) eats up by default 1/2 of the physical
memory. So even if you have 16 GB you only have 8 GB available for the apps
like SQL Server. The rest is reserved for the OS.

This is really strange!!!

They also said this will be changed in Longhorn.

Post by Kevin Brooks
I guess I will chime in. We are running a 4 way 64bit Unysis ES7000 with
16GB of memory on Windows 2003. We are currently using it for an OLAP
implementation, however the RDBMS is on the server is well. The best
benefit is memory, which we will be bumping up into the 30gigish range soon.
You can dynamically adjust memory over 8GB(do not really need AWE) without
rebooting SQL. When we are loading data into the cubes we can give SQL more
and when aggs are being built in the cube give more to OLAP Services.
Beyond that memory ease, we have not really noticed a great difference.

Post by Kevin Brooks
alot of aspects it is a pain using SQL 64 bit, due to its lack tools and
bugs we have ran into. We have crashed our 64bit many times with the amount
of data we are trying to punch through(billion +). To give you some hard
numbers from our test using it in an OLTP environment, we traced some daily
activity and we ran the delete, insert, updates for the same anount of time
roughly --
32bit 4 way 8GB memory -- 133 Trans/sec
64bit 4 way 8GB memory -- 613 Trans/sec
32bit 8way 8GB memory -- 493 Trans/sec

Kevin

2003-12-06 17:33:07 UTC

Windows does not use that much memory in a typical sql installation. If you
have a 16GB system and you allocate 14 to SQL, SQL will get 14 assuming no
other memory intensive application is running on there.

Post by Daniel P.
Finally the kind of answer I was expecting: someone who uses 64 bit every
day.
Thank you very much Kevin!!!
I think I will stick with 32 bit and a lot of memory. I've also notices that
the amount of memory makes a big difference.
BTW: some MS guys came to us about two weeks ago and they said that the OS
(Windows 2000 or Windows 2003) eats up by default 1/2 of the physical
memory. So even if you have 16 GB you only have 8 GB available for the apps
like SQL Server. The rest is reserved for the OS.
This is really strange!!!
They also said this will be changed in Longhorn.

soon.

Post by Kevin Brooks
You can dynamically adjust memory over 8GB(do not really need AWE) without
rebooting SQL. When we are loading data into the cubes we can give SQL

Post by Kevin Brooks
and when aggs are being built in the cube give more to OLAP Services.
Beyond that memory ease, we have not really noticed a great difference.

Post by Kevin Brooks
alot of aspects it is a pain using SQL 64 bit, due to its lack tools and
bugs we have ran into. We have crashed our 64bit many times with the

amount

Post by Kevin Brooks
of data we are trying to punch through(billion +). To give you some hard
numbers from our test using it in an OLTP environment, we traced some

daily

Post by Kevin Brooks
activity and we ran the delete, insert, updates for the same anount of

time

Post by Kevin Brooks
roughly --
32bit 4 way 8GB memory -- 133 Trans/sec
64bit 4 way 8GB memory -- 613 Trans/sec
32bit 8way 8GB memory -- 493 Trans/sec

Aaron Bertrand [MVP]

2003-12-06 19:56:33 UTC

Post by Daniel P.
BTW: some MS guys came to us about two weeks ago and they said that the OS
(Windows 2000 or Windows 2003) eats up by default 1/2 of the physical
memory. So even if you have 16 GB you only have 8 GB available for the apps
like SQL Server. The rest is reserved for the OS.

Up to 4 GB, this may be true (I think the max the system will reserve is 2
GB by default). If your system has more than 4 GB of RAM, the system will
not automatically keep consuming *half.* We have systems with > 4 GB and we
have no problem allocating most of it to SQL Server.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Kevin

2003-12-06 17:34:39 UTC

Kevin - when you say crash you mean crashing analysis services, right, not
the RDBMS?

Kevin Brooks

2003-12-09 00:13:34 UTC