• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 646
  • Last Modified:

RAM versus CPU on SQL 2000 box

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?


3 Solutions
It entirely depends on what you use it for.

- Small transactions, large reports or both?
- How fast does it need to be?
- How big are the main databases?
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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...
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.

JXCovertAuthor Commented:
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.

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.

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now