SQL Server Performance

Wierd Queue length numbers with files and RAID 0

Discussion in 'Performance Tuning for Hardware Configurations' started by yodarules, Sep 8, 2005.

  1. yodarules New Member

    I'm trying to achive the best performance with what we have and have been doing a lot of tests.

    Windows 2003
    SQL Enterprise SP4
    Dual CPU Intel 2.80 Ghz
    2 GB RAM
    C: 180 GB
    D: RAID 0, 3 disks of about 180 each, RAID Controller has 64MB RAM, Stripe size 64 KB

    SQL is installed on C:
    We do a lot of writes, every millisec. Read also happen, but currently write is becoming a big issue.

    When we have 1 mdf and 1 ldf, both on D:, the Avg disk que length seems fine. I sepearted the table into which there's continuous writes into a new file group. So now we have 1 mdf, 1ndf and 1 ldf. If I used the same old indexes then I see that the Avg disk que length increases a little bit. But if I put in nearly the same number of indexes, but break up the composite indexes into individual indexes, there's an enormous increase in avg. disk que length. Any ideas why???


  2. mulhall New Member

    Striping will increase read performeance and decrease write performance.

    Breaking up your composite indexes will increase the number of reads required to return a result set.
  3. satya Moderator

  4. derrickleggett New Member

    What do your disk queue lengths look like, out of curiosity.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. FrankKalis Moderator

    Derrick, nice to see you're back! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Aargh, it's killing me, but I have to ask this...<br /><br />yodarules, are you the guy who appeared on SQL Server Central.com some time ago, gave *really* knowledgeable answers and disappeared some days later [?]<br /><br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  6. mmarovic Active Member

    quote:But if I put in nearly the same number of indexes, but break up the composite indexes into individual indexes, there's an enormous increase in avg. disk que length. Any ideas why???
    Your composite indexes where used for covering important queries so only data in the index were used instead of additional bookmark lookup processing.
  7. derrickleggett New Member

    Frank, it looks like he disappeared again. lol I'm going to try posting more often again. I kind of disappeared off the face of the earth. My client decided to redesign the world all at once.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  8. yodarules New Member

    Sorry guys, have a been with a lot off stuff. Well I don't I'm the same guy you are looking for in the other site. I'm doing a lot of test and will report back once I'm done with those.

    Well my q's are really high.... Most of the time it between 30-50. Usually in about 24 hours our applications fills out about 200M records. When with the new changes its usally drags and drags and only reaches about 100M records.
  9. FrankKalis Moderator

    <pre id="code"><font face="courier" size="2" id="code"><br />Well I don't I'm the same guy you are looking for in the other site.<br /></font id="code"></pre id="code"><br />Bummer. Anyway, welcome on board. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  10. satya Moderator

Share This Page