SQL Server Performance

Detailed Avg. Disk Queue Length issue!

Discussion in 'General DBA Questions' started by mutekinohito, Dec 13, 2007.

  1. mutekinohito New Member

    Hello everybody,
    Happy Holidays! I saw a post by evilDBA dated: "11-21-2007 4:57 PM " tha seems similar to what's happening to me, but my scenareo is a bit different.
    The Avg. Queue length is usuall close to 0.02, but sudden spikes occur (for about 15-20 sec). The server is a dedicated machine. 1.5GB RAM (total consumption of 710MB including OS - does not change during spikes). Only one user is connected. No scheduled tasks for the SQLAgent. No database mirroring. No locking issues. No alerts or warnings. No DB or Log growth occures.
    I cheked a number of counters and this is the only one that shows values beyond recomended.
    The task going on during this situation is a single SP (no other SP or queries are executed) that is executed hundreds to thousends of times and that does the following:
    Begin Tran
    Insert into Table1 (params) --> One row only
    Update Table2 (@@Identity, params) -->One row only (@@Identity results from Insert in table1)
    Updates Table3 (params) -->One row only
    If no erros then Commit Tran other wise Rollback Tran.
    When monitoring the Profiler, the "stall" is never in the same part of the SP. Sometimes it shows at the Insert, sometimes at the Commit Tran, and a fewer times at the 2nd Update. I executed the task without table indexes and the performance is, of course, very poor. With indexes, is about 200 times faster.
    Most times the spike happens after a thousand or so executions, but every now and then after only a few (about 100) or even after 3000. I am unable to detect any patterns. The spike happens only once per batch, ei. if I am processing 1000 or 6000+ items; the stall never occurs twice!
    Please, oh mighty SQL professionals, can anybody enlight me?
    Thanks for your time and interest in my call for help.
  2. Greg Larsen New Member

    What else in on the same disk as the database? Is the page file on that disk?
  3. mutekinohito New Member

    Please forgive me if my answer does not make sense. I do not have a formal education on this matters.
    If you are refering to the Page File for the virtual memory, I have tried both scenareos. In the same particion of the Drive and in a different one. No chages occured, Situation remained the same.
    Along with the Avg. Disk Queue Length counter, I have also monitored the following:
    Avg. Disk Sec/Read : min. 0 | max. 0.012 | avg. 0.006
    Avg. Disk Sec/write: min. 0 | max.0.036 | avg. 0.008
    Page Splits/sec: min. 0 | max. 37.0 | avg.1.298
    Avg. Disk Write Queue Length: min. 0 | max. 1.384 | avg. 0.234
    Paging File Object:: %Usage: all 1.131
    Hope this help you to help me :)
    thanks.
  4. Greg Larsen New Member

  5. mutekinohito New Member

    Thank you for the Link Greg,
    I will look into it and try to follow its hints.
    Have a good day.

Share This Page