(I know there is always a reason)
I may get asked to post this elsewhere, but I thought I would put this the DBA's first.
Last week we have the same issue as one of my previous posts. For around 5 minutes our SQL processes just seem to "chugging" along. There were no CPU spikes more than 2% per thread (thanks to @kev riley and @fatherjack). Also, all memory counters (hit ration etc) were within our normal threshold).
Now, here is the interesting part. I noticed that some of the lead blockers came from one of our application via webservices and not the direct calls to SPs. Then out of thin air I decided to try re-cycling the application pool within IIS for the for-mentioned webserivce..
BAM... All resolved!
Has anyone seen this before..? None of my DEVs can offer any insight nor has my "insert prefered search engine" 'in
All it takes to get a situation like this is a single process holding a lock on a resource needed by other processes. You need to look at sys.dm_exec_requests when this occurs in order to determine what process is blocking all the others. You can determine what that process is doing by looking at the queries associated with it through sys.dm_exec_sql_text. That's the only way to know for sure.
answered Oct 02, 2012 at 11:31 AM
Grant Fritchey ♦♦