How to handle lock information?

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

(Explanation).

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.

Questions:

-
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 re

I suggest you read Understanding how SQL Server executes a query.

I can't comment about what Brent's scrips, but I can tell you about the wait stats info in sys.dm_db_index_operational_stats. The per-hobt wait stats aggregate page and row lock wait info and page latch and io latch info. High numbers here indicate either contention (many concurrent transactions attempting to operate on the same page/row) or scans (operations locking and latching all pages). In your case looks like the problem is scans. As every operation is reading the entire table you cause a lot of locking, latching and possible IO. An index would dramatically reduce the numbers by turning scans into seeks.

Further read:

• How to analyse SQL Server performance

• SQL Server Index Design Guide

StackExchange Database Administrators Q#118572, answer score: 2