SQL Server Performance

Avg. Disk Queue Length

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by jn4u, Oct 20, 2008.

  1. jn4u Member

    We have EVA4000 storage Network. The configuration is like this; 1 disk Group, 2 Virtual disks in vraid1 and 26 physical disks in data group. Is it correct when I monitor the Avg. Disk Queue Length I have to divide the value with 26?
  2. moh_hassan20 New Member

    [quote user="jn4u"]Is it correct when I monitor the Avg. Disk Queue Length I have to divide the value with 26?[/quote]
    That is may be valid for Raid Array , but for SAN storage , The SAN switch and disks involved in Performance.
    So check HP SAN Docs.
  3. moh_hassan20 New Member

    review: http://technet.microsoft.com/en-us/magazine/cc162466.aspx
    That is an excerpt from the article:
    Q What should my average disk queue lengthbe? For example, if I have 20 separate physical spindles off of StorageArea Network (SAN) with RAID 01 configuration, how do I calculateaverage disk queue length? Is it Avg. Disk Queue Length/20 or Avg. DiskQueue Length/2?
    A First ofall, you should look at the disk latency before wasting any time onaverage disk queue length in a SAN environment. But it really doesdepend on what you are trying to figure out. You'll see why in a moment.
    Theexplanation of this counter (from Perfmon) is "Avg. Disk Queue Lengthis the average number of both read and write requests that were queuedfor the selected disk during the sample interval." This is either aphysical disk or logical disk counter, so the number that you getdepends on how the underlying storage is presented to the operatingsystem.
    Let's look atyour case. You have 20 spindles in a RAID 01 configuration, which meansthey are striped and mirrored (or mirrored and striped depending on howyou read 01 or 10). The key point about your storage array is thatthere are 10 spindles in the stripe set.
    ButI'm missing essential information, such as what the stripe size is, howbig your writes are, and what kind of I/O you're issuing (read, write,sequential, or random).
    Ignoringthe missing information for now, if the Avg. Disk Queue Length says 10,then the OS queued 10 I/Os to the disk array. Theoretically, that couldbe one I/O to each of the 10 mirrored sets in the stripe, or it couldhave been 10 I/Os all to the one disk. You have no way of knowing whichit was.
    This is where themissing information comes in. Let's assume your stripe size is 64KB,your write size is 8KB, and you're going to do a whole chunk ofsequential writes. This is a typical scenario for SQL Server storageactivity. In this case, there is a good chance that eight of the I/Osall went to the first disk, and the next two I/Os went to the nextdisk. So, if you're trying to work out the disk queue length per diskfor this scenario, it is 8 for the first disk, 2 for the second disk,and 0 for the remaining eight disks in the array.
    Nowlet's change the scenario to a theoretical stripe size of 8KB and awrite block size of 64KB-and keep the disk queue length at 10. In thiscase, each 64KB block gets spread over 8 disks, so one I/O is writtento 8 disks and the 10 queued I/Os are spread across 80 disk writes overall 10 disks in the array. If you're trying to calculate the disk queuelength per disk in the array, it's going to be 8 for each disk in thearray.
    Let's be realisticand add another level of uncertainty into the picture. In most cases,your SAN storage is going to be connected to the server using one ormore HBAs in the server, a number of fibers to connect the HBA to theSAN, a number of ports on the front end of the SAN, and perhaps somekind of fiber switch in the fabric between the server and the SAN. Thenwe get into the internal architecture of the buses inside the SAN, andhow the disks are connected to the ports on the front of the SAN.
    Anyqueuing you are seeing reported in Perfmon can be a symptom of highlatency or queuing at any of these points between where the OS measuresits disk queue length and the surface of the disks. This is why youshould look at the latency and base any decisions on that counterrather than the average disk queue length.
  4. jn4u Member

    Yes found that also, will collect a new log and look for Avg. Disk Sec/Read and Write instead. MS told me that
    10ms - very good
    10-20ms - normal
    20-50ms is slow
    > 50 ms is problem...


  5. satya Moderator

    How the disks are configured?
    For READ or WRITE performance?
  6. jn4u Member


    I have no access san monitors... Just Perfmon.... This is the values during a days activity.

    Memory Available MBytes = 100
    Pages/sec = 37.889
    Database PhysicalDisk % Disk Time = 151.5
    Avg. Disk sec / Read = 0.035 for database
    Avg. Disk sec / Write= 0.013 for database
    Avg. Disk sec / Read = 0.010 for Log
    Avg. Disk sec / Write= 0.000 for Log
    % Processor Time = 798 total of 8 cores
    Processor Queue Length = 0
    Buffer cache hit ratio = 99.771
    General Statistics User Connections = 253 (x64)

    %Disktime is a useless counter on high-end IO systems. It was designed forsequential command IO systems? Is this true and the 35ms for read preffor the database can be a problem?
  7. Saurabh Srivastava New Member

    memory available is just 100MB. If this value is correct its a red alert. Rest of the values seems ok.

Share This Page