SQL Server Performance

Long-Term Hardware Solution

Discussion in 'Performance Tuning for Hardware Configurations' started by rweinstein, Sep 1, 2004.

  1. rweinstein New Member

    Satya, Luis, others,

    I am asked to come up with a long-term hardware solution (3-5 yrs) and to be very conservative. Right now I have my DB, logs and tempDB on one drive (RAID5) and am running Cognos Cubes on same drive and server (not dedicated SQL server), but am also running DW updates and Cognos Cube updates on a 2nd load balancing server.

    Here is what I am proposing, please let me know if you think this is enough for the long term (3-5 years) given that my DB will likely no more than triple to 45GB in this time period. Also, please let me know if this configuration follows "best practices", if there are any glaring weaknesses, if something does not look right, if I should ask for more, or if I am proposing too much. Lastly, we use CPQ ProLiant servers. Are these good enough or sufficient? As always, I appreciate all of your invaluable input!

    Stats:
    Current SQL DB is 15GB (should not grow to more than 60GB in 5 yrs)

    Long-Term Hardware Solution Proposal:

    Server 1 (dedicated for SQL server DB):
    8 X 3.6GHz 800MHz FSB processors
    16GB RAM
    200GB RAID1 C: Drive (holds the Log files and tempDB and Backups - no more than 2)
    200GB RAID5 E: Drive (holds the DB Data file)
    10GB RAID1 P: drive for paging file

    Server 2 (Load Balancing Server used for Cognos Cube processing, Data Warehouse Update processing, and a backup for server 1 in case of failure):
    8 X 3.6GHz 800MHz FSB processors
    16GB RAM
    200GB RAID1 C: Drive (used for backup if Server 1 fails)
    200GB RAID5 E: Drive (holds the Cognos Objects)
    10GB RAID1 P: drive for paging file

    Server 3 (Load Balancing Server used for Cognos Cube processing, Data Warehouse Update processing, and a backup for server 1 in case of failure):
    8 X 3.6GHz 800MHz FSB processors
    16GB RAM
    200GB RAID1 C: Drive (used for backup if Server 1 fails)
    200GB RAID5 E: Drive (holds Backup of the Cognos Objects)
    10GB RAID1 P: drive for paging file
  2. joechang New Member

    my recommendation is that one should not buy a high-end system for 3-5 yrs out, but rather have a program from replacing performance critical systems every 2 years.
    basically, an 8-way at best will only be 1.5X faster than a 4-way, but more than 2X cost.
    however the most current 4-way (or any given platform) will be 1.4X faster every year.
    also, i don't think the 4-way Xeon next will have 800MHz FSB, that's very difficult to do for 4-CPU,
    you don't need a separate drive for the page file, if you are actually using the page file, you are having more performance problems any single disk will solve.
    plan on 64-bit OS for the analysis systems, ie, start evaluating now, the large address space is critical
  3. satya Moderator

    I agree with Joe comments and the first task is to stabilize the existing infrastructure. Scale the current systems and hardware upgrade will definetly overwhelm lack of stability. REcently we performed planning exercise on one of the 24/7 critical system and found EMC SYmmetrix -poweredge will suffice the growth of database over next 5 years and overcome any issues that may arise. I don't see any issue in your proposal.

    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.
  4. Luis Martin Moderator

    Sound good to me.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

  5. rweinstein New Member

    Sorry, I apologize if I misspoke.

    We are purchasing the system next year and hope for it to last 3-5 years for our growth needs. I may have indicated that we were wanting to purchase in 3-5 years.

    Also, right now, we are not experiencing any major performance issues.

    The new system is to shorten our overnight refresh time, to use best practices (separate SQL files, dedicated server), and to be able to sustain some possible major future growth.

    Our current infrastructure is stable.

    Would you please clarify what a "64-bit OS for the analysis systems" is?

    Also, how do I change the use/non-use of a Page file? How do I know if I am actually using it?

    Joe, I'm confused at your statement "however the most current 4-way (or any given platform) will be 1.4X faster every year". I'm not sure what this means.

    Do you guys suggest I go with a 4 way processor and remove the page file?

    Thanks.
  6. chopeen Member

    quote:Originally posted by rweinstein

    Also, how do I change the use/non-use of a Page file? How do I know if I am actually using it?

    You cannot change it. Windows always uses paging file. However, excessive page file usage can really hurt SQL Server performance.

    To check it, you should monitor Memory: Pages/sec counter. It should never be consistently higher then zero.

    So, since every effort should be made to minimize paging file usage, there is usually no need for a separate disk to put this file on.

    --

    Marek 'chopeen' Grzenkowicz, MCP
    Poland
  7. rweinstein New Member

    Chopeen,

    Thanks, I am monitoring this stat and it does go much higher than zero during timeperiods, but not longer than 1/2 hour. During these times, though it can get as high as 1300, but averages more like 616. But, I think this is due to me running my resource intensive Cognos applications on the SQL server, it is not dedicated.

    For the new system, we plan to have a dedicated SQL server.
  8. joechang New Member

    approximately once per year, the major system vendors will release a new 4-way product, which may be a completely new system, or an upgrade of the existing system, example, 2.0GHz to 2.8GHz.
    the 2005 model should be 40% faster than the 2004 model, the 2006 40% faster than the 2005.
    this is not always exact, one year it might be a 60% improvement, the next it might be just a 20%.
    i suspect the 2005 system will be a minor improvement (minor frequency gain), and the 2006 will be a major improvement (dual cores)

    the 2 server on which you want to run Cognos, i cannot actually speak for Cognos, so i am assuming all analysis/cubing software has more or less the same characteristics, in this case, they really need to be on 64-bit hardware and OS
    the system you buy next year should be 64-bit capable. i would then just be sure you are using Windows 2003 Server 64-bit version
  9. rweinstein New Member

    Joe,

    Thanks. Much clearer now.

    It was also mentioned that it is best to have an upgrade plan for every 2 years. Does this mean swap out the hardware of the current server, or migrate to entirely new servers every 2 years?

    What are best practices for hardware upgrades? Is it fairly easy to upgrade processors and/or hard disks or other hardware components?

    Also, is 16GB of RAM sufficient, too much, too little? How to organizations go about decding how much RAM to have?

    Thanks.
  10. joechang New Member

    for a busines with many servers, i like to recommend buying new servers every 1-2 years for the most performance critical servers depending on growth rate (40% annual growth means once per year)
    then rotate the old system to next priority applications, finally replacing the system at the 5 yr point. i would also recommend no critical data resides on old hard drives. SCSI / FC drives are rated for 5yrs, i think its safe to most important stuff at the 3-4year point,
    this comes from having 3 drives in an old array fail at one time

    i would make some attempt to figure out how much memory you are using.
    but the other way is to buy max economical memory,
    if your 4-way has 16 DIMM sockets, and 1GB modules are reasonably priced, buy it, but avoid the really expensive ($/GB) stuff.
    i just checked the crucial site for PC2100 registered ECC DIMMS at:
    1GB $311
    2GB $800
    4GB $3500

    so there is a mild penalty for the 2GB, but a big penalty for the 4GB
  11. derrickleggett New Member

    Agree with Joe on the memory. We buy third-party RAM instead of the branded RAM for our servers that need additional RAM. It's the only thing we don't buy branded as HP. Everytime we buy a new server, this saves us thousands of dollars. The memory has just as good of a guarantee; and we have had no problems with it. When you're buying 4-5 new servers a month with additional RAM, this is huge.

    I think a three-year rotation cycle is max on servers. Never plan on having a frontline production server longer than that. If you can't afford a 3 year cycle, replace all the secondary servers with your frontline for the 4-5 year timeframe and replace the frontline at least.

    On SAN solutions, you seldom can get by more than 3 years. Most of the solutions come with a three year coverage. Running a SAN without it is crazy. Regarding EMC, I would probably just consider a Clariion series for your environment if you do go with SAN at all. You definitely don't need Symmetrix. You could probably get by with fiber enclosures though. One thing to consider, the fiber drives and U320 drives generally have a MUCH lower failure rate than the SATA drives if they are considering that route.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page