High disk queue length

  • Hi,

    We have clustered sql server 2005 instance. We have a full backup every and every 8 hrs log backup.

    We a Spot light monitoring tools to monitor Sql server.

    We are frequently getting this Log flush wait time alarm as below:

    1.

    The database WSS_Content has a high Log Flush Wait time (313 ms).InformationDatabases - Log Flush Wait Time Alarm

    Is this alarm is due to taking log backups only 2 times a day?what this this log flush wait time?

    2.We also getting High disk queue length on C drive. Iam confused why we are getting high disk queue lenght on C drive? I went through event viewer logs and found that Anti virus is started running at the time we are getting this alarm on C drive. What we need to do to aviod getting this alarm on C drive?

    Alarm raisedPhysical disk #0 C: containing paging file(s) 'C:\pagefile.sys' is experiencing a high rate of I/O (100% busy) unrelated to paging.InformationPaging Disk Activity Alarm

    3. We have Z drive, which used for only as backup drive for the databases. All full backups and log backups are stored on Z drive.We are also getting High disk queue lengthon Z drive too as below.

    Alarm raisedPhysical disk #20 Z: containing paging file(s) 'C:\pagefile.sys' is experiencing a high rate of I/O (96.4% busy) unrelated to paging.InformationPaging Disk Activity Alarm

    we have D and E drives for mdf and ldf files. My question is Why we are getting High disk queue lenght on C and Z drives But not on D and E.

    Plz advice me....

  • What's your disk layout? (No of drives, raid level, DB-related content)?

    SAN or local storage?

    What do the following two perfmon counters look like (broken down by drive)

    Physical disk\ avg sec/read

    Physical disk\ avg sec/write

    Why only log backups twice a day? It means you could lose half a day's work if the server fails?

    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
  • Thank you

    We have Raid 10 SAN storage drives.

    Disk lay out as below:

    D:datafiles

    E:Secondary data files

    F:log files

    Z:Backup Drive

    and the instance is clustered.

    I did not understand why Iam getting high disk Queue length on C and Z drives.

    and what is meant by database having High log flush time. Where can I check in SQL Server about this high disk queue length other than monitoring tool?

    I will set up the disk counters tommorow and will post the readings...

    Why only 2 backups daily? Because, currently there is not much activity going on.In coulpe of weeks we are going to schedule log backup every hour.

    Thanks for your help

  • Hi,

    What do the following two perfmon counters look like (broken down by drive)

    Physical disk\ avg sec/read

    Physical disk\ avg sec/write

    Drives:

    C->OS

    D->datafiles

    E->Secondary files

    F->Log files

    G->TempDB

    Z->Backup

    I attached the excel sheets with above mentioned perfmon counters.

    I would like to know from the readings how can we know that disks are performing at optimum level?

    Please give me your suggestions

    Thank You

    Madhu

  • Your disk stats are high. Both sec/read and sec/write should be around 10ms. On C, your sec/read reaches 0.5, on D, G and Z the sec/write reaches 0.1. Both of those are far too high.

    The spreadsheet doesn't contain stats for E and only has the read for F, so I can't see if these are also affected.

    Are the SAN luns composed of dedicated disks (not shared with other luns)?

    I would suggest more drives, spread the load out further.

    Also, speak to the storage people and see if they have noticed any performance problems. With a SAN, it could be anything from the switch, the cache, the controllers to the disk. The storage people should be able to pinpoint the problem better.

    Disk queue length is near-impossible to interpret on a SAN, because of all the caching, etc, etc. Ignore it.

    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
  • First, you should upgrade your Spotlight - the fact that you see "disk queue length" means you are not on the current version. The metric they use now is I/O Wait, which is much more meaningful because - as Gila said - SAN's and disk configurations render that statistic almost meaningless.

    The paging alarm just means that the drive your pagefile is on is getting hit rather hard, but not the pagefile itself. It's a "heads up" - nothing more really. Maybe you were doing a file transfer or zipping up some files - something along those lines.

    Since you are using Spotlight, you don't have to go to perfmon - you can get your physical reads/writes right from Spotlight. Go to the server in Spotlight, right-click on the drive and check out the Logical Disk Activity - might save you a little time.

  • madhu-686862 (2/17/2009)


    Thank you

    We have Raid 10 SAN storage drives.

    Disk lay out as below:

    D:datafiles

    E:Secondary data files

    F:log files

    Z:Backup Drive

    and the instance is clustered.

    I did not understand why Iam getting high disk Queue length on C and Z drives.

    and what is meant by database having High log flush time. Where can I check in SQL Server about this high disk queue length other than monitoring tool?

    I will set up the disk counters tommorow and will post the readings...

    Why only 2 backups daily? Because, currently there is not much activity going on.In coulpe of weeks we are going to schedule log backup every hour.

    Thanks for your help

    Unfortunately that information is not nearly complete.

    How many spindles in the RAID 10 group?

    Are there different RAID 10 physical LUNs for each logical drive?

    Do other things sit on those same spindles?

    How many and what type of HBAs connect server to SAN?

    iSCSI or FC? What speed?

    I HIGHLY recommend you get a performance tuning professional on board for a few days to give your entire sql server infrastructure a performace/system review.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Please note, the original post is almost 3 years old and the OP has not logged in for over a year.

    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
  • Aha - newbie mistake friends. I was looking at your last login date, not the thread date.

    At any rate, it still pertains to Spotlight users today and the question does still come up in other forums.

  • Hey Grasshopper,, your post of 3 years ago helped me today because I TOO have spotlight and was researching same alert. THANKS,,, SO very helpful and pertinent indeed

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply