4

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:

  1. 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?

  2. 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?

1

Excessive locks and dead locks have a few usual suspects. The fact that your server complaining about missing indexes is a big hint that the current indexing strategy is inadequate, therefore any transactions taking place for the table O, P, D, S, and E are sloooooow and loooooong to complete, therefore increasing the chance of escalation and such. Try running some problematic statements with SET STATISTICS IO ON. They most likely have a very high number of physical reads, instead of high logical reads, indicating high disk IO.

Combining this with a high number of concurrent transactions can increase the chance of deadlocks. The rule of thumb for a good transaction design is to make it as quick and short as possible. It might be helpful for you to revisit the basic and refresh your knowledge on deadlocks and lock escalation.

Your concern regarding the decreased upsert performance with indexes is understandable. Indexes can indeed degrade the performance of operations like insert and update because pages might have to be split, reorganized, and shuffled around but that's only IF there are too many indexes. A properly designed indexes will boost the IO performance dramatically. Test by adding an index that can support the problematic statement; one without index, one with. You can use SET STATISTICS TIME ON to get the actual time.

But having a good set of indexes alone isn't enough. After all, your statement must be designed to utilize them by making predicates work with the indexes. When either one lacks, your query performance will suffer dramatically. On top of reviewing your indexes, I also recommend reviewing the proper usage of SARGs.

Without seeing the usage stats, I cannot advise you to drop other indexes. I suggest you read this blog Tips on how to find index usage

  • I already have implemented ongoing, restart-independant persisted statistics about usage and missing indexes I can review at any time. so I am in the process of reviewing all indexes, merge them if they are similar, remove unused ones or add missing new ones. The correlation with deadlocks was not clear to me. TY for the information. Q: What are SARGS? – Magier Nov 23 '15 at 9:08
  • ....SARGS=SearchArguments; upsert=Updates/Inserts...... :) – Magier Jan 21 '16 at 13:50
1

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.