• sqlrob - Sunday, March 19, 2017 10:37 PM

    Sample Blocking capture..

    Thanks for that image, the details raise some interesting questions.
    Those are Key locks, not Page locks (the Wait Resource starts with "KEY:". What follows that is the lock hash (identifier) for a given key value.

    Is the column between CPU and Memory showing Physical Reads? If so, those numbers are awfully high for a single-row update using a primary key. Are there other queries that are also updating values in this table that could produce a collision, either on the specific row or on values in the indexes?
    Also, all of the waiting sessions are two transaction levels deep. Are these updates being called in a loop as part of a larger batch? If so, can those separate calls be individually committed (meaning you will need to separately take steps to ensure the entire batch is fully executed and committed, even through errors and failures)?

    You may be blaming the victim of a different call that should be changed instead.

    Eddie Wuerch
    MCM: SQL