SQL Server Performance

Extreme high values - Logical Disk:Avg Disk Queue Length

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by anilksharma99, Mar 28, 2008.

  1. anilksharma99 New Member

    I wanted to check what does the values below would comprehend for Logical Disk:Avg Disk Queue Length (for data file logical drive)
    I understand that the average value should not be more than 10 (8 physical disk spindles on the array thus 8+2) but could not digest the above high values for the average. I haven't experienced such high values for this counter. This is a datawarehouse application and above counters have been collected for duration of 40 hours run in test environment. Am I missing anything in regards to some server setting. Any pointers would be helpful.
    SQL Server 2005 SP2, Windows 2003
  2. ghemant Moderator

    Hope you have updated drivers. Refer an article http://www.microsoft.com/technet/pr...serv/reskit/prork/pree_exa_swlz.mspx?mfr=true where it says, and this could be the case:
    When working with the disk-time or disk-queue length counters, be aware of the following limitations that might yield unlikely counter values.
    •The % Disk Read Time and % Disk Write Time counters can exaggerate disk time. This is because they report busy time based on the duration of the I/O request, which includes time spent in activities other than reading to or writing from the disk. It then sums up all busy time for all requests and divides it by the elapsed time of the sample interval. If multiple requests are in process at a time, the total request time is greater than the time of the sample interval; as a result, reported disk utilization can exceed actual utilization.
    •Counter values that report sums can be misleading for multidisk systems. When you look at the _Total instance for the % Disk Time or disk-queue counters on a multidisk system, the counters report values totaled for all disks and do not divide these totals over the number of disks in use. Therefore, in a system with one idle disk and one disk that is 100 percent busy, it can appear as if all disks are 100 percent busy.
    The following sections describe how you can use disk-monitoring counters to observe available space on the disk and to observe the efficiency of disk operations as you become acquainted with your systems disk performance.
  3. satya Moderator

    What kind of database activity you have observed?
  4. anilksharma99 New Member

    Thanks much Hemant and Satya!
    Hemant: I am going through the article and implement in the environment. From the initial reading though, not sure if it is relevant to Windows 2003 also (along with Win 2000).
    Satya:I haven't observed any unusual activities (blocking/deadlock). Also being an ETL infrastructure, the environment doesn't involve any concurrent activites to any object. At one time, only one fact table gets loaded with absolutely no other queries running on it. Not sure if this is what you meant.

Share This Page