Blocking Chains and Timing

  • 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.

    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

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

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

  • Where did you get the blocking chain information from?

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

  • 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)

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

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