How Many CPUs for Microsoft's SQL Server? 95
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?"
Re:I will tell you (Score:1, Offtopic)
Was this a troll or what? (Score:2)
Re:Depends on the usage patterns (Score:1)
Is this a 70's-era computer, are the 20 employees all lightning-fingered data entry operators, or what?
Re:Depends on the usage patterns (Score:1)
I dunno, I think that should have been modded as funny. 3/4 of a processor ? Add another 2/3 ? Ha ha ha !
system requirements? (Score:4, Funny)
Re:system requirements? (Score:2, Insightful)
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.
Re:system requirements? (Score:2, Informative)
Ask IBM (Score:5, Informative)
For $$$ like this (and the potential of future business), they should scurry, and get their R&D kicking real quick....
Re:Ask IBM (Score:1)
I know Compaq publishes white papers on MSSQL best practices/tuning/sizing. Perhaps IBM does as well.
Re:Ask IBM (Score:2)
Re:Ask IBM (Score:1)
Re:Ask IBM (Score:4, Insightful)
>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.
You may also want to ask Microsoft... (Score:3)
Re:You may also want to ask Microsoft... (Score:2)
Redundancy (Score:3, Insightful)
Re:Redundancy (Score:5, Insightful)
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.
More CPUs (Score:5, Informative)
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
Re:More CPUs (Score:1)
It depends (Score:3, Informative)
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.
Re:It depends (Score:1)
Re:It depends (Score:2)
Re:It depends (Score:1)
Depends (Score:5, Interesting)
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%.
Re:Depends (Score:2)
Yes, the disk would be needed to be shared for writing, while snapshoting your data to other db's would overcome this.
MTOWTDI powers, activate!
Re:Depends (Score:3, Insightful)
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.
Re:Depends (Score:2)
But at my current job, I have noticed that some developers/application owners have a tendency to do this. One guy claimed his data would change 50% daily, which would have required an extra 2TB of tape storage over 3 years. We allocated him 500GB, and his backups have used about 50% of this over two years, using the same backup methods as were spec'd in the past.
Keep in mind that alot of this is attributed by a management-inspired lack of trust between the systems, enterprise mgmt & support and application groups. People outside of the systems group do not trust the large integrated systems like SANs and partitioned Sun and IBM unix machines.
Re:Depends (Score:2)
We had to tune our queries very carefully to utilize all 8 processors (even when we were only issuing reads).
Expect to have more developer/DBA work to utilize all those processors.
Joe
Four (Score:5, Funny)
A: Four, one to hold the light bulb and three to turn the ladder.
Q: How many CPUs does it take to run MS SQL Server (Score:2)
Re:Q: How many CPUs does it take to run MS SQL Ser (Score:3, Funny)
Uh, posting to slashdot shouldn't get you that excited...
Limitiation will likely be IO anyway (Score:5, Informative)
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 (Score:4, Informative)
Re:Limitiation will likely be IO anyway (Score:5, Informative)
Memory and IO are still the biggies on most DB processing though.
Re:Limitiation will likely be IO anyway (Score:2)
To hit MSSQL server's desired response rate of 5ms, we have to push 40MB/s
It's true that you cannot stress enough the need to normalize your database as well as anticipate how to scale the potential weak spots in the design. In our case, the application proceeded most of the admin staff, so we're just playing cleanup.
Re:Limitiation will likely be IO anyway (Score:1)
Do you have performance metrics? (Score:5, Interesting)
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.
It depends (Score:3, Informative)
-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.
Re:It depends (Score:4, Insightful)
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.
You're looking for a sizing tool... (Score:5, Insightful)
Here's some of dells:
Dell Sizing Tools [dell.com]
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:You're looking for a sizing tool... (Score:1)
Some people may want the 4600 for upgradeability...but in my 6 years in IT, I have only added CPUs to one server, and have rarely added memory. Usually you're looking at replacing the machine by the time upgrades are needed.
Re:You're looking for a sizing tool... (Score:2)
Perhaps the reason you are looking at replacing the system by the time upgrades come up, is that you didn't buy something powerfull enough to begin with. It could be that the sizing tools are taking that into account.
Re:You're looking for a sizing tool... (Score:2)
My guess is that the tool specs machines way bigger than true need so they don't have customers comming back saying "You told us that this machine would do the job and it's way too slow." The CYA factor.
Good admins of course don't need marketing tools. They are for the pointy haired types that don't know enough to make good decisions.
Re:You're looking for a sizing tool... (Score:1)
Type of Workload (Score:3, Informative)
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!
It's all about peaks (Score:1)
Sample Boxes (Score:2)
Re:Sample Boxes (Score:1)
Re:Sample Boxes (Score:1)
Licesnsing is based on number of physical processors.
How many you got? (Score:2)
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.
Re:How many you got? (Score:3, Interesting)
Re:How many you got? (Score:2)
Re:How many you got? (Score:2)
Maybe I'm just Naive, but... (Score:2)
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).
Re:Maybe I'm just Naive, but... (Score:2)
Assume that all other things (bus speed, memory size and access speed, disk i/o speed) on your two systems are the same. The question is, how much work needs to be done to switch the running thread on a CPU? If it's a lot - and it might be, if you're working on large resultsets for your queries, and have lots of queries running concurrently - then the less you have to do it, the better. On systems like this, many slower processors give you more throughput and lower latency than a single faster one, even if (num cpus * clock speed) is the same on both.
Re:Maybe I'm just Naive, but... (Score:3)
One thing we are missing (Score:1)
Re:One thing we are missing (Score:2)
Go for the faster processors (Score:2, Interesting)
In most cases SQL Server's performance may be limitted more by your disk performance than the CPU's performance.
Dear Slashdot, (Score:4, Funny)
Any thoughts?
Re:Dear Slashdot, (Score:5, Funny)
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?
Re:Dear Slashdot, (Score:1)
Doing the same (Score:5, Interesting)
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.
Why not use a per server/seat license? (Score:3, Informative)
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.
Re:Why not use a per server/seat license? (Score:1)
The only option where you can use CAL's is when you can identify each and every user.
Re:Why not use a per server/seat license? (Score:2)
My web users are not authenticated users by that definition, so they do not need CALs. If Microsoft told you otherwise, they're lying. Tell them to read their own license agreement.
Limitations of the Small Business Server license (Score:1)
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.
Benchmarks: you didn't look around much aparently (Score:1)
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.
Go Dual (Score:2)
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.
Misappropriation of funds! (Score:1)
There is so much crappy db architecture out there makes me sick!
www.sql-server-performance.com (Score:3, Informative)
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 [sql-server...rmance.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.
Google doesn't like MSSQL (Score:2)
Search: MSSQL Benchmarks
Reply: Did you mean:MYSQL Benchmarks
the PC is not a parallel machine (Score:1)
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).
Re: (Score:2)