SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Kev Riley / Blocking / Latest Posts

RE: Blocking

L' Eomot Inversé — Thu, 23 Dec 2010 16:45:43 GMT

Nice straghtforwards question.I found this one easy to answer because years ago I wrote some code to detect undesirable blocking behaviours in a particular system that had a couple of extremely buggy apps with rotten embedded SQL and kill off the root blocker; this was actually safe given the overall system design, although it wasn't a desirable things to do things, and worked fine for some time; then along came SP4 and it started killing things too often - which turned out to be this problem. Very easily fixed once I discovered that this was expected behaviour, but a week or so of very real worry before I discovered what was going on. (Of course we planned a rewrite of those apps, including restricting them to use only stored procedures, no other db access, but it took a long time to get a plan approved and a long time to get a decent implementation; but eventually that block detector ceased to be needed.)

RE: Blocking

ezlikesundaymorning — Fri, 16 May 2008 09:04:18 GMT

While that is true, #1 is partially true, though not infinite, #2 is true it is the expected behavior, I guess that parallism is not correct I did not really notice there was anything in there about what type, #4 is definitely not correct. The real reason we see this type of behavior which is what I was getting at is because of the attempt to keep all of the IO asynchronous. This is really an expected result from sp_who but the cause is IO. This can be aleviated in some cases but not all. I was looking more for a root of the why not something else I guess.Thanks for the reply.

RE: Blocking

kevriley — Fri, 16 May 2008 08:49:20 GMT

That is why I added the caveats that the wait time value is low and the waittype is a latch waittype. If you had waits due to parallelism the waittypes would be PSS_CHILD, EXCHANGE or CXPACKET waittypes and the blocked column would not be populated - after all a paralleism wait is NOT a block.

RE: Blocking

ezlikesundaymorning — Fri, 16 May 2008 08:28:08 GMT

Ok it looks like I am back on my soap box. This is another one of those questions that actually does not have a right answer given. This is not a microsoft exam where you choose the best answer. What the H E double hockey sticks is going on with these questions. While it is expected behavior, that is not completely the right answer as given in the support site. I did know that according to microsoft this was the intended behavior but others that choose parallelism is not totally out of the question. Ascync IO, not totally wrong. This should have been choose which apply.Coooooommmmmmmeeeeee ooooooooonnnnnnnnnn. I mean WTF. Ya see I am black and white kind of guy. Were any of the answers totally right or totally wrong... nope. Read the article.:D

Blocking

kevriley — Thu, 15 May 2008 21:24:21 GMT

Comments posted to this topic are about the item [B]Blocking[/B]