Due to analyzing several deadlocks I used sp_blitzIndex to get an overview about locks.
There is a high accordance between the tables (and views) affected by the deadlocks and the tables being diagnosed as
"Aggressive Indexes: Total lock wait time > 5 minutes (row + page)"
There it is adviced to check missing indexes on those tables. Ok, there are indeed misssing indexes with high potential suggested by Sql Server for those tables...
But I am actually trying to understand the exact meaning of the information given by sp_BlitzIndex and the reasons for them appearing.
Here are some samples popping up in deadlock chains as well as "Aggressive Indexes":
dbo.O.IX_O1 (48): Page lock waits: 133; total duration: 5 minutes; avg duration: 2 seconds; Lock escalation attempts: 98; Actual Escalations: 19.
dbo.O.IX_O2 (57): Row lock waits: 468; total duration: 5 minutes; avg duration: 0 seconds;
dbo.O is a heap.
dbo.P.IX_P1 (18): Row lock waits: 6; total duration: 19 seconds; avg duration: 3 seconds; Page lock waits: 52; total duration: 4 minutes; avg duration: 5 seconds; Lock escalation attempts: 196; Actual Escalations: 19.
dbo.D.IX_D1 (14): Row lock waits: 587; total duration: 30 minutes; avg duration: 3 seconds; Lock escalation attempts: 30; Actual Escalations: 0.
dbo.S.PK_S (2): Row lock waits: 697; total duration: 44 minutes; avg duration: 3 seconds; Lock escalation attempts: 709; Actual Escalations: 1.
dbo.E.IX_E1 (18): Row lock waits: 108; total duration: 5 minutes; avg duration: 3 seconds; Lock escalation attempts: 4; Actual Escalations: 0.
Brent Ozar unfortunately does not really explain their meaning and ways to workaround, besides re-engeneer indexes. So what does it mean and why is it there? Is it basically because READS and/or WRITES on those tables are simply too slow and the resulting locks take too much time?
In case I go ahead and add the missing indexes having the most impact - won't this just increase the time required to perform insert / update statements and maybe this way increase the chance to trap into deadlocks - or the other way round, will removal of unused indexes for a table reduce the chance of deadlocks because I/U statements will be faster?