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?"
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
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
MSSQL Server also really, really, really prefers to talk over the network. I have seen several large sites that had IIS and MSSQL Server on the same box and were slow as shit. However, moving MSSQL Server to its own box, with *nothing* else running, increased response time, etc... by an order of magnitude.
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 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.
ÕÕ