SQL Deadlock graph - Find exact statement holding the lock and the time it started I'm trying to troubleshoot a complex deadlock issue.

There are 2 separate processes (service and agent job) which often concurrently execute the same SP but with different @BatchID parameters. This SP is called within an explicit BEGIN TRAN. There are 30 different Insert/Update statements within the SP. The 2 processes deadlock 5 times a day on the same table/index, around the same time each day, and the service process is always the victim. The table has many unnecessary/redundant indexes and a couple of Insert/Update Triggers. Lock Escalation is Disabled.

Deadlock graph:


Deadlock statements:


We know that the survivor/victim are SQL sessions (SPID), NOT SQL statements.

We also know that the UPDATE statements shown in Frame1 of both sessions are involved in the Deadlock. And How are they involved ? They are both REQUESTORS of the U lock on the Index Key. Am I correct so far..

But which statements are the OWNERS ? And when did they start holding the X lock on the Index Key?
One of the standard recommendations for resolving/reducing deadlocks is to Shorten Transactions.
But if there are a hundred similar statements, without knowing WHICH statement started holding the lock and WHEN, it's not easy to go about shortening a transaction..
Question 1: But which statements are the OWNERS ?
  


Statements don't own locks.  Transactions own locks.  


  
    And when did they start holding the X lock on the Index Key?
  


There's nothing in the deadlock graph or DMVs that will tell you that.  That information is only available in a trace event or an XEvent, at the moment the lock is acquired.


  
    Is a Key Lock held only on the rows which satisfy the UPDATE's WHERE clause, or is it held on All values that match the value in the Index
  


UPDATE uses a U lock to read, then converts to an X lock to update.


  
    Would it go away by adding (TABLOCK) to the UPDATEs
  


Probably.  One key strategy to deadlock resolution is to lock earlier and bigger.  Deadlocks occur only when two sessions acquire compatible locks, and then later attempt to acquire incompatible locks.  If the first locks in the transaction are incompatible, then the second session will wait until the first commits to acquire its first lock.  

One easy way to get a lock at the right granularity is to use an application lock, with sp_getapplock.  If you decide that transaction A and B can't run concurrently, then just make each acquire the same application lock at the beginning, and they will run sequentially.