Solving intra parallel query deadlocks

Question

We are experiencing deadlocks in our production environment. Seems to be the victim and winner are the same spid's. We rebuild/reorganize indexes daily and we dont find any missing indexes.

Have you guys ever encountered this scenario and if so how did you guys solve it?  Any suggestions are appreciated.

Here's the deadlock graph:

Additionally, here's

the deadlock XML: https://justpaste.it/6xasd
and the query plan: https://www.brentozar.com/pastetheplan/?id=ryBbs-XPQ

We are on SQL Server 2017 SP2.

David Browne - Microsoft · Accepted Answer

First off, intra-query parallelism deadlocks are always a SQL Server bug.  There's not much you can do to fix them, other than updating SQL Server, opening a support case, suppressing parallelism (eg adding a MAXDOP 1 hint), or trying to get a query that uses a simpler plan.
In this case in EF, if you can, set UseDatabaseNullSemantics to true.  Otherwise the EF query translator generates TSQL queries that emulate the C# null comparison semantics.  That will clean up the NULL handling in the query.
Other than that, this query appears to be a view, joined with another table, and then a paging query on top of that.  So possibly can be simplified if you can figure out what the intent is.

Solving intra parallel query deadlocks

One Answer

Add your own answers!

Ask a Question