x

Mass blocking for no reason

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

more ▼

asked Oct 01, 2012 at 07:42 AM in Default

sp_lock gravatar image

sp_lock
9.3k 26 28 31

What was the blocking call from the app doing? Are you running any kind of ORM?
Oct 01, 2012 at 10:14 AM Kev Riley ♦♦
It is a SP that is called... No ORM insight! The same SP is called from a Non web service application.
Oct 01, 2012 at 12:26 PM sp_lock
so the app isnt releasing connections back into the pool? Any changes to the app, or updates/changes to the web server?
Oct 01, 2012 at 12:38 PM Fatherjack ♦♦
No change to the application in 3 months. No Windows updates applied in 2 months. Pool set to re-cycle every 27 hours.
Oct 01, 2012 at 12:50 PM sp_lock
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)
Oct 02, 2012 at 06:31 AM Magnus Ahlkvist
show all comments (comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
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.
more ▼

answered Oct 02, 2012 at 11:31 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

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, 2012 at 07:23 PM sp_lock
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x599
x39
x26

asked: Oct 01, 2012 at 07:42 AM

Seen: 793 times

Last Updated: Oct 02, 2012 at 07:23 PM