SQL Server Performance

physical disk :Avg.Disk queue length

Discussion in 'Performance Tuning for DBAs' started by NewDBA, Oct 24, 2003.

  1. NewDBA New Member

    Hi !
    I am collecting this counter as well as others using
    performence monitor

    I know that physical disk:avg disk queue length never should be bigger then 2 per disk
    Now i am wondering if my data (mdf,ldf) is being kept only on one drive
    Do i have to tace into considerartion others?

    i have 4 drives

    and sometimes avb.disk queue(total) goes up to 8 is that something to invastigate?
    or this is okey?
  2. Luis Martin Moderator

    Physical Queue is over 2 for long time, 10 minuts or more?
    Queue total: same question.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. joechang New Member

    the average disk queue should be less than 2 per actual disk, not necessarily never, in fact it can burst as high as several hundred during checkpoints,

    I am not inclined to set a hard number on either the queue or duration, on the thinking that it is really a judgment call. sql server may dump a load of disk IOs, so there is no easy way to guarantee the queue will be less than 2/disk.
    It really is the ability of the disk system to process the outstanding requests quickly. If the queue jumps, one would like to see a quick clearing of the queue
  4. NewDBA New Member

    Thank you but actually i was asking if i have to look at all drives or only on that holding the data (mdf,ldf)?
  5. satya Moderator

    Its necessary to check the disk holding .MDF & .LDF files.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. NewDBA New Member

    THANK YOU
  7. NewDBA New Member

    I am also not sure if this is okey:

    I collected physicaldisk:%disk time during couple of days and notice that valued there would be like this during like 20 min at the middle of the day

    35.15444699
    346.8391533
    732.8421681
    2.64263303
    2.907129939
    2.302014733
    5.690481086
    507.1650232
    3.102909192
    3.736353902
    3.195794051
    817.3138379
    1.37064159
    633.3904984
    2.182851514
    1.978517605
    1151.305497
    1.695320746
    1.336838025
    542.2080446
    12.99697185
    228.0945295
    4.549434747
    411.8101916
    208.6255572
    1078.658681
    1404.970601
    2.777355502
    3.568689506
    2.690905888
    1.591463755
    614.0977082
    2.198081054
    2.796216522
    5.446923527
    3.57819483
    765.4298031
    326.2525327
    2.705199415
    3.1717983
    3.309912987
    1.571566058
    67.33584523
    1685.572359
    666.2808927

    those values goes like 3-4 per minnutes.
    I know that if %disk time is constantly high during 10 min than it's require invastigation
    What about those counters ?Are they point to the problem

  8. satya Moderator

    If the PhysicalDisk: % Disk Time counter is high (more than 90 percent), check the Physical Disk: Current Disk Queue Length counter to see how many system requests are waiting for disk access. The number of waiting I/O requests should be sustained at no more than 1.5 to 2 times the number of spindles making up the physical disk.

    Most disks have one spindle, although redundant array of inexpensive disks (RAID) devices usually have more. A hardware RAID device appears as one physical disk in System Monitor; RAID devices created through software appear as multiple instances.

    Use the values of the Current Disk Queue Length and % Disk Time counters to detect bottlenecks within the disk subsystem.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. Twan New Member

    Also if you are looking at the _Total instance, then you may want to split it into the actual disks (although your original post suggests that you may only have one disk)... This way you may be able to ignore certain known activities such as performance monitoring, profiling, paging, etc.

    Also check that pages/sec is close to 0 on average. If not, then although your disks are busy, it is due to memory shortages.

    Cheers
    Twan

  10. NewDBA New Member

    Memory<img src='/community/emoticons/emotion-4.gif' alt=':p' />ages/sec =average=10.09923107 <br />I collected those data for a day<br /><br />Also i realy have 4 drives but only one hold mdf and ldf files.<br />so i will monitor disk that hold the db files and come back here <br />
  11. satya Moderator

    Monitor Current Disk Queue Length and % Disk Time counters specifically.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  12. NewDBA New Member

    i Understand that this is too short periond of time for making conclusions
    but after monitoring for 30 min i noticed that when disk writes goes to 200-300
    the Curent disk queue lenth remains =0,sometimes it jumps to 4

    but once %disk READ time jumped to 400 and in that moment curent disk queue lentth was =37

  13. Twan New Member

    memory pages/sec seems high...

    can you look at the SQL counters buffer page reads/writes? If these are also averaging 10/sec then it sounds like a memory shortage rather than a disk constaint

    also check SQLServer Memory Manager total and target server memory. These should be roughly the same

    another one is SQL counter buffer Page life expectancy. If this is low and or fluctuates a lot, then consider running profiler to look for long running queries that might be using large table/clustered index scans, flushing out SQL's cache

    What else are you running on the server apart from SQL Server? If you are running COM, IIS, file serving, etc. then consider setting SQL Server's max memory to leave 50-100 MB for other things...?

    Cheers
    Twan

    It is fairly rare to have a disk problem, usually the problems are application or memory related, but they appear like disk problems
  14. NewDBA New Member

    thank you i will cath them tomorrow.
  15. satya Moderator

    Just keep on eye on event viewer also for any such h/w resource issues.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  16. NewDBA New Member

    Today monitoring shows that
    SQLServer:Buffer ManagerPage life expectancy=
    max=36 614
    min=54
    avg=16 154
    where majority= near or above 30 000

    then for acouple a min = 1 600 starting from 54


    SQLServer:Buffer ManagerPage writes/sec
    avg=9.21
    majority=0
    couple of spikes of 200 close to each other


    SQLServer:Buffer ManagerPage reads/sec
    avg=23.7
    lot's of 0
    SQLServer:Memory ManagerTarget Server Memory always exactly the same like
    SQLServer:Memory ManagerTotal Server Memory


    sql has 4 processors
    physical memory=2560
    virtual memory=4000-4000
    Microsoft SQL Server 2000 - 8.00.760




  17. satya Moderator

    No issue with Buffer ManagerPage life expectancy counter.
    About Page Writes/sec you need to look for information about which Process is causing such a spike.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  18. Twan New Member

    Do page reads + page writes /sec follow a similar pattern to memory pages/sec? If not then SQL is not the process causing the paging

    Cheers
    Twan

Share This Page