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.
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.
Looks like you have some page slitting going on there. You might want to read this article to see if it helps your out: http://www.sql-server-performance.com/faq/reduce_page_splits_p1.aspx