13

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.

Improve this question
4
  • 2
    That's a hot ORM mess. How is performance when the query is run at MAXDOP 1?
    – Erik Reasonable Rates Darling
    Commented Aug 28, 2018 at 18:27
  • 1
    Performance was not that bad. Yeah, the option i have is to run with Option MaxDOP 1. Have to hate EF sometimes.
    – warrior
    Commented Aug 28, 2018 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
    Commented Aug 28, 2018 at 18:44
  • @Forrest So view calls a function which return belows justpaste.it/68axo
    – warrior
    Commented Aug 28, 2018 at 19:03

1 Answer 1

Reset to default
13

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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