SQL Server Performance

PerfMon: Current Disk Queue Length

Discussion in 'Performance Tuning for DBAs' started by dfr031260, Aug 26, 2003.

  1. dfr031260 New Member

    I have perfmon on one of my SQL Server boxes. Both the Current Disk Queue Length, and Avg. Disk Queue Length are pegged. What causes this, and how do I remediate this problem.

    David

    Peace
  2. gaurav_bindlish New Member

    Check if there is any problem with the disk. Check Eventvwr fro any visible errors.

    Is the disk access through explorer slow? If yes, check if there is no free space left on the hard drive. Also run defragmentation and other hardware diagnostic utilities to eliminate disk as the problem.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. bradmcgehee New Member

    Disk queues occur when the disk subsystem can't keep up with SQL Server's read/write requests, assuming there is no physical reason (bad disk or drivers) causing this issue.

    Your choices to remedy this problem include:

    --Tune queries to reduce disk I/O
    --Spread out I/O activity over the day
    --Get faster disk subsystem
    --Anything else that can reduce disk I/O

    By the way, what do you mean by pegged? Generally speaking, a length of 2 times the number of physical drives in your system should be the maximum you should see. Anything over this value generally means an I/O bottleneck.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  4. dfr031260 New Member

    Nothing in the eventlog.

    I am having one of the LAN guys check the disk.


    quote:Originally posted by gaurav_bindlish

    Check if there is any problem with the disk. Check Eventvwr fro any visible errors.

    Is the disk access through explorer slow? If yes, check if there is no free space left on the hard drive. Also run defragmentation and other hardware diagnostic utilities to eliminate disk as the problem.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard


    Peace
  5. dfr031260 New Member

    This SQL Server is running a Mail Tool called UnityMail. It has a reputation around my office as being a bit of a bear. So tuning the queries is not possible.

    After doing a trace on the SQL Server, I found that this software does almost all of its' queries as sqlexecute. I maybe wrong, but isn't that a horrible way to query a database?

    This is a production box, so defragging the disk is going to be a trip(trying to get a service window I mean).

    David


    quote:Originally posted by bradmcgehee

    Disk queues occur when the disk subsystem can't keep up with SQL Server's read/write requests, assuming there is no physical reason (bad disk or drivers) causing this issue.

    Your choices to remedy this problem include:

    --Tune queries to reduce disk I/O
    --Spread out I/O activity over the day
    --Get faster disk subsystem
    --Anything else that can reduce disk I/O

    By the way, what do you mean by pegged? Generally speaking, a length of 2 times the number of physical drives in your system should be the maximum you should see. Anything over this value generally means an I/O bottleneck.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com

    Peace
  6. bradmcgehee New Member

    Even though you can't tune the queries, you may be able to add needed indexes. Get a good trace and use the Index Wizard for a start, then identify all long running queries, and one at a time, analyze them for missing indexes.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  7. dfr031260 New Member

    Already have a good trace. So I am going to try that, and let you know what shakes out.

    Thank you so much for your input. It is good to know you are not alone out here.

    David


    quote:Originally posted by bradmcgehee

    Even though you can't tune the queries, you may be able to add needed indexes. Get a good trace and use the Index Wizard for a start, then identify all long running queries, and one at a time, analyze them for missing indexes.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com

    Peace
  8. Luis Martin Moderator

    Brad sed: "By the way, what do you mean by pegged? Generally speaking, a length of 2 times the number of physical drives in your system should be the maximum you should see. "

    So, what is the Average Disk Queue?. Current may be show you 34 or any data, but Average is more than 2?.

    Luis Martin
  9. nite_eagle New Member

    In this world of RAID and SAN, what is the definition of a "physical" disk?

    If I have a RAID 10 array of 10 disks, 5 pairs striped and mirrored, what is the correct threshold for Current Disk Queue Length 20 or 10? What about a SAN which could place the data on many different drives?

    Thanks,
    Norman
  10. gaurav_bindlish New Member

    For the configuration mentioned the threshold is 10 as there are 5 available disks to write.

    For SAN if 10 disks are ivolved, the threshold is 20.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  11. joechang New Member

    on the matter of pegged, the default scale for disk queue depth is 100, so a steady value of >1 will give the impression of being "pegged"
    physical disk still means what it always was, the disk drive itself.
    however, for queue depth as read from the OS, in RAID 10, each write corresponds to 2 physical disk ops and RAID 5 to 4, hence i would use the following:
    Reads Writes
    RAID 0 1 1
    RAID 1 or 10 1 2
    RAID 5 1 4

    that is, multiply any write values by 2 for RAID 10 and 4 for RAID 5

Share This Page