Solved

RAM versus CPU on SQL 2000 box

Posted on 2004-08-09
8
632 Views
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
or
- dual processor Enterprise Edition with 8Gig?

John

0
Comment
Question by:JXCovert
8 Comments
 
LVL 18

Expert Comment

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

0
 
LVL 18

Expert Comment

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

Assisted Solution

by:FDzjuba
FDzjuba earned 100 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
0
 
LVL 34

Accepted Solution

by:
arbert earned 100 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...
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 10

Assisted Solution

by:AustinSeven
AustinSeven earned 300 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.

AustinSeven
0
 

Author Comment

by:JXCovert
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.



0
 
LVL 34

Expert Comment

by:arbert
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.

0
 
LVL 18

Expert Comment

by:SjoerdVerweij
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:
http://www.anandtech.com/IT/showdoc.aspx?i=1982
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now