SQL Server Performance

Average Disk Queue Length

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by linus516, Nov 7, 2007.

  1. linus516 New Member

    hi i find that my Server with SQL Server 2000 always has very high Average Disk Queue Length..............it is over 50 and sometimes over 100. So that when we are running queries, it takes very long time to complete.
    So any suggestion to improve the situation?
    Thousand thanks
    Linus
  2. satya Moderator

  3. evilDBA New Member

    Hi, all
    My avg Queue length is usuall very low, <1 all the time, except sudden spikes. These spikes can trigger a real disaster (domino effect). May be you have some ideas. I provide you 3 samples:
    Time / Avg Read Queue length / Avg Write Queue length


    10/23/2007 12:30:14.994 0.05 0.26
    10/23/2007 12:30:29.994 0.03 0.39
    10/23/2007 12:30:44.993 0.01 0.28
    10/23/2007 12:30:59.993 0.05 0.25
    10/23/2007 12:31:14.993 0.17 59.49 (!!!)
    10/23/2007 12:31:29.992 0.24 2.74
    10/23/2007 12:31:44.992 0.05 0.35

    10/23/2007 12:03:15.025 0.05 0.22
    10/23/2007 12:03:30.025 0.04 0.30
    10/23/2007 12:03:45.024 16.13 0.67
    10/23/2007 12:04:00.024 182.18 6.44
    10/23/2007 12:04:15.024 57.05 2.65
    10/23/2007 12:04:30.023 6.06 0.23
    10/23/2007 12:04:45.023 0.05 0.30
    10/23/2007 12:05:00.023 0.04 0.26
    Or even during the night time:


    10/22/2007 23:57:15.765 0.70 0.57
    10/22/2007 23:57:30.765 0.96 0.19
    10/22/2007 23:57:45.764 2037.64 0.95
    10/22/2007 23:58:00.764 3012.34 0.89
    10/22/2007 23:58:15.764 1257.45 5.50
    10/22/2007 23:58:30.764 2473.33 1.31
    10/22/2007 23:58:45.763 2909.65 0.89
    10/22/2007 23:59:00.763 1414.20 0.42
    10/22/2007 23:59:15.763 154.27 5.46
    10/22/2007 23:59:30.762 2.32 0.37
    10/22/2007 23:59:45.762 1.02 0.39
  4. Luis Martin Moderator

    Any maintenance plan running it those times?.
    Is SQL server dedicated?
  5. evilDBA New Member

    Maint.plans perform nightly backups, there are also trn.log backup every hour I guess.
    SQL server is dedicated. Database mirroring is ON.
    This is really strange: server is under heavy load but Avg Queue is <1, then suddenly there is a spike, and it is collapsing and slowly recovering in 10-20 minutes...
  6. Luis Martin Moderator

    Run profiler during those hours to find long queries and/or sp.
  7. evilDBA New Member

    Thank you. I will check it tomorrow, but I tried once and I havent found anything special at the time of these strange peaks.
    Can it be... say, CHECKPOINTs ?
    I also have another question. Write Queue and Read Queue - are physically separate or not? If Read Queue Length =3 and Write Queue length = 7, is it:
    • Read Queue: R R R
    • Write Queue: W W W W W W W
    Or
    • Common Queue: R W W W R W W R W W
    Lets say we have a CHECKPOINT, SQL server flushes all dirty pages and generates, say, 1000 write requests. With 5 ms per write it gives 5 seconds. Now imagine that right after the checkpoint we have one simple read request. Is that poor read request waiting 5 seconds for 1000 write requests to be completed in the same queue, or it is passed somehow 'before' write requests?

Share This Page