SQL Server Performance

SQL striping vs raid striping

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by frankd, Jan 20, 2007.

  1. frankd New Member


    I'm in the process of setting up a new SQL server (SS05) for a data mining application (note: primarily heavy sequential access -- writes during bulk loads and reads during mining/reporting), and I'm wondering if I should bother striping the raid array, or if I should just do mirroring on the controller and leave the striping to SS05. I have 16 spindles to use (250s) for data, and my database size need is approx 1TB, and I have no problems with max number of volumes, etc. (Controller is a pair of Dell PERC 5s, all 4 ports connected to two split disk racks; an additional separate PERC for the system/log disks.)

    CPU is dual quad Xeon, so I know I get a lot of benefit from running somewhere around +/-8 SQL stripes for the data files (multiple data files per filegroup); my question is if I would gain (even) more by having the raid controller also stripe, or if I should just leave the controller to do mirroring.

    I suppose there are four alternatives:
    - one volume raid 10 across all 16 spindles, with ~8 files in the volume, all pre-sized to avoid frags
    - 8 volumes, each striped raid 10 across all 16 spindles, each with one data file
    - 8 volumes each raid 1 on two spindles, each with one data file
    - 8 volumes, each striped raid 10 across all 16 spindles, each with ~8 files in the volume

    [Although I say raid 1/10, I plan on testing raid 5/50 as well; this question -- to stripe at the disk controller level in addition to the file group level, or not -- seems independent to me of the raid level.]

    Any thoughts??? (I don't really see a benefit to the last alternative above, and I am ambivallent about the third alternative, but am having trouble deciding between the first two.) I wish I had time to thoroughly test this, but I don't so I would really appreciate any advice!

    (I originally posted this over at StorageReview.net, but was directed to this forum...)

    Thanks!
    -frank
  2. mdefehr New Member

    I believe there would be no performance difference between the first two - either way you have 8 files on one RAID 10 array - the fact that the array is divided into different volumes is inconsequential from a performance perspective.

    Recommendations on option 1 vs option 3 would be interesting to me, but I suspect that with completely random access patterns, you're better off just letting the controllers sort it out (i.e. option 1)

    Don't bother with RAID 5 if you agree with the members of www.baarf.com
  3. frankd New Member

    quote:Originally posted by mdefehr

    I believe there would be no performance difference between the first two - either way you have 8 files on one RAID 10 array - the fact that the array is divided into different volumes is inconsequential from a performance perspective.

    Recommendations on option 1 vs option 3 would be interesting to me, but I suspect that with completely random access patterns, you're better off just letting the controllers sort it out (i.e. option 1)

    Don't bother with RAID 5 if you agree with the members of www.baarf.com

    I'm actually leaning towards option 1 now (1 raid 10 volume, ~8 files) myself, thinking that as an extent is only 4k, I really don't want to stripe by extents alone as they are way too small.

    I'm also thinking -- and this is radical -- that maybe for this application, fragmentation is my friend: if I'm building these tables with sequential streaming, and then reading them with sequential streaming, if I pre-allocate the 8 files and avoid frags, then the sequential writes and reads will result in lots of head movement as the drives fetch extents from each file. Perhaps reordering would avoid a lot of this, but still it seems that letting the files grow and frag during the round-robbin process would reult in the data being laid down in an optimal ordering for a streaming read???

    I need to think on this!
    Thanks,
    -frank
  4. Luis Martin Moderator

  5. bertcord New Member

    I would do one of the following

    1. One RAID10 across all drives
    2. One 2 drive RAID1 for TLOG of your main DB, one RAID10 across 14drives.

    Bert

Share This Page