Avg. Read Queue Length | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Avg. Read Queue Length

Hi ,
How to measure if we are using SAN disk( We are connected to EMC storage) . Avg Read queue length. It should be same as MS recommended ( <2 ). When I spoke with our SAN group they told me that each san Disk having min 4 spindles and each disk is combination of 4 physical disks. So, he told me that IF Avg read disk queue lenght more than 20s or 30s you need to worry otherwise no need to worry. Is it true? Is there any other questions I pose them ?
thanks
it is really amazing how careless people are in citing rules without critical elements the rule should be: queue depth <2 per physical disk for low disk latency operation that is: if this is a tranactional server, and users are waiting for each set of query response, it is important that disk latency be low. if this is a batch or DW/DSS server, then the above does not apply
a decent storage system should be able to deliver more IOPS as you pile on the queue
this is because the disk/storage system can reorder requests to reduce random access time per request,
so in Batch/DW where throughput is king, pile on the queue to get the most IOPS back to queue depth,
since the rule applies per physical disk
and the number of physical disks per LUN varies
I suggest you not bother setting rules on queue depth because you do not know how to correct for the LUN-phys disk, unless you communicate with the SAN engineer
and this seems to be a verboten (frank-help on the umlat?) at many companies i say this (disregarding queue) is reasonable
because as you recall,
the original purpose of the rule was for good low latency disk operation do you recall a counter in Physical Disks:
Avg Disk sec/Read?
that is the disk latency
if you can observer disk latency directly,
why bother with an indirect measure? a. good disk latency for random IO is < 8 ms,
which will usually correspond to queue depth 1 per physical.
b. ok latency for random IO is 15ms, which should map to Q2 / phys disk
c. 20-40ms is bad, and your users probably think you are incompetent, and/or your storage vendor screwed you
d. >40ms sustained, you should get a resume (CV for europeans) ready quick note- the above applies to transactional servers
and the sustained values
short high peaks in latency are inevitable due to the characteristicsc of most transaction DB engines.
unless you want to spend a lot of money (HW + expertise) on the matter of peaks, disregard the peak value,
watch the duration of the spikes

" it really amazing how careless people are in citing rules without critical elements"<br />[<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All in Love is Fair <br />Stevie Wonder<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
i think people like rules because it is considered a substitute for intelligence.
when they have a list of rules to follow, intelligence is not required or even desired. many people also think blind adherence to rules absolves them of responsibility my thoughts:
if you are paid (very/exhorbitantly/obscenely) well (relative to your location),
then you should be (very) intelligent and take responsibilty if you are not, you can operate in the rule mode
thats all that is expected of you so if any of you see the disk queue rule without the specification of per physical disk
the writer of the rule is an idiot

hi Joe,
Thanks for the explanation . Here my environment in DW .anyways I will monitor those counters.
quote:Originally posted by joechang
back to queue depth,
since the rule applies per physical disk
and the number of physical disks per LUN varies
I suggest you not bother setting rules on queue depth because you do not know how to correct for the LUN-phys disk, unless you communicate with the SAN engineer…

I’d like to offer further clarification. You do not need to know actual number of spindles to interpret AVG DISK QUEUE for a "single device seen as a physical drive at the host". In other words, that AVG DISK QUEUE divided by physical spindles makes more sense when you see global aggregate AVG DISK QUEUE for the entire system and then divide by the number of "drive letters" you’ve configured. Consider this: do 100% solid-state "disk" drives still have a concept of avg queue depth? They do. How can that be when there are no "spindles" in the drive?
quote:
if you can observer disk latency directly,
why bother with an indirect measure?

Both ms/read and avg queue are useful. What you call "indirect" measure can sometimes be interpeted more intuitively (watching changing queues in realtime) for workload behavior as opposed to monitoring response times (watching changing ms/read values).
quote:
a. good disk latency for random IO is < 8 ms,

With today’s modern disk frames EMC/Hitachi/IBM, I would hope for <5ms.

you can get 6ms on a bare disk
<5ms by using just 1/2 of a disk why insert a grossly expensive san that does not contribute to performance?
quote:Originally posted by joechang
why insert a grossly expensive san that does not contribute to performance?

Nobody’s inserting a SAN out of nowhere just to create an argument. The context of this thread seemed to be EMC. Excerpt of the original question: "How to measure if we are using SAN disk( We are connected to EMC storage)" Therefore, if SQLDBAS is using EMC by choice/bribery/ignorance/loyalty/etc , and it happens to be a modern EMC frame, then he should expect <5ms.

In System Monitor it will always show the drive as if it where only one physical disk drive regardless if in reality it contains multiple disks on SAN. In my opinion this is misleading. In this situation monitoring this counter will always require comunication between DBAs and Storage teams. Raulie
5.5 ms happens to be the full disk random access time of current generation 15K drives the SAN has nothing to do with 5ms

]]>