Become a fan of Slashdot on Facebook

 


Forgot your password?
Close
typodupeerror
×
Microsoft

How Many CPUs for Microsoft's SQL Server? 95

Posted by Cliff from the getting-the-best-bang-for-your-buck dept.
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?"
This discussion has been archived. No new comments can be posted.

How Many CPUs for Microsoft's SQL Server? More

How Many CPUs for Microsoft's SQL Server?

Comments Filter:

  • system requirements? (Score:4, Funny)

    by tps12 ( 105590 ) on Tuesday October 29, 2002 @11:42AM (#4555992) Homepage Journal
    Doesn't it tell you on the back of the box?

  • Ask IBM (Score:5, Informative)

    by forsetti ( 158019 ) on Tuesday October 29, 2002 @11:42AM (#4555999)
    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....
    • For one customer buying one lousy server? I'd be suprised if they care....

      I know Compaq publishes white papers on MSSQL best practices/tuning/sizing. Perhaps IBM does as well.
      • You've obviously never had a business relationship with Big Blue. If they can get you to buy one server, they'll get you to buy another, then another, then your entire IT infrastructure becomes IBM.
        • Hehe. We have several large AS/400s/iSeries and more RS/6000s/pSeries servers. They started sucking on the Wintel front, though, and now we're down to less than 5% of our Windows Servers being IBM. We also dropped IBM for Compaq on the desktop (6000+ machines). They are FAR more accomodating on the midrange stuff. They never really seemed to care when we dropped them.

    • Re:Ask IBM (Score:4, Insightful)

      by Clover_Kicker ( 20761 ) <clover_kicker@yahoo.com> on Tuesday October 29, 2002 @01:09PM (#4556668)
      >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.

  • Redundancy (Score:3, Insightful)

    by polyphemus-blinder ( 540915 ) on Tuesday October 29, 2002 @11:44AM (#4556018)
    There's one obvious benefit (depending on your architecture/manufacturer) with running 4 instead of 2: if one blows out, you aren't fried.

    • Re:Redundancy (Score:5, Insightful)

      by fooguy ( 237418 ) on Tuesday October 29, 2002 @12:03PM (#4556168) Homepage
      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.

  • More CPUs (Score:5, Informative)

    by pci ( 13339 ) <vince...power@@@gmail...com> on Tuesday October 29, 2002 @11:49AM (#4556055) Homepage
    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
    • This is good advice. Of course since you "have to" use MS SQL the cost is not really an option. Do get xeons the much bigger cache should make a lot of difference with a DB Server.

  • It depends (Score:3, Informative)

    by borgboy ( 218060 ) on Tuesday October 29, 2002 @11:49AM (#4556058)
    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.
    • Wouldn't it also depend on if SQL server is compiled with optimizations for the P4? It's probably compiled for the 386.
    • Also depends who is asking for the machine. I was asked to purchase a box to run a sql server that was spec'd with dual 1ghz procs and 4 gigs of ram, only to find out later 10 users would be on the box...they got an old 550mhz desktop with 256ram and were very happy with it.

  • Depends (Score:5, Interesting)

    by duffbeer703 ( 177751 ) on Tuesday October 29, 2002 @11:53AM (#4556093)
    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%.
    • Don't forget, doing parallel processing by multiple machines has its advantages. Such as not needing to share the busses, for memory, net access, etc etc..

      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)

      by V. Mole ( 9567 )

      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.

      • I was being a little sarcastic.

        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.
    • MSSQL did a very lousy job at stuff in parallel.

      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)

    by bwt ( 68845 ) on Tuesday October 29, 2002 @11:54AM (#4556107)
    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 (Score:5, Informative)

    by Bravo_Two_Zero ( 516479 ) on Tuesday October 29, 2002 @11:56AM (#4556120)
    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 (Score:4, Informative)

      by dthable ( 163749 ) on Tuesday October 29, 2002 @12:05PM (#4556188) Journal
      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 (Score:5, Informative)

      by walt-sjc ( 145127 ) on Tuesday October 29, 2002 @01:11PM (#4556697)
      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.
      • How true. I learned a lot about MSSQL performance because of a particularly bad application. It reads a 200MB table (and a poorly-indexed table at that) for *almost_every* application function. Plus, the app author refuses to fix the application, pointing the finger at the hardware.

        To hit MSSQL server's desired response rate of 5ms, we have to push 40MB/s ... sustained ... for random reads AND random writes. Engineering around that is possible, but we quit after a month of halfhearted attempts when we finally convinced management that the application really needs to be fixed, not the hardware. For MSSQL, the queue starts to fill if you miss that 5ms time. Once that happens, you hit a vicious cycle that drags everything down with it if the load stays the same (or, god forbid, increases).

        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.
    • You're absolutely right. Most people spend too much money on cpu and way to little on IO. (We're talking average here). For IBM machines (it actually applies to most intel machines) IBM has a fairly good book (shameless self plug: I co-authored it ;-) called "Tuning IBM eServer xseries for performance" which should point you in the right direction on what to spend on what. I have to disagree with you when it comes to network IO. Way before you will reach a bottleneck with networking your CPUs will probably peak out. Network traffic for sql servers is usually fairly low.

  • Do you have performance metrics? (Score:5, Interesting)

    by fooguy ( 237418 ) on Tuesday October 29, 2002 @12:00PM (#4556143) Homepage
    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.

  • It depends (Score:3, Informative)

    by Mordant ( 138460 ) on Tuesday October 29, 2002 @12:01PM (#4556151)
    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. ;>

  • You're looking for a sizing tool... (Score:5, Insightful)

    by malakai ( 136531 ) on Tuesday October 29, 2002 @12:02PM (#4556157) Journal
    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 [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
    • Dell's sizing tools are horribly off. You can go with a 2650 over their 4600 any day at half the price...assuming you don't need more than 2 processors and 6 GB of memory.

      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.
      • This is going to sound long a snotty troll, but i don't mean it that way.

        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.
        • While your statement may be true, the sizing tool should have a SEPARATE metric for growth and not be built-in. Some apps' needs NEVER grow, while others may grow rapidly. Depends on the app and usage. If the tool truely does spec machines that are far beyond true need, and does NOT specifically state exactly what growth is factored in, then that tool is BROKEN and USELESS.

          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.
    • If I wanted some room for expansion and wasn't sure of the load, I'd buy the four processor box and just one license for SQL, and run it on one processor. If that wasn't enough, add another license and run on two. If you need more power add another license. The cost of the licenses will far outweigh the cost of the hardware, this way you spend a little more on the hardware but only as much as you need on licenses.

  • Type of Workload (Score:3, Informative)

    by Phoukka ( 83589 ) on Tuesday October 29, 2002 @12:12PM (#4556241)
    Your choice of CPU configuration should depend on your estimated workload. If you are going to be running a database that will support many simultaneous connections, each of which performs only a short transaction, then the 4-way configuration would seem to be more appropriate. On the other hand, if you have an app where you will have relatively few connections, but each task is both computationally expensive and not particularly able to be spread over multiple threads, the higher-powered dually-CPU config looks more tempting.

    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:

    • Run Linux and PostgreSQL -- the REALLY low-cost option
    • Run Linux and DB2 -- you are buying IBM hardware, why not check out their database offering as well?


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

    • Re:How many you got? (Score:3, Interesting)

      by glenstar ( 569572 )
      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.
      • I'm curious....which network library were you using, in both instances? I exclusively use DBMSSOCN, which is the TCP/IP library. I have (highly multithreaded, batch-style, network bound) applications which get a 10~15% boost from running local on the sql sever. I seem to remember that named pipes, which in many cases is the default library, performs poorly. Also, the OS version would have a lot to do with it, as TCP/IP performance has made drastic inroads since the days of NT4.
      • There's your problem. You shouldn't be running a SQL server and a web server on the same box anyhow. IIS itself loves to allocate RAM (for session data, etc.) as well as access the hard drive, and when you have SQL trying to do the same thing..

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

    • 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

      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.
    • There are special wierd requirements of SQL that does make it desirable (in many cases) to have more slower CPUs than fewer, faster ones. It really depends on the application*S* that are running on the SQL server. Remember that most of the time, a SQL server isn't just hosting one database, it's hosting MANY databases which are used concurrently. More processors means less contention. Also consider that SQL is *FAR* more likely to be I/O bound than CPU bound. If your SQL server is routinely maxxing out it's processors, it's a pretty strong indication that there's a bug at work (more than likely, bad design).
  • 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.
  • 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.

  • Dear Slashdot, (Score:4, Funny)

    by zulux ( 112259 ) on Tuesday October 29, 2002 @12:44PM (#4556487) Homepage Journal
    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, (Score:5, Funny)

      by highcaffeine ( 83298 ) on Tuesday October 29, 2002 @01:11PM (#4556692)
      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 (Score:5, Interesting)

    by duplicate-nickname ( 87112 ) on Tuesday October 29, 2002 @12:59PM (#4556599) Homepage
    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.

  • Why not use a per server/seat license? (Score:3, Informative)

    by Wonko42 ( 29194 ) <.ryan+slashdot. .at. .wonko.com.> on Tuesday October 29, 2002 @02:53PM (#4557619) Homepage
    SQL Server can be licensed either per-processor or per server/seat. If you license it per server, you'll need to buy additional client access licenses for each client that connects to the server, but since one webserver equals one client no matter how many hits it gets, this could be a more cost-effective solution for your company.

    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.

    • That isn't legal. If you make any connections whatsoever where you cannot identify the enduser then you must use the per-cpu processor licensing. I have just had a heated discussion with m$ about this very subject.

      The only option where you can use CAL's is when you can identify each and every user.
      • That's odd, because my Small Business Server 2000 Per server/seat license agreement right here in front of me says I only need a CAL for each device that is used by an authenticated user to connect to the server. It defines "authenticated user" as a user who "directly or indirectly utilizes the Windows 2000 Server Integrated Sign-On Service or receives credentials from the Windows 2000 Directory Services." It also says I can run the software on up to four processors.

        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.

    • 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.
  • The TPC-C [tpc.org] 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.
  • 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!

  • www.sql-server-performance.com (Score:3, Informative)

    by arb ( 452787 ) <.amosba. .at. .gmail.com.> on Tuesday October 29, 2002 @10:27PM (#4561110) Homepage
    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 [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.
  • 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).

  • Comment removed based on user account deletion

Slashdot Top Deals

Real Users know your home telephone number.

Close