How to handle SQL Server Intrasession Parallelism Deadlock?

I am struggling to deal with a intrasession deadlock coming from parallelism on SQL Server 2017.

The query is not handwritten but generated by an ORM.

Please have a look at the query plan over here.
My main objective is to get rid of the deadlocks happening daily. The query is not the prettiest but execution time is adequate and not any problem for end users right now.

Here are my questions:

• Why is this query going parallel at all? Estimated subtree cost is 32. Cost Threshold for Parallelism is set to 50 on my instance.

• How would you proceed to reproduce the behaviour? The query is executed via a prepared statement. However I do not end up with the same parallel plan whether I run it as an ad hoc query or as prepared statement. My plan looks completely different and even starts with a completely different table

• What would you propose to stop these deadlocks from ocurring? Is there any index which could help out here?

There is obviously a lot in the statement which could be optimized in order to achieve a much smoother execution. To name one thing it is unfortunate that the same tables are joined multiple times (INNER JOIN) with different predicates sitting on the tables. There is no query simplification happening and all the join tables are read...just as the great Erik Darling blogged about.

However (as this is generated by ORM) a query rewrite is not possible short term and might even be complicated to achieve in the long run. But please don't hold back with any suggestions of rewriting the query even as this is not my first priority.

And here's the deadlock graph:

```






unknown

unknown


(@P0 varchar(8000),@P1 varchar(8000),@P2 varchar(8000),@P3 varchar(8000),@P4 varchar(8000),@P5 varchar(8000),@P6 varchar(8000),@P7 varchar(8000),@P8 varchar(8000),@P9 varchar(8000),@P10 int)select carauditco0_.id as col_0_0_, (select partner17_.id from Partner partner17_ where partner17_.id=damagefile8_.pri

Community wiki answer:

You mentioned this is from an ORM. It looks a lot like NHibernate's flavor of generated SQL.

I know you mentioned re-writing the query would be tough, but the developers may be able to append a query hint:

NHibernate LINQ Add Query Hints (Stack Overflow)

That example adds OPTION (RECOMPILE), but you could add a MAXDOP 1 hint to work around this problem, assuming that doesn't cause unacceptable performance.

Alternatively, you could achieve the same effect in SQL Server using a Plan Guide (to add the hint) or by using a Resource Governor Resource Pool (Enterprise Edition required).

StackExchange Database Administrators Q#212104, answer score: 2