SQL Server Performance

RAID Config & SAS Config

Discussion in 'Performance Tuning for Hardware Configurations' started by rhunt, Aug 11, 2006.

  1. rhunt New Member

    Alright, sorry for so many questions in one post - but I have several things I'm trying to hash out.

    First, I'm building a new SQL box. My budget essentially allows for an HP DL380 G5. I'm thinking 8 SAS drives since I can get all eight in the main casing - although I may cough up more money and buy a storage works extension so I can have 8 15K SCSI drives instead.

    So, I have several questions...

    1) If I go 15K U320 SCSI X 8 I will be using 3 channels on a RAID card with 256MB cache. My plan is to have three RAIDs: 2 drives RAID1, 2 drives RAID1, 4 drives RAID5. I've never actually built a server with more than one RAID per controller before. How likely am I to over-saturate the card with too many IO's per second? Also, how likely am I to over-saturate the PCI bus for that matter?

    2) If I go with SAS drives, the card I'm looking at doesn't do channel - just ports. So, how do you setup different RAID's with the SAS card? Will the RAID setup simply allow me to define port 0 & 1 = RAID1, port 2 & 3 = RAID1, port 4-7 = RAID5. Will it be that easy? Again, what is the standard IO's per second saturation point for SAS.

    3) Finally, I will have 3 RAID's so I would like opinions on the best way to spread those out. Ideally I should separate:
    1) OS/page file
    2) Transaction logs
    3) TempDB
    4) Data Files
    5) Heavy use non-clustered indexes.

    Unfortunately, I have 5 things to separate and only three buckets. Anyone have an opinion on what combo I should use?

    Thanks in advance!

    Ryan
  2. joechang New Member

    you cannot saturate a good raid controller (HP Smart Array are good) on random IO
    you can saturate on sequential, 3-4 drives per channel, at 250MB/sec per channel

    SAS probably will not saturate

    see my post (top in this section) on recommend hardware + storage
    try to push for 2 full racks of external storage for the DL380G5, SAS preferred
  3. rhunt New Member

    Yes, I've seen your post - it's very helpful. Unfortunately, the listed configs are way above my budget. This server is currently running on a 4x15k U320 SCSI RAID5 array with everything on this one array. It is a dedicated SQL box (dedicated to one app..one function) so I'm lucky to get the number of drives I've listed above.

    Can you comment on the 3 separate RAID arrays over the 8 internal SAS drives? Is this possible/fairly easy?

    Also, can you comment on the I/O distribution/separation I listed above? If you had 5 workloads to separate:
    1) OS/pagefile.sys
    2) Tempdb
    3) Transaction Logs
    4) Heaps & Clustered Indexes
    5) Non Clsutered Indexes

    ...but only 3 RAID arrays, which would you (or any other poster) put where?
  4. rhunt New Member

    Joe, you've made an interesting statement in your hardware recommendation post that I've quoted below.

    quote:Originally posted by joechang

    data from multiple databases can be shared on a common pool of disks
    only logs get dedicated disks.
    I think many other DBA's would disagree since we are often trying to separate sequential from random I/O - as well as achieve parallel disk operations (multiple RAID arrays working on the same I/O requests).

    However, I've been thinking about this very point. Even on a SQL Standard box I never go less than 4GB. This way SQL gets its full 2GB and the OS gets its own 2GB for OS operations, binaries, etc.

    The point here is that I expect there is VERY would be very little I/O activity on a dedicated OS/pagefile.sys/binary mirror. This very much feels like I might be wasting 400 IOPS or 160MB/s of total I/O throughput.

    So, in addition to commenting on my post listing the 5 different I/O workloads across 3 RAID arrays (I am still interested in input on that one), please also comment on:

    (for 8 SAS internal drives)
    2@RAID1 = transaction logs
    6@RAID5 = OS/pagefile/binaries/tempdb/data/indexes

    Thanks. RH
  5. Luis Martin Moderator

    If you are not in critical situation, wait for Joe answer.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


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



  6. joechang New Member

    many people seem to be big on rules, without considering the circumstances that the rule were developed for

    many of the rules for database and disks were developed long ago for different circumstances,
    including unix file system limits, lack of raid controller etc

    everything i have seems to indicate that most database disk loads are bursty
    ie, there will be a burst of random, a burst of sequential etc
    there is usually not a fixed distribution,
    and certainly it can be said that the tempdb load is not guaranteed to be sequential
    (assuming you are aware that you can put indexes on temp tables)

    for this case, i would want all disks to be available to drive the load of the moment

    also, if there is significant disk activity to OS or page, then there is something wrong with your configuration
  7. rhunt New Member

    Thanks. I think we are in agreement. RH

Share This Page