SQL Server Performance

help!! confused from dealers offers

Discussion in 'Performance Tuning for Hardware Configurations' started by prince amir, Aug 22, 2004.

  1. prince amir New Member

    hello all

    i send my request for a SQL server for 3 companys
    hp, dell, ibm

    so far i got from ibm and hp an offer

    ibm say that i should take the xserver 365 with 4 CPU XEON 3.0 GHZ, and 12GB ram, with 4 SCSI 146 ,

    HP say , hey why u need this tank to kill the fly
    take a HP prolient with 2 cpu, xeon or amd with 2 GB ram
    and cpu of 2.GHz , , take 2 computers like that and connect to one central HDD store, then u also have cluster and avilability

    also they asay for 2.5 million transaction and 10k client u dont need that powerful computer.

    dell still dont sent me a offer?

    so im pretty confused,

    i was thinking about a computer that composed with
    2 cpu of amd
    2.4 ghz
    8 gb ram
    2 scsi 36, 15k raid 1 for system
    5 scsi 146 10k raid 5 for ddb

    overall , tose any one know how many transcation a computer in a set of amd do in a sec?

    thanks alot
    amir

  2. Luis Martin Moderator

  3. dhilditch New Member

    Also check www.tpc.org for info about different hardware vendors performance and transactions per minute ratings. Surely you don't need 2.5 million transactions per minute?? The disks on a system like that would fill up pretty quickly - I don't think that kind of speed is even possible yet.

    Dave Hilditch.
  4. prince amir New Member

    sorry, 2.5 million in 4 hours, not in one min

    so if i calculate right
    2.5 or lets say 4 million, devided by 4, then by 60 min then by 60 sec
    gives me 277, lets say 300 trans per sec!!

    so for that, do i need a 30k$ computer, or a 2 cpu with a good raid will be fine>??

    thanks amir
  5. Luis Martin Moderator

    I think with 300 trans/sec, you proposed configuration will work fine, better if all disk will 15K.
    A couple of questions:
    1) Users, database will grow faster?
    2) Applications is more read than write or balanced?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  6. bradmcgehee New Member

    Not knowing a lot about your application, I would recommend erring on the "larger" size of the hardware. Based on what little I know, I would recommend the IBM system over the Compaq system. We use a lot of the IBM 365's in our shop, and we like them a lot.

    I know this must all be very confusing for you, and it is confusing for all of us too. There are no hard and fast rules on what hardware you need for your software. This is because the software varies so much, much more than the hardware. Unless you have tested your software under the load you expect, it is nearly impossible to predict exactly what hardware you need. That is why I always overbuy any hardware I need.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  7. derrickleggett New Member

    I agree with Brad on this one. I like buying servers that can grow also. Buying a 4-processor server with only 2 processors makes a lot of sense when you consider the possibility of upgrading vs. just replacing the entire piece of hardware. I also like IBM because there service is much better. I am in an HP shop now. After working with IBM for many years, I've been shocked by the level of service HP provides (or doesn't).

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  8. prince amir New Member

    thank u all for the answering

    first:
    my Database will not grow large on the begining but u never know
    about the clients, i know something about 10000 clients top

    i just got the offer drom HP, they want to sell the hp dl380 cluster

    http://h18004.www1.hp.com/solutions/enterprise/highavailability/dl380/index-g3.html

    2 XEON 2.8 cpu, 4 GB ram, 2 HDD for each computer and main storage 5 HDD 146 SCSI 10K
    the sellsman say that the system will work great and take the work without a problem
    also its high avilability, that if one computer fail the second take over, or use it active-active mode.
    thst a big plus, but why they want to sell me to old G3 and not the new G4 with xeon 3.2 with 800MHZ bus,
    by the way, is it possible to by from IBM 2 2U amd computer and build also a cluster like HP???

    also where i can get a program that benchmark transaction ??? it there one??
    like pcmark2004, is there something similler to SQL ?

    thanks alot
    amir
  9. derrickleggett New Member

    You can look on www.tpc.org for comparisons. You have a lot of questions in that last post, so here goes:

    i just got the offer drom HP, they want to sell the hp dl380 cluster

    --I would recommend just buying the DL 580. If you have problems, you can easily scale up. The DL380 means you're stuck with what you have. Having said that, I have quite a few DL 580's and DL380's. The DL380's perform fine.

    --2 XEON 2.8 cpu, 4 GB ram, 2 HDD for each computer and main storage 5 HDD 146 SCSI 10K
    the sellsman say that the system will work great and take the work without a problem

    Salesmen are all liars. That's the first lesson. I wouldn't put a 300 t/sec system on 5 146 SCSI 10k drives unless someone made me do it. That's just stupid. You need to really look at buying at a fiber enclosure. Put the log files on 4 RAID 10 drives. Put the data on 6-7 RAID 5 drives. Use the 73gb 15k drives if they provide enough room. Configure the drives like this even if you have to buy a SCSI enclosure instead of fiber.

    --also its high avilability, that if one computer fail the second take over, or use it active-active mode.

    This is great if your applications are cluster aware. You need to know though that this takes some testing and isn't guaranteed to work if you're using third-party software. You might have to tweak some of your applications. Active-Active is only good if you never take one of the nodes above 50%. Also, it requires you to be able to segregate your databases or applications so you don't have a lot of cross-server traffic.

    --thst a big plus, but why they want to sell me to old G3 and not the new G4 with xeon 3.2 with 800MHZ bus,

    That's a good question. The new G4 is quite a bit more expensive right now, so it might be for the price break. Also, they have been trying to unload all the G3 model. Especially, if it's the 2.2 or 2.8ghz models. It's kind of odd right now though. They just shut down a big plant, so the lead time on the G3's is pretty long right now with many vendors. Is this HP direct?

    --by the way, is it possible to by from IBM 2 2U amd computer and build also a cluster like HP???

    Yes. IBM has an X-Series that's pretty much an exact match of the HP DL380. I can get you the model number if you need it.


    --also where i can get a program that benchmark transaction ??? it there one??
    like pcmark2004, is there something similler to SQL ?

    The vendor websites many times have these. Look for server comparison at www.hp.com and www.ibm.com . Also, check out the www.tpc.org website.


    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  10. prince amir New Member

    thanks alot derrickleggett for the answer, also all of the other users.

    this SQL thing make me crazy now, now i learn alot from your answers.
    the buttom line, to get a good SQL server u need to spend money, and if u want a brand name SQL u need to pay more for nothing just the name.

    so let me see:

    i need for a computer that run 300-300 t/sec something like that:

    cpu: amd opteron or xeon - 2 cpu,
    motherboard : intel or amd , 4 cpu, to expend in the future
    ram: 8 gb min.
    hdd.
    all mini- SCSI 15K - 76GB

    operation system: 2 raid 1 or 4 raid 0+1 for better fault tolerance
    log file: 4 HDD in raid 10
    DATABASE: raid 5 , 7 HDD to get to 0.5tera
    TEMPDB: ????

    redundency power supply.

    case? U3 U4
    or outer case for the SCSI HDD.

    also, if i want to build a cheaper system, can i use outer SATA raid cluster storage bay??

    thanks alot
    amir
  11. derrickleggett New Member

    --the buttom line, to get a good SQL server u need to spend money, and if u want a brand name SQL u need to pay more for nothing just the name.

    You are also paying for support. Get the 3yr onsite support. You'll regret it if you don't.

    --TEMPDB: ????
    The data file should be on it's own seperate disks. Preferable RAID 1 or 10.

    --redundency power supply.
    You need redundant fans also.

    --case? U3 U4
    The 4proc boxes are all 4U currently.

    --or outer case for the SCSI HDD.
    Need another 3U generally.

    --also, if i want to build a cheaper system, can i use outer SATA raid cluster storage bay??
    Yes. They aren't as fast and fail 60% more than U320 drives under heavy usage. They are cheaper though.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  12. Twan New Member

    Hi guys,

    we use DL560s which are U2 4cpu servers from HP. fantastic little beasts! although as Derrick metions support is pretty limited (in our case limited to replacing parts rather than troubleshooting even with 3yr on site support...)

    don't use raid 0+1 as it is not very fault tolerant, raid 10 is much better if you're going to take the hit on doubling the number of disks

    15k disks are a steal nowadays compared to the cost of 10k disks

    8GB RAM seems excessive to me, unless you're going to go down the AWE route, as SQL will only use up to 3GB anyway so 8GB sounds like overkill

    As Derrick said, buy a system that you can upgrade, so put GB DIMS in and start with a quad capable box with 2 cpus

    300 t/sec doesn't really mean anything as we have no idea how big each transaction is... In most cases if you don't have a development environment where you can take some measurements and extrapolate the results, then all you can do is buy scalable hardware and upgrade as needed...


    Cheers
    Twan
  13. prince amir New Member

    its hard for me to say how big will be every transaction, the system that im trying to build is for a pilot 3rd program that will work with the DB, so on the stage of experementing here.

    also another question if i can.

    i know that soon , (lets hope) microsoft will go out with windows 64bit (not beta), and SQL 64 bit, so isnt it worth to buy AMD opteron that have the 64 bit working force??

  14. derrickleggett New Member

    --i know that soon , (lets hope) microsoft will go out with windows 64bit (not beta), and SQL 64 bit, so isnt it worth to buy AMD opteron that have the 64 bit working force??<br /><br />My opinion on this, which might not be very popular, is that unless you have an urgent business need you shouldn't ever jump onto a bandwagon that hasn't gone through a minimum of one Service Pack update and had a proven track record at the database level. The numbers are impressive. I wouldn't buy that now though. Give them some more time to work out bugs so they aren't working them out on MY system.<br /><br />--8GB RAM seems excessive to me, unless you're going to go down the AWE route, as SQL will only use up to 3GB anyway so 8GB sounds like overkill<br /><br />It's a huge performance increase though. Doesn't cost that much either. Tell your vendor to look at third-part ECC licensed RAM. Will save HUGE amounts of money.<br /><br /><br />Twan, forgot about the DL560's. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> Sorry about that.<br /><br /><br /><br />--in our case limited to replacing parts rather than troubleshooting even with 3yr on site support<br /><br />lol. Don't they suck???<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  15. Twan New Member

    <br />are you sure that 8GB RAM would give any improvement over say 4GB? I would think that there would be zero gain especially without AWE unless the database was huge and the amount of data queries/updated was huge... again it would come down to application tuning first and foremost, however RAM is cheap so if you can afford 8GB and no one complains then why not <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />yep they do suck...<br /><br />Cheers<br />Twan
  16. simas New Member

    Amir , couple of comments to think about

    a) Cost of licensing - there is 40k difference between 4 and 2 cpus for enterprise edition.
    b) the nature of your data and transactions that run on it . By itself 300 t/sec do not mean much unless you know what kind of transactions will be occuring and what data transactions are occuring on. You need to know what exactly is happening in your system - how much data would your system hold, how would this data be allocated between tables, how fast would it grow , how it would be accessed ,etc, etc ,etc.

    Dell, IBM, HP - who cares? all of them make good hardware. what is important is for you to think through all of the information you have on the system you are about to build and design accordingly optimizing for your specific system.

    I would also probably not go with RAID 5 for any database that you plan to run a lot of DML on , and do not allocate 4 drives in RAID 10 configuration for transaction log - how much of the transaction log are you planning to have?? 2 drives united into RAID-1 disk may be sufficient to you.

    simas
  17. derrickleggett New Member

    --are you sure that 8GB RAM would give any improvement over say 4GB? I would think that there would be zero gain especially without AWE unless the database was huge and the amount of data queries/updated was huge... again it would come down to application tuning first and foremost, however RAM is cheap so if you can afford 8GB and no one complains then why not <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />You would enable AWE, otherwise there would be no point of doing it in the first place. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> This requires the /PAE switch in the boot.ini file and the awe enabled option set to true in SQL Server.<br /><br />--a) Cost of licensing - there is 40k difference between 4 and 2 cpus for enterprise edition.<br /><br />That's why you buy a 4 CPU server and only put 2 physical CPUs in it. Under the MS licensing agreement, you have to pay for ALL physical processors even if you aren't using them.<br /><br />--On the size of your system transactions, I would consider a few things.<br />1. What is the read/write ratio? <br />2. How much IO will the read/writes need?<br />3. How mission critical is this vs. how much money do you spend. Scale accordingly.<br /><br />--and do not allocate 4 drives in RAID 10 configuration for transaction log - how much of the transaction log are you planning to have?? 2 drives united into RAID-1 disk may be sufficient to you.<br /><br />This is wrong Simas. That's the best place you can spend money on disks. If you want to scale back to RAID 1 do so. Plan for RAID 10 at least on the log files though. The log files do only sequential writes across multiple files as opposed to read/writes on the data files. This is why you get a bigger bang for the buck by putting the log files on RAID 10 and can survive with more data IO on RAID 5. <br /><br /><br /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  18. Twan New Member


    but if you buy 2x 140GB disk drives for the logs then might that not be enough for the log files, and buying 4x140GB won't actually gain you any performance...?

    I don't think that RAID10 is any faster than RAID1 it just allows for much large volumes, after all it is simply pairs of RAID1 disk put into a RAID0 volume (and so in theory the second pair isn't used until the first pair is full...?

    Cheers
    Twan
  19. cosaco New Member

    Another consideration, look at the controller configuration,

    some has cache, some no

    some has a battery to use this cache, like a WRITE cache, that is the true value
    if its read cache, really it will never be used, cause there is the RAM cache.

    i suggest at least 512Mb in the controller, with battery, configured for write-caching.

    I give my vote to the cluster of 2 little servers, tomorrow it will be cheaper to make
    an upgrade, the downside, its, you must do a carefull initial setup
  20. prince_amir New Member

    thanks alot with the help.<br /><br />i have another question, does the cluster machine need windows 2003 enterprise to work with cluster and SQL 2000 enterprise .<br /><br />the dealer say that for the system to wok with 2 computers and with a cluster i need to buy the software from microsoft .<br />the stupid thing it cost like the cluster itself because the system need 2 license for both<br />1 for each and that double the price<br /><br />why ms sql cant run on linux <img src='/community/emoticons/emotion-1.gif' alt=':)' />)))
  21. Twan New Member

    Hi ya,

    yes to run a w2k3sql2k cluster you need w2k3 advanced and sql2k enterprise editions. You'll need two w2k3 licenses. For an active/passive cluster you only need to license the active server's sql installation for an active/active you'd need to license both

    Cheers
    Twan
  22. derrickleggett New Member

    Be careful on the active/passive. If you put anything at all on the passive side (like another instance of SQL Server for example to use for reporting), you then have to pay the complete cost.

    Also, technically RAID 10 with 4 disks is faster at reads than a RAID 1 with 2 disks. The reason for this is because of the combination of RAID 1 and 0. The RAID 10 array can support reads on all 4 disks at once. You'll only get two with the RAID 1. It's also faster as you are concurrently writing to 2 disks instead of 1 so you have more disk heads to write to. Just a consideration for you.

    Here's a good general information site if you want to read up on RAID. There are tons out there though.

    http://www.bytepile.com/raid_class.php

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page