August 27, 2008 at 2:20 pm
One of our sql servers 2005 had performance problem today.
All of a sudden (we did not change anything in a while) it performed very slowly and unstable (did not die but was close and this was a reason why almost all applications that called different DBs on this server generated Timeout exceptions).
From PerfMon I checked that CPU was OK (below 20%), Disk activity was normal (read/write/transfer rates and almost no queue).
The only exception was huge value of “SqlServer:Locks- Average Wait Time”- around 150,000 ms.
Eventually I solved the problem and this value came to normal for this server- around 0 (and no more applications problem).
But I am still not sure what caused this issue (could not find any error messages) and confused with the fact that Disk queue length was 0 but Average wait time was huge at the same time?!
Really appreciate any help or thoughts. Thanks,
August 27, 2008 at 2:46 pm
When you say you solved the problem, did you kill some really offensive query? 😉 Being that CPU wasn't pegged and Disk IO wasn't the issue, there isn't much else to look at, either really bad code or you have a memory issue.
Might be worth looking at this http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 27, 2008 at 3:31 pm
Thanks, David.
I did not kill any query but I did find application that caused this huge wait time. Then I updated statistics on all tables in particular DB and bingo- everything is OK. This (Update Statistics) helped but frankly I am not convinced (probably I am wrong) that bad Statistics was the main reason for the issue (we have a job that does this trick -update every 8 hours). And still my main question remains- does this combination (Disk Queue length = 0 and Locks:Wait Time = 150,000 ms) make any sense? It seems to me I do not have clear understanding what this counter:Locks-AverageWaitTime stands for? Thanks,
August 27, 2008 at 4:20 pm
Did not mention before- can DB Mirror be the reason for increased Locks: AverageWaitTime?
Thanks,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply