Slashdot Mirror
How Many CPUs for Microsoft's SQL Server?
adrian asks: "I've been wrestling with this problem some time now. I'm looking to buy a new machine to act as a SQL server. Unfortunately, we have to use M$ SQL Server 2000 and the per CPU unlimited licensing is very expensive. My question is this: Is there a benefit to running 4 slower CPUs as opposed to running 2 faster CPUs on a MS SQL box? I've found some people seem to think having more processors is better for SQL server... But, getting only two CPUs is certainly cheaper for licensing. Will performance suffer even if the two CPUs are faster? I've searched high and low using google and have yet to find any good hard numbers or benchmarks. Take these machines as an example: A quad PIII Xeon 550Mhz/512k cache box versus a dual P4 Xeon 2Ghz/512k cache box. The P4 machine would be more expensive, but we would save about $10,000 on licensing. And I know a 2Ghz P4 wouldn't be as fast as a 2Ghz PIII (if it existed) but yet I still want to think the dual P4 rig would be faster. The machines I am looking at are both IBM boxes with the same RAID and disk configs, 4 gigs of RAM, etc. Maybe some Slashdot. readers, who have experience with similar situations, could shed some light on this topic?"
There's one obvious benefit (depending on your architecture/manufacturer) with running 4 instead of 2: if one blows out, you aren't fried.
It's all going according to
I thought the benefit of shelling out the big bucks for Microsoft software was that this sort of thing was supposed to be easy to figure out.
Yet Another Web Site
Compaq, dell, maybe even IBM have little apps known as Sizing Tools. You download a tool specific to the type of server you want to install (SQL Server, Exchange Server, BizTalk...etc). And then you give it some key information, and it makes recommendations (and nicely enough, some make parts lists).
Here's some of dells:
Dell Sizing Tools
Compaq's i think you need to do a freee registration to get through.
Because the performance is tied to the system as a whole (CPU, Memory, drives/raid) the only usefully information your going to get that you can use to compare apples to oranges is the TPC numbers for a specific configuration.
In terms of which is better (1 big CPU vs 2 med CPUs), it COMPLETLY depends on your application (which you've told us nothing about). SQL Server is obviously very good at utilizing multiple CPUs. This isn't like trying to get a Quake Server to show some benefiti off 2/4 CPUs. I think it fair to say SQL Server is optimized for more than one CPU.
However, you results still depend on what you do with SQL Server. If you have a lot of long running queries yet have a high degree of concurrency then you will see benefit from multiple CPUs. If you have long complex queries that do a lot of processor intensive stuff (check the query plan for your biggest queries) and they have concurrency issues (locking key tables, affinity for the same group of rows...etc) then a very powerfull processor that can get through the bottle neck quicker may be better for you.
Also, as someone else mentioned there is some 'redundancy' with 2 cpus. Although, this benefit isn't as clean as say, having an extra power supply. If a CPU goes, there's a good chance the box is going down. You can most likely disable that CPU on reboot (or hopefully the BIOS does it for you) but still, you're down until then.
-malakai
-Malakai
A Dragon Lives in my Garage
>get a sales rep, ask for them to get you benchmarks
>for SQL server on each of these boxes. They should
>have numbers for you. If not, tell them that if
>they can't get you these numbers, you are going to
>have to try Dell, HP-Compaq, etc...
Alternatively, ask the IBM sales rep to let you borrow an evaluation unit for a week or two. Thrash on it, see if the config meets your needs.
Abuse the salescritters, that's what they're for.
The reason IBM servers are more expensive then buying a rackmount case and a bunch of parts is that IBM "adds value". Make 'em earn their extra $$$, and get some of that "added value" for yourself.
A second piece of advice -- discount the application developers hardware requirements heavily. When specing equipment, most application groups pad numbers throughout 10-15%. When the final requirements are forwarded up, the developer's manager inflate those inflated numbers by 20-200%.
Oh, there's some brilliant advice for a successful project. Sigh. Yes, the developers tend to overstate hardware requirements. They do this because the DB vendor understated them and the clients change (increase) the application requirements, and guess who gets blamed when it's too slow?
The rest of the duffbeer703's advice is reasonable, but ignore this (or at least be very careful about it.) It's a hell of lot easier to buy some expansion capability now than to try to upgrade later, or spend 100s of hours tweaking the DB/apps to make them 'faster'.
Oh, and in general, buying enough RAM to hold the (used part) of the DB in memory will get you a lot more perfomance than a faster/additional CPU. But it really depends on the workload. As everyone else notes.
The 'rule of thumb' for disk partitions for databases is thus: a mirrored set for the OS and apps, a mirrored set of the fastest bloody disks you can find for the transaction logs, and a raid 5, or if you can afford it, 0+1, for the actual database files.
Note that this also applies to Exchange, which is a database too.
Vintage computer games and RPG books available. Email me if you're interested.