|
Hi All (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
(comments are locked)
|
|
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. Thanks Grant.. We use SQLdm from Idea that uses dm_exec_requests etc. The best example before the re-cycle took place is that SPID 70 was lead blocker with NO wait type visible. If we killed 70 then the next SPID just seem to stall. As in my previous post, the SP enforce an application lock and that is the wait type with for the rest of the SPIDs.
Oct 02 '12 at 07:23 PM
sp_lock
(comments are locked)
|
What was the blocking call from the app doing? Are you running any kind of ORM?
It is a SP that is called... No ORM insight! The same SP is called from a Non web service application.
so the app isnt releasing connections back into the pool? Any changes to the app, or updates/changes to the web server?
No change to the application in 3 months. No Windows updates applied in 2 months. Pool set to re-cycle every 27 hours.
Sounds to me like the web app didn't commit à transaction. Since app pool didn't release connection, the transaction wasnt rolled back either. (just guessing here)