Go Premium for a chance to win a PS4. Enter to Win


RAM versus CPU on SQL 2000 box

Posted on 2004-08-09
Medium Priority
Last Modified: 2009-07-29
We, like everyone, have limited budget for our SQL 7 -> SQL 2000 upgrade.

My problem is that we can buy Standard Edtion for a quad processor box for $20,000 or so.  But it can use only 2Gig RAM.

We'd have to pay almost $50,000 for Enterprise Edition for that same box.  We could then use more RAM.

What's more important to SQL Server 2000? Memory or CPUs?

Am I faster with:

- quad processor Standard Edition w/ 2Gig memory
- dual processor Enterprise Edition with 8Gig?


Question by:JXCovert
LVL 18

Expert Comment

ID: 11753769
It entirely depends on what you use it for.

LVL 18

Expert Comment

ID: 11753795
- Small transactions, large reports or both?
- How fast does it need to be?
- How big are the main databases?

Assisted Solution

FDzjuba earned 300 total points
ID: 11753887
memory is important when lots of selections and data manipulation is in place, CPU important ..... hmmm always! If you were to waste some money on hardware update then spend it on memory and then CPU

there is not much difference between Standard Edition and Enterprise Edition as such (speed wise) just more interesting tools that makes corporate work with database easier
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 34

Accepted Solution

arbert earned 300 total points
ID: 11753954
I agree with the above--it depends on your use.  Actually, without doing benchmarking and getting a before picture of your environment, it would be really tough to tell you which would be better.

Windows Addressing extensions in SQL Server can actually slow some things down.  On the flipside, the parallel plans with multiple CPUs can also slow things down.  So, you might find that some things actually slow down with more memory or more processors (the key being SOME things--not all).

Personally, I would recommend you use the Trial version and see before you plop all the cash out...
LVL 10

Assisted Solution

AustinSeven earned 900 total points
ID: 11754086
I think the answer depends on the nature of your database/app.   I don't think anyone could really give you an anwer to your question without knowing more about the nature of your SQL Server environment.   I deal with SQL Servers that demand more memory than cpu or the other way around.   If you look at the nature of your database before the upgrade, what does it seem to be bottlenecked on most?   Is the tendency toward a dynamic database subject to a lot of inserts, updates and deletes?   The answers will probably dictate where the money should go.  Also, in terms of capacity planning, where are the changes likely to occur?  More user connections?  More transactions per second?  Database size?  

You didn't mention I/O and I suspect you have more than 2 options to consider.   On higher end system, a good disk subsystem with separate mirrored disk volumes on a high-end disk controller would be just as important if not more important than CPU and RAM - although the whole thing needs to be a balanced package of course.  

Hope this helps.


Author Comment

ID: 11754491
We have one database where we update information.  That database is about 40Gig.  There are 12 tables used to store our transaction information, each of which now exceeds 2 million rows.  

We have seven other databases that we read but do not update. The largest is 10Gig with the key tables being between 1 million and two million rows.  One query seems to be the key bottleneck here - it joins three tables all of which exceed 1.5 Million rows.  During downturns in performance (at peak usage, we're performing that query about 2000 times an hour) we see query times go from 3 to 7 seconds.

We're getting two Dell 6600s which we will configure Active-Passive (if we get SQL 2000 Enterprise).  We believe we can manually swap to our second box if need be, so failover isn't extremely important. We're also getting a Powervault drive array with fourteen 73Gig drives; we plan to stripe our data across all of 'em and hang it off the back of both SQL boxes.

We expect to see our business grow by 4x in the next twenty-four months.  Probably that means going from:

120 users to 400 or so
Databases will quadruple in size
Transaction volume will go from 4,000/day to 16,000/day

Tidbit: this is ASP software currently running on NT4 / SQL 7 and NT4 / IIS 4.  It was written by Access programmers in 90 days.  Hence there are myriad contributors to our performance degradation including Crystal Reports 8 running on the IIS 4 box and our key tables having neither Identity fields nor foreign key relationships.  All of our joins are performed pretty much "brute force" style by joining on two string fields. So what we want to accomplish now is getting some hardware into place to improve performance **today** while we spend the next 12 months reworking our schema and code to eliminate other contributory weaknesses.  We fell like code changes can support at least 33% of the performance needs spurred by our coming growth.  

At the extremes are quad processors Enterprise SQL for $50,000 and dual processor Standard SQL for $10,000.  I lean toward dual 2.2Gig with 8Gig RAM running Enterprise Edition.

I appreciate the feedback and would of course welcome more thoughts as to what we should be considering.

LVL 34

Expert Comment

ID: 11754806
I tend to agree with you.  With the amount of data you're talking about, I think RAM will probably buy you more than the CPU will.  If your data presentation layer (IIS/ASP and crystal) is on another machine, it will be offloading a lot of processing.  Running RAID10 across your SAN will also make a big difference.

LVL 18

Expert Comment

ID: 11755123
brett's right. Go for the dual; however, try as hard as humanly possible to buy a machine with a motherboard that takes 4 processors so you can expand later (if/when needed) rather than having to throw the entire thing out.

Also, seriously consider Opterons (in your case, I would go for dual 846/848/850). They are not only faster than Xeons, they scale much better if you add 2 more processors:

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question