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?"
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....
10b||~10b -- aah, what a question!
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
It completely depends on the load the machine will experience. Maybe you could explain a little more what kind of traffic the server will be handling.
Here at work, I deal with SQL Server machines from 2x PII Xeon 450 all the way up to 8x PIII Xeon 733. For the loads we experience, more processors is better than faster/fewer, but I've not worked with P4 Xeons. Hyperthreading might actually tip that in your favor.
Can you buy a 4x capable chassis with 2 CPUs? Maybe leave yourself some room. We do this here quite a bit - but we use Compaq Proliants.
meh.
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.
Amateurs discuss tactics. Professionals discuss logistics.
upon the structure of your data, the types of queries you'll be running, whether the database is geared more towards large numbers of people doing simple queries, or small numbers of people doing complex queries, etc.
;>
-Very- generally speaking, your RAM and hard drive storage are far more important to your performance in this arena, coupled with your table layouts, etc. Multiple FiberChannel RAID controllers connected to big, fast RAID arrays (generally, RAID 5+1 is the 'Swiss Army knife' config for this sort of thing, but again, depends on what you're doing, and how) is key . . . . also, you may wish to consider having two separate boxes accessing that same shared storage via a SAN switch, for faliover, etc.
Finally, it may well be worth your investment in a second server with a 'warm' copy of the database synced onto it as a reporting instance . . . you can run complex queries against the reporitng instance, rather than the live copy, so as to avoid blocking issues, etc.
Better yet, do all this under Linux and Oracle.
Well, the first question Microsoft is going to ask is How much money you got? Then they tell you what kind of box you need to run SQL Server.
The best option, to my eye, is the one others have suggested: get a 4-way chassis with only 2 CPUs, and pay more licensing if you find yourself really needing it. And, realistically, if you find yourself needing more iron in the future, it will be cheaper to throw 2 more CPUs into an existing box and pay the additional licensing fees than it would be to buy another computer.
I have a couple of other options for you to consider, though I realize before I say it that the decision has already been made and that I'm sure there are dozens of reasons not to, but you still might want to consider them:
Now I'm sure that your developers don't know Linux, PostgreSQL, or DB2, and the decision has already been made, etc. ad nauseam, but I figured I'd go ahead and toss these possibilities out for the sake of argument.
Either way, good luck!
In addition, you may want to look into purchasing Small Business Server instead. Small Business Server comes bundled with SQL Server 2000 Standard and five CALs, and if you don't need the extra features in Enterprise, it'll save you a ton of cash. Why SQL Server is cheaper when bundled with a bunch of other products than it is on its own I will never understand, but that's the way Microsoft works, I guess.
The answer to your question depends on a lot of variables. As others have mentioned, make sure you throw plenty of RAM into the server and that your disks and network are nice and speedy too. You then need to assess your particular requirements. What is the current load like (if you are running in a production environment already) or what is the simulated load in your testing environments? How well written are the stored procedures? How many users? What usage patterns do you expect? What response times are acceptable? How much data? How well-structured is the database? etc...
Poorly written code can kill a server far more quickly than slow processors or less CPUs. Make sure the developers know what they are doing and are not generating too many round-trips to the database and are writing efficient code. A major problem with my current project is that most of the stored procedures were written by C++ and VB coders who did not understand the set-based nature of SQL. Re-writing some of their stored procedures (removing their reliance on cursors for example) achieved massive performance improvements - in one case taking a 12-16 hour batch job down to 25-30 minutes, and bringing most 1 minute or so queries to under a second. A bigger/faster box would not have helped much in those situations.
Head on over to www.sql-server-performance.com for some help with finding your answer, though my suggestion is that if cost is such a big factor, buy a server with 2 really fast CPUs, but make sure it is expandable and you can then add another 2 CPUs to it if you need extra performance at a later stage.
Whatever you do though, make sure you have plenty of RAM.