SQL Server Performance Forum – Threads Archive
Wierd Queue length numbers with files and RAID 0
I’m trying to achive the best performance with what we have and have been doing a lot of tests. Windows 2003SQL 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???
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.
May check thishttp://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1571 post for relevancy. 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.
What do your disk queue lengths look like, out of curiosity. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
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 />
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.
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
[email protected] When life gives you a lemon, fire the DBA.
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.
<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 />
Where is TEMPDB placed? 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.
]]>