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?"
Doesn't it tell you on the back of the box?
Karma: Good (despite my invention of the Karma: sig)
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!
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
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.
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%.
Conformity is the jailer of freedom and enemy of growth. -JFK
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.
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.
Timesheets and vacation days for 20 people and it's always thrashing hard? Give me a break. You could do that with windoze shares and excel.
"Eve of Destruction", it's not just for old hippies anymore...
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.
"All I ever wanted was to see Larry Wall give Bill Gates a Perl necklace."
http://www.eisenschmidt.org/jweisen
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.
No, no, no! It's not just syllables, you have to have a reference to the seasons:
Seattle autumn and taking over the world are on Redmond minds. Under winter's rain, their gazes turning southward: they look to Intel. Spring's green shoots show them that their license agreements will make them money. Laws bought each summer strengthen the illegal trust. No one breaks them up.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
Is it just me, or is there something horribly wrong if a computer is "always thrashing hard" tracking timesheets and vacation days for "about 20 employees"?
Is this a 70's-era computer, are the 20 employees all lightning-fingered data entry operators, or what?
It's easy to make up & spread cool- and credible-sounding stuff. Finding & checking hard facts is hard work.
Fimbulwinter falls
upon my PRE-less haiku.
Fuck you, Rob Malda.
Seattle autumn
and taking over the world
are on Redmond minds.
Under winter's rain,
their gazes turning southward:
they look to Intel.
Spring's green shoots show them
that their license agreements
will make them money.
Laws bought each summer
strengthen the illegal trust.
No one breaks them up.
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!
Check the CPU usage. I'm not sure if you can see what process uses most CPU, but on HP-UX it's the 'select' process (SQL query selects). When the peaks are at max, the system will respond slowly, and you will need more CPU's to master these peaks.
Just remember that in your sample boxes that you listed, the P4s would have hyperthreading, which might boost their performance even more (I'm not sure how much of an effect that would have on something like this). So if you're looking at maybe 2 P4s, this will throw another monkey wrench in things. But I agree with some of the posters before me, ask the sales rep, see if they have any benchmarks.
Comment forecast: Bits of genius surrounded by a sea of mediocrity.
SQL Server likes CPUs alright, but what it really really really really wants, is fast access to disk, and lots and lots of memory. (so it can cache things all over da place)
and like everyone else has been saying, it really depends on what you're trying to do. ask your DBAs or ask your Microsoft Consulting Services DBA. and then ask your hardware vendor. and then ask your finance guy how much you can really spend. cuz if i'm not mistaken, budgets are tight nowadays.
Maybe I'm just naive, but can someone explain a believable situation where four slower CPUs (say, 100mhz) would beat two faster ones (200mhz)? There's loads of overhead in an SMP system, so I find it hard to imagine that having more chips would be better. (I don't know about the special weird requirements of a big SQL system, though).
Those P4 Xeons might be Hypertthreading capable, which, if MSSQL2K was tuned for would make an ideal choice. I'm sure $price/$NUM_of_CPUs vendors were not happy with this move to multi-core/single die/socket procs, it will be interesting to see if M$FT charges different prices for a HT machine than a Non-HT box.
Read my plan to save the Bengals
As the number of processors increase the bus becomes saturated and no matter what how many CPUs you add it doesn't increase performance.
In most cases SQL Server's performance may be limitted more by your disk performance than the CPU's performance.
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?
Moneyed corporations, non-working 'poor' and criminal prisoners are turning productive citizens into tax-slaves.
This isn't offtopic, it's funny and pretty darn insightful too.
evanchik.net
A: Zero.
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.
ÕÕ
I dunno, I think that should have been modded as funny. 3/4 of a processor ? Add another 2/3 ? Ha ha ha !
I don't know how this affects licensing, but much to my surprise our dual processor Xeon shows up as 4 CPU's to both the OS and SQL (I think it's because of Hyperthreading). You can always turn off CPU's in the DB if you're concerned about the license. But you should keep in mind that the Standard Edition of SQL 2K only supports 4 CPU's.
I know that this will get me nailed to the wall by the postgre/linux/mysql crowd; but you probably should check out the SQL parts of Microsoft's website. There are bound to be whitepapers there on this or a similar topic. TechNet may have something for you as well; but that's almost like putting a generic term into an engine and hoping that one of the 2,000 search results is the one you want.
I think with the interesting people, their lives can't possibly be wrapped up into a nice little package.
Uh, posting to slashdot shouldn't get you that excited...
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.
I didn't even know that you had a *per CPU* license. I mean its the same goddamn box. :-)
Well I guess I wouldn't be knowing cuz (i) I use linux (ii) Whenever I use windows I lookup crack.txt copy it and press "Accept"
ciao nandz
The TPC-C benchmark is very good, and there are enough multi-cpu results that this should inform you a bit.
In general, for generic database work the important system aspects are:
-very large I cache
-lots of main memory bandwidth
-lots of secondary store bandwidth
This would suggest that a 2x xeon would be better than a cheaper 4x P3 box. While it's impossible for us to give you sizing advice without a more detailed question, my guess would be you need 2 xeon boxes with 4 SCSI disks each in RAID 0-1. One should be enough to run your production work, and I wouldn't personally run MS SQL2k without having a hot backup running as well. It's reliability is a bit better than say, MySQL, but it still will complain now and again. With intel kit, the things that will fail most often will likely be disk controllers and the like, not the SCSI disks, so I'd even chose having a hot backup system over a more reliable RAID setup.
Not only is the licensing cheaper, but you'll get better performance. In a theoretical sense, there can be some distinct advantages to 4CPU over 2CPU assuming the Mhz are equivalent (e.g. 2x2Ghz vs 4x1Ghz), but in reality, especially in a Microsoft reality, the scalability just isn't there. The biggest win comes from moving from Single to Dual, and after that it drops like a rock.
11*43+456^2
Small Business Server exists for just that--small businesses--and the licensing scheme reflects that. A Small Business Server must be the only server on a single-domain network. Which means it acts as PDC, file server, print server, mail server, SQL server, and so on. All of the software in Small Business Server on one server only.
You can buy enough CALs to accommodate up to 50 users, but if your network exceeds that, you'll have to purchase the full versions of Windows 2000 Server, SQL Server 2000, and Exchange 2000 Server.
Because the Small Business Server only accommodates 50 clients, you can expect that a decent box will be able to handle anything its users can throw at. Though if you put it on the Web and get massive traffic, every other aspect of your network will suffer.
If you decide the network needs another server, you'll have to get the "Migration Pack," which will give you full standard licenses of each product with five CALs each. You'll be able to install each product on a different server (though you'll need to purchase a new Windows 2000 Server license for each new server) to increase performance.
Though it seems like Small Business Server is a trap, it's actually a boon for small businesses because they will be able to afford the software and because they have few employees, the strain on even a multi-purpose server will be minimal. And when the business outgrows the single server solution, the Migration Pack makes it simple enough to expand the network.
Hi,
c
This past weekend we migrated our Dell 6400 with 4 X 550 Xeon processors, 2.0GB RAM, 6x9GB RAID5, NT 4.0 and SQL Server 7.0 to a new Dell 2650 with 2 x 2.4 Ghz Xeon processors, 2.0, 2.0GB RAM, 73x4 RAID10, W2K Server and SQL2K - We are getting better performance with the 2 processor box. The processors weren't the only factor, but hopefully this info will help you.
Also, of interest, the newer Xeons support Hyper Threading. The more interesting note about hyper threading and SQL2K is that if you buy a server with 4 of the newer Xeon processors, you can't use all the processors in the standard edition of SQL2K - you'll need to get the enterprise edition @ 20K/processor - Take a look at: http://www.microsoft.com/sql/howtobuy/SQLonHTT.do
License the 2 cpu version, put the saving (about 35k) into hiring a grade A db architect who can make that server bark, roll over, whatever.
There is so much crappy db architecture out there makes me sick!
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.
I can see why you didn't find anything on Google. I think they're a little biased.
Search: MSSQL Benchmarks
Reply: Did you mean:MYSQL Benchmarks
The PC's architecture does not lend itself too well to multiple CPUs. In particular, the CPUs do not have their own separate memory partitions so they will keep fighting over the same bandwidth and I/O space, thrashing the main cache in the process. While I believe the performance in a dual system is pretty close to a single CPU at twice the speed (because the single CPU has the context-switching overhead too), it gets only worse from there. I would not be surprised if for some application you'll only get a 300% performance increase from a for 4-way machine.
At any rate, database servers are very disk-intensive anyway. They are all about organizing data on disks to retrieve it in the least amounts of block reads, but they can't control where the data actually goes on the physical disk. Most of the time it's the disks and I/O bandwidth that are the bottleneck. I would get a good RAID device with gobs of memory (independent of the main system's) connected to fast SCSI disks in a striped configuration. I would also get as much system memory as I can to allow disk caching.
Another possibility is to use distributed access instead of a parallel system. Set up a replicated database where you can send the requests to any of the machines. I think this will get you the best performance improvement over any n-way system if you are doing lots of short queries and few writes (the times where this won't work so well is when your program consists of heavy, resource intensive single queries that take long to answer).
While the P4 architecture may not be as "effecient" as the PIII architecture, the P4 platform does benefit from a higher bandwidth memory bus. DDR sdram and Rambus are great for database servers....way better than the SDR sdram that the PIII xeon was stuck with.
-ted