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?"

Comments

system requirements?

[tps12]

Doesn't it tell you on the back of the box?

Ask IBM

[forsetti]

You said you are looking at IBM boxes -- 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...
For $$$ like this (and the potential of future business), they should scurry, and get their R&D kicking real quick....

Re:Ask IBM

[Clover_Kicker]

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

More CPUs

[pci]

For SQL servers (Oracle, IBM, Microsoft) more CPU's are typically better.

I would suggest buying a machine that is capable of going to 4 processors and only installing 2 in it at first. That way you can save money on software licenses and give yourself room for growth. Something like the Dell 6650 would be perfect.

-Vince

Depends

[duffbeer703]

For RDBMSs in general:

The more concurrent connections you have, the more CPUs you want.

The more analytical work being done on the server (like a data warehouse) the faster cpu you want.

Basically, all things being equal, faster CPUs will make tasks run faster. (How much faster depends) More CPUs will make more happen at once.

In general, more CPUs of any reasonable speed are the better choice, which is why the database vendor charges you to use them.

Since you didn't give any detail whatsoever about what your application is doing, how many users, etc. How can you expect any kind of reasonable answer?

My advice to you would be to buy the faster, 2 processor server if you cannot afford a 4-cpu license. You can always add database licenses and cpus later, perhaps in a quarter when you have a larger budget.

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

Four

[bwt]

Q: How many CPUs does it take to run MS SQL Server

A: Four, one to hold the light bulb and three to turn the ladder.

Limitiation will likely be IO anyway

[Bravo_Two_Zero]

To be honest, unless you're working with cubes (and you aren't if you're installing the plain, vanilla MSSQL server), your limiting factor will be IO then network. You *probably* won't run into a CPU-bound issue. Go with the two CPUs and invest the rest in ram, disk controllers and disks.

And when I say invest in disks and controllers, I mean multiple raid controllers with multiple channels and several drives on each channel. MSSQL server gets supremely cranky of the IO backs up. Same goes for the network traffic, but the random reads/writes will cause more havoc before you flood your NIC.

Of course, if you can buy enough ram so all your reads and writes can happen in memory (maybe a ramdisk?), you'll be very happy. Hey, if you have to use an M$ product, MSSQL server is about the most solid product they sell.

Re:Limitiation will likely be IO anyway

[dthable]

This is what I've found with our new SQL Server 2000 box. It's currently running on a single 850 PIII, but we've added the 2GB (I believe) of RAM to the box and spent the money on the disk controllers. Even with our simulated load of 200 clients, we only see CPU utilization spikes every so often.

Re:Limitiation will likely be IO anyway

[walt-sjc]

Yeah, mod that up. HOWEVER, it depends on the application. Poorly written / designed SQL can REAllY pound the snot out of a database in terms of CPU. I ran into this at my last company. I worked with the DBA and programmers to redesign the app and we ended up reducing the CPU load by a factor of 50. We were murdering an 8 CPU Sun 5K, and after optimizations we ran on a couple 2 CPU E450 box saving us HUGE licensing dollars (2 boxes for redundancy.) Sometimes there is not much you can do to fix CPU usage of certain types of queries.

Memory and IO are still the biggies on most DB processing though.

Do you have performance metrics?

[fooguy]

Are you running this application in production now? How is the performance? What kind of performance do you need?

The answer is: it depends. I've seen off the shelf apps that don't use a single stored procedure and have one user for pass-thru authentication, they are always IO bound, never CPU bound. I've seen custom apps that have a billion stored procedures, replication, etc that are really CPU intensive and IO intensive.

Ask yourself:
-Are you using replication?
-Are you using the SQL Server security model?
-Are you using stored procedures?
-Is this a federated database?
-How many tables does it have?
-How many rows in the largest table?
-What kind of transaction volume does it have?
-To what degree does data change?
-What kind of reporting are you doing?

That's the kind of information you need to ask before you size the server. More stored procedures, more CPU intensive. More users whose access rights are checked against schema objects, more CPU intensive. Replication, duh. If you're loading a million rows a day and truncing a bunch of tables, you'll have more CPU overhead to maintain indexes and optimizer statistics. If you're letting users run Crystal Reports against your live database, you're raping the system but good.

Make sure you use RAID 1 or RAID 10, not RAID5. Parity overhead is the death of an RDBMS. Also, you have an assload of ram, but don't just let SQL Server use it, make sure you actually tune the database. You'd be floored at the kind of transaction volume we get our of at a moderately sized, well tuned (and well chosen) SQL Server, but alot depends on the app too. Shitty app, shitty performance.

Off the cuff, my personal opinion is that the threading in SQL Server isn't good enough to make quality use of 4 CPUs -- I'd get the two fast ones. Just remember: opinions are like assholes, everybody has one. You need to make the decision for yourself.

You're looking for a sizing tool...

[malakai]

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

Re:Redundancy

[fooguy]

What do you think this is, a Sun? How many Intel servers have you seen cook a CPU in an SMP system and keep running without crashing the machine?

Sure, the machine can run with an odd number of processors, but they still die. At least with our Suns you can hot plug the CPU board in and out.

Dear Slashdot,

[zulux]

I'm going to shoot myself in the foot - should I use four small bullets or two larger ones? The larger ones as cheeper per shot, but I've been told, if you're a lousy shot that you need the four small ones to make sure that you at least hit your target?

Any thoughts?

Re:Dear Slashdot,

[highcaffeine]

Try this version instead, it follows the incredible verbosity of the original question better:

I've been wrestling with this problem for some time now. I'm looking to shoot myself in the foot using some sort of firearm. I will probably use a pistol or revolver, as I believe that will be easier to handle as I assume I will be pointing directly downward (unless I decide to shoot myself in the foot while in bed). Unfortunately, the pain that I hear may result could prevent me from possibly being able to fire more than a couple rounds. My question is this: Is there a real benefit from using four smaller bullets as opposed to using two larger bullets when I shoot myself in the foot? I've found some people seem to think that a 9mm round will be more effective for this use... But, purchasing two .38 rounds appears to be cheaper than four 9mm rounds. Will the effectiveness of shooting myself in the foot suffer if the two .38 rounds are used, even if they are larger? I've searched high and low using google and have yet to find someone else who has collected any data on this matter. Take these firearm/ammunition combinations as an example: A revolver with two .38 rounds versus a pistol with four 9mm rounds. The revolver would be more expensive, because I am limiting myself to a model which is not used very frequently and is slightly more difficult to find readily available. I would also have to purchase a full box of ammunition even though I am only considering using two rounds (I don't know how to ask the clerks for only two rounds -- but that's another Ask Slashdot), thus inflating the price/use ratio. And I know a .38 round wouldn't have the spread of a 5 gauge buckshot round (if it existed) for a shotgun, although this comment has almost no relevance to my question (and makes a comparison to a product that doesn't even exist, nor will ever), but yet I still want to think it would be more effective. The firearms I am looking at are both manufactured by Colt and have the same basic features. Maybe some Slashdot readers, who have shot their own feet, could shed some light on this topic?

Doing the same

[duplicate-nickname]

I am in the same position. We will either need to license 4 processors on our current Xeon 500 system, or upgrade it to a new dual system.

From my research and testing, the dual 2.8GHz Xeon system we are purchsing will be considerably faster than our current quad CPU system. Of course, we will also have more, higher bandwidth memory too. Tack on another 20-30% increase due to hyperthreading (results may vary depending on your SQL usage), and you can't beat a new system. Not to mention a new 3 year warranty that was up on the old system.

So, in the end, we will license 2 SQL Enterprise processors, purchase a new dual 2.8 GHz system with 6 GB of memory, and an addition 350 GB of 15,000 storage all for the price of the original 4 enterprise licenses. It's a win-win situation.

Re:It depends

[SuiteSisterMary]

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.