SQL Server – Solving Intra Parallel Query Deadlocks

deadlocksql serversql-server-2017

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: enter image description here

Additionally, here's

We are on SQL Server 2017 SP2.

Best 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.