Blocking Chains and Timing

Question

Post reply

Blocking Chains and Timing

Leo.Miller

SSChampion

Points: 13048
More actions
September 19, 2023 at 4:52 am

#4289313

I've got a blocking chain anomaly, where the Head Blocker has a duration of 145 sec, but there's a blocked SPID under that with a duration of 304 sec. See lines 1 and 5 below. What's also odd is that the Login Time of the blocking SPID is after the Last Batch time of the Blocked SPID.
I'd initially thought that the last batch from SPID 116 isn't the actual culprit, but that there was an earlier batch against SPID 116 that caused the problem the SPID has the same transaction bracket open, but then why didn't the blocking alert trigger earlier, after all there was 150 seconds of blocking already.
In fact why is 153 in there at all since it started before ALL the other batches but isn't blocking any of them. I would expect Last Batch times to generally increase as you go down the table as is generally true, but SPID 153 is out of place.
Any ideas would be appreciated.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jo Pattyn SSC-Dedicated Points: 32073 More actions · Answer 2

Where did you get the blocking chain information from?

Could it be that a program started a select but doesn't retrieve all rows?

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 3

Duration only identifies how long something has been running. It is possible that a SPID was running something with many queries for a long period and, when it finally got to a certain point, it ran into the blocking, which hadn't been running for as long.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)