SQL Server Performance

Average Disk Queue Length

Discussion in 'General DBA Questions' started by hftho, Sep 24, 2006.

  1. hftho New Member

    The system becomes very slow while the Average Disk Queue Length in the db server (SQL server 2000 standard edition) is maintaining at 100%. This seems to happen when several users are running reports that extract data from the sales history table (the largest tables in the database). I found that the value of the maximum Disk Queue Length indicated 143.98 and the average is 23.13. Does it means that the bottleneck of the slowness in my system is on the physical disk? Is there anyway I can improve the performance?
  2. ramkumar.mu New Member

    If Avg. Disk Queue Length counter exceeds 2 for continuous periods (over 10 minutes or so during your 24 hour monitoring period) for each disk drive in an array, then you may have an I/O bottleneck for that array. Like the Physical Disk: % Disk Time counter, if this happens occasionally in your 24 hour monitoring period, dont worry too much, but if it happens often, then start looking into finding ways to increase the I/O performance on the server, as described previously.
    You will need to calculate this figure because Performance Monitor does not know how many physical drives are in your array. For example, if you have an array of 6 physical disks, and the Avg. Disk Queue Length is 10 for a particular array, then the actual Avg. Disk Queue Length for each drive is 1.66 (10/6=1.66), which is well within the recommended 2 per physical disk.
    Before using this counter under NT 4.0, be sure to manually turn it on by going to the NT Command Prompt and entering the following: "diskperf -y", and then rebooting your server. This is required to turn on the disk counters on for the first time under Windows NT 4.0. If you are running Windows 2000, this counter is turned on by default.
    Use both the % Disk Time and the Avg. Disk Queue Length counters together to help you decide if your server is experiencing an I/O bottleneck. For example, if you see many time periods where the % Disk Time is over 55% and when the Avg. Disk Queue Length counter is over 2 per physical disk, you can be confident the server is having a I/O bottleneck.


    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  3. SQL_Guess New Member

    quote:Originally posted by hftho

    The system becomes very slow while the Average Disk Queue Length in the db server (SQL server 2000 standard edition) is maintaining at 100%. This seems to happen when several users are running reports that extract data from the sales history table (the largest tables in the database). I found that the value of the maximum Disk Queue Length indicated 143.98 and the average is 23.13. Does it means that the bottleneck of the slowness in my system is on the physical disk? Is there anyway I can improve the performance?

    As Ram pointed out, there are a few factors to take into account, but a value of 143 is almost certainly a problem. The target queue number becomes more difficult to determine when you are running on a SAN, what with the way LUNs are configured etc. Still, it does feel like your values are quite high.

    You mention this typically occurs when reports are running on the Sales History Table - how well indexed is that table? If you can reduce the amount of scanning done in the queries, and thus reduce the amount of IO operations, you may be able to ease to load on the IO sub-system.

    Panic, Chaos, Disorder ... my work here is done --unknown
  4. joechang New Member

    the disk queue rule is actually 2 per physical disk, not 2 period
    and that rule was really for benchmark systems and not real production systems

    per guess re san where it is hard to tell how many physical disks you actually have,
    just go with Avg Disk sec/read for the data disks, and sec/write for the log disks

    data: 10ms is good when the goal is highly responsive behavior, 20ms is ok for high throughput,
    at >40ms your users probably do not like you

    log: >1ms, - same as >40 for data
  5. bharath New Member

    Hi Ram,
    How do you exactly calculate the average disk queue? You have mentioned above that the Disk Queue Length/No of physical disks. How do I find out the no physical disks? Should I look in to the DeviceManager-->Disk drives and calculate the number of units present? Moreover I find that the Average Disk Queue Length Graph in my server always appears to be at 100%. Can you guide us on how to reduce this value? We are facing serious performance issues due to this.
    Many thanks in advance.
    Regards,
    Bharath Gunalan
    bharathgunalan@gmail.com
  6. satya Moderator

    Welcome to the forums.
    Rather than finding out the forumla for that if you can specify what kind of performance problems you are facing and a note of SQL configuration would help to guide you.
  7. bharath New Member

    Mine is a RAID array...In that case the average disk queue length always appears to be at 100%...Is that fine or Is there someother way to calculate it?
    Regards...Bharath
  8. moh_hassan20 New Member

    for the raid array:
    what is the block size of the disk , strip size , no of disks in the array, disk speed ?
    - is tempdp in separate disk?

    -

Share This Page