5

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.

  • 1
    That's a hot ORM mess. How is performance when the query is run at MAXDOP 1? – Erik Darling Aug 28 '18 at 18:27
  • Performance was not that bad. Yeah, the option i have is to run with Option MaxDOP 1. Have to hate EF sometimes. – din Aug 28 '18 at 18:40
  • Would you be able to share the definition of ViewEffectiveEmployees? There's a chance you could make some tweaks in there that will affect the plan. – Forrest Aug 28 '18 at 18:44
  • @Forrest So view calls a function which return belows justpaste.it/68axo – din Aug 28 '18 at 19:03
6

First off, intra-query parallelism deadlocks are always a SQL Server bug. There's not much you can do to fix them, other than 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.

| improve this answer | |

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.