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
or
- dual processor Enterprise Edition with 8Gig?
John
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
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?
- How fast does it need to be?
- How big are the main databases?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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:
http://www.anandtech.com/IT/showdoc.aspx?i=1982
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