TheSQLGuru - Monday, March 20, 2017 9:40 AM

Eddie Wuerch - Monday, March 20, 2017 1:03 AM

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.

Thanks Edie.
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 ?
- There are no other UPDATE QUERIES on that table doing update. But it gets blocked by same UPDATE Query run by other SPIDs. Having said that once SPID acquires exclusive lock on that Key, how is it possible to have a collision? If you could detail me your thoughts?
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 though errors and failures)?
- Good Observation. I will have a look at why they are having two transaction waiting?

WAY back in this thread I mentioned the very high call volume and that the infrastructure may not be up to the task. I don't see where the OP ever answered that. I also mentioned doing differential file IO stall and wait stats analyses and using sp_whoisactive in diff mode too. - There is plenty of CPU and Memory when this happens. So I don't beleive that is the bottleneck. I will check the IO stall..

No one has asked about the potential for trigger(s) on the table either... - There are no Triggers in that table