• This piece is just wrong.

    If % Disk Time is consistently over 85%, the disk system is stressed. Avg. Disk Queue Length and Current Disk Queue Length refer to the number of tasks that are queued at the disk controller or are being processed. You want to see a counter value of 2 or less. If you use a RAID array where the controller is attached to several disks, you want to see counter values of 2 times the number of individual disks or less.

    Firstly, with the number of layers between SQL and the disks with SAN or similar, it's near-impossible to diagnose a queue length as good or bad.

    The second reason is that there are operations in SQL that intentionally drive the queue length high, that submit large numbers of IOs and then carry on working while they wait.

    Queue length just isn't a useful metric these days.

    The problem with disk time is that it is not a value between 1 and 100. It can go to several times 100, depending on what's beneath that physical drive. A value of 500 may be perfectly fine.

    Rather stick with the latencies (Avg sec/read and Avg sec/write) for diagnosing disk-related bottlenecks.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass