SQL Server Performance

SCSI Considerations

Discussion in 'Performance Tuning for Hardware Configurations' started by Sunemaster, Jul 23, 2004.

  1. Sunemaster New Member

    Hi All!<br /><br />I'm in the process of putting together a new SQL server, and have a few questions regarding the SCSI conf. that i hope some of you can help me with.<br /><br />I've come up with a solution that looks something like this:<br />Dual Xeon 3GHz<br />4GB RAM<br />6 * 73GB 15k RPM Ultra320 disks<br /><br />With a disk setup like:<br />Array1: RAID1 - OS/Appl<br />Array2: RAID1 - SQLData<br />Array3: RAID1 - SQLLog<br /><br />The SQL server will be hosting multiple (not many) databases (Full Recovery).<br /><br />Will i gain anything in performance by choosing a Dual Channel RAID ctrl. instead of Single Channel?<br /><br />Have my eyes on Adaptecs 2200S (Dual) and 2120S (Single) and if i didn't have time to think i would say "Dual", but as i'm on a tight budget i don't wan't to waste my money.<br />(And i have time to think... up a couple of questions for you <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Suggestions and comments are more than welcome.<br /><br />Thanks.<br /><br />--<br />/Sune<br />
  2. Luis Martin Moderator

    With this configuration of Array, I don't think you will gain to much with Dual Channel (Price/Performance).
    But, I you think you will have heavy database, size and use, then Dual will help.
    Also, if you are considering to split data in one Array and Index in other, Dual will help a lot.

    Of course, wait to others members oppinions.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

  3. Sunemaster New Member

    Luis,<br />Thank you for replying.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">With this configuration of Array, I don't think you will gain to much with Dual Channel (Price/Performance).<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />There's perhaps a small chance of getting 2 more disks. If i were to be that lucky, i was thinking of placing the SQLLog on a RAID 1+0 (or is it 0+1?).<br />Comments on that?<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />But, I you think you will have heavy database, size and use, then Dual will help.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I've got the feeling that these dbs only are puppies (no, kittens) next what i've read about here. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Probably 5-10 dbs ranging from 50MB to 8GB.<br />Only one above 3GB. This is also the one that will be used the most and quite a lot.<br />Also the one used, when everyone complains about the performance.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Also, if you are considering to split data in one Array and Index in other, Dual will help a lot.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Erhm...? *wush*<br />I guess i won't be doing that. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Really don't know much SQL.<br /><br />Thanks again.<br /><br />--<br />/Sune<br />
  4. Luis Martin Moderator

    Is good idea to put log in other Array.

    Any database (if no one design in different way) has data and index in one filegroup, PRIMARY.
    So, all data will in one Array.
    Is easy to create one o more filegroups (secondary, sune, free name) in other Array and then put indexs on new filegroup.
    So you have data in one filegroup, one channel, and indexs in other filegroup, other channel.
    In this way performance will better, because SQL can read in paralel way both disk.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

  5. rlahoty New Member

    What benefit do we get by striping the log data? We hardly read it other than in rollback, right? So why do you want to place the log on 0+1 array or 1+0 array? I think RAID 1 array should be good for log as it will give you same write performance as 0+1 or 1+0 configurations and also it will cost you less compared to the other configurations.
  6. Sunemaster New Member

    rlahoty,

    Well... I could be wrong, but i have always thought that the transactional logfiles should be placed on the fastest disks. Likewise for an Exchange database.

    Writes go to the logfiles first, and those are then committed to the DB afterwards. (roughly)

    Am i totally off track with this?

    Dunno how much the logfiles er used for reads, though.
    Perhaps the best setup depends on whether it's a read-mostly og write-mostly DB!?

    Now i'm confused.
    What IS best practice?

    Thanks.

    --
    /Sune
  7. Luis Martin Moderator

  8. rlahoty New Member

    Sune,

    I agree that transaction log should be placed on the fastest disks but I don't think you get any benefits by placing them on 1+0 array or 0+1 array. You should be able to get same performance by placing them on RAID 1 array. That was my whole point.

    Thanks
  9. derrickleggett New Member

    RAID 10 performs significantly faster than RAID 1 actually for heavy write environments, which describes transaction log backups perfectly. It uses the disks much more efficiently for write performance.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page