SQL Server Performance

Avg. Read Queue Length

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by SQLDBAS, May 15, 2007.

  1. SQLDBAS New Member

    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
  2. joechang New Member

    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
  3. Luis Martin Moderator

    " 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 />
  4. joechang New Member

    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
  5. SQLDBAS New Member


    hi Joe,
    Thanks for the explanation . Here my environment in DW .anyways I will monitor those counters.
  6. JWSQL New Member

    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.
  7. joechang New Member

    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?
  8. JWSQL New Member

    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.


  9. Raulie New Member

    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



  10. joechang New Member

    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

Share This Page