SQL Server Performance

New Server: Price Performance Considerations

Discussion in 'Performance Tuning for Hardware Configurations' started by lovellwy, Sep 13, 2004.

  1. lovellwy New Member

    As a new member, I offer thanks for all of the existing discussions; they are very interesting and helpful.

    I have about 5 years of moderate SQL Server development and administration experience. Along with a few friends, I am undertaking a development project somewhat equivalent to 'a garage-based start-up.' We will be utilizing Windows 2003 Server / SQL Server 2000 for some heavy analytical work based on complex stored procedures; as you probably would guess, the procedures involve a lot of reads and writes. The data volumes will not be very large, but the processing will be intense. No outside users will be hitting the box.

    We are getting ready to purchase a system for the project, and I'd like some feedback on my plans. We are doing this project 'on the cheap,' and our key decision factors are 1) low hardware cost and 2) high performance. High availability is desired but far less important than cost and performance.

    I'm thinking of building a system with dual processors (maybe 2.8 GHz), around 2GB of RAM, and SATA RAID (Adaptec 1210SA and two 36GB WD Raptor 10K hard drives).

    Does this approach sound reasonable? Should I consider having a vendor build the indicated system? Am I missing any low hanging fruit? Are there other important considerations?

    Thanks in advance for any and all feedback.




    Relevance Over Precision
  2. Luis Martin Moderator

    If performance is one of the keys, I suggest 15K disk drives.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

  3. vbkenya New Member

    Quick Quip:

    Have the vendor build the system:
    - for the sake of quality (and a reasonable warranty period).
    - in case you need to point a finger at somebody.


    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  4. cosaco New Member

    I think, you must at least consider another 1 GB, of RAM, and,

    256Mb in the controller of the adaptec, configured for write.







  5. satya Moderator

    If you need high availability then you must spend $$$ and with low cost you may not get accurate high-availability solutions in hardware & software.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. derrickleggett New Member

    For that configuration, buy 4 GB of RAM if you can afford it. RAM is cheap. That will give 2 to the OS and 2 to SQL Server. I'd be really wary about only having 2 drives also. You're going to get hit really hard on the performance with only two. If you can afford four, go with that. A little 2 processor 1U box with four drives isn't that expensive.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  7. thomas New Member

    I would also look at warranty and support. This is very important, servers break down, what are you going to do if yours does? Unfortunately it's not at all cheap, but you've got to factor it in too if you are after high availability. You might buy all the redundant disks you like, but your main board might go and you're going to need another one fast. Trouble is, good support can cost as much as the server. You may decide it's worth taking the risk. But I must warn you, things frequently go wrong with our servers, which come from a range of suppliers.

    Tom Pullen
    DBA, Oxfam GB
  8. FrankKalis Moderator

  9. simas New Member

    is hard to tell where your system will be bound (CPU, RAM, I/O) without knowing the nature of processing you run , however
    a) RAM is relatively cheap and rarely hurts. 4 Gb of ram (3 for SQL Server ,1 for OS) is a good start
    b) I/O can be bottleneck , get as many disks as you system can support and arrange them accordingly in RAID level suitable for you.
    c) Depending on how much downtime can you tolerate, decide on whether or not to invest in support/warranty.

    as for hardware, we had good luck with DL servers from HP, relatively inexpensive, easy to manage and perform well.

    simas
  10. joechang New Member

    keep in mind most SATA drives are rated for 2 year life at 20% duty cycle,
    most SCSI/FC disks are rated for 5 years at 100%.
    i would budget for (desktop) disk replacement once or twice per year, server drives should last the full 5 years, but replacement at the 3-4 year point might be helpful
  11. chopeen Member

    Since you're not able to tell what the exact load will be, remeber to buy a machine that you will be able to scale up easily. Leave some empty slots (hope this is a right word), so you can add a CPU or some RAM, if you need them.

    --

    Marek Grzenkowicz
  12. cosaco New Member

    Rereading the specs you give:

    I propose this:

    Get another machine ( a desktop, for Backup purposes) (and make your backup there, if its posible
    use simple recover model)

    Raid-0, (the 2 disks, the more the better(ideally 4),
    you must have the performance counters on, to get an idea),

    forget about raid-5,1,0+1, i think you can stop and recover from a disk crash (its a true guess ?)

    Get at least 256MB, of cache in the Adaptec, configured for write, the read will be managed by the Sql Server cache.

    if you can define the stripe size, put it at 8k.

    Stop all innecesary services, in this server, dont oversize your files, defragment once a week,
    get the RIGHT drivers, for the disk and adaptec, motherboard, network, etc, and try to tweak the "disk queue", normally windows put a "limited" secure queue, in new disks a queue of 16 is not a sin, but must review the model documentation.

    To get this processors busy, i think you will need the 4GB RAM, and please, review your SQL's.

  13. chopeen Member

    quote:Originally posted by FrankKalis

    For SATA RAID you might find this interesting:
    ftp://ftp.research.microsoft.com/pub/tr/tr-2003-70.pdf
    I read this article while I was on the train this morning and one thing really amazed me.

    Please go to this topic and tell me what you think.

    --

    Marek Grzenkowicz

Share This Page