Sql-server – How to handle SQL Server Intrasession Parallelism Deadlock

deadlockparallelismsql serversql-server-2017

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:

<deadlock>
  <victim-list />
  <process-list>
    <process id="process145e9f6b088" taskpriority="0" logused="10000" waittime="3905" schedulerid="11" kpid="12188" status="suspended" spid="120" sbid="0" ecid="12" priority="0" trancount="0" lastbatchstarted="2018-07-13T08:53:21.247" lastbatchcompleted="2018-07-13T08:53:21.247" lastattention="1900-01-01T00:00:00.247" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxx" hostpid="0" isolationlevel="read committed (2)" xactid="827548366" currentdb="7" currentdbname="YAP_LIVE" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="380" stmtend="8196" sqlhandle="0x02000000f28e4e300a119dabe9b8c2f6faf02b459cb08d700000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@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_.principalPartnerId) as col_1_0_, participan9_.id as col_2_0_, participan9_.name as col_3_0_, (select process18_1_.lastEditorId from Process process18_ inner join ProcessCommissionBase process18_1_ on process18_.id=process18_1_.id where process18_.id=(select max(process19_.id) from Process process19_ inner join ProcessCommissionBase process19_1_ on process19_.id=process19_1_.id inner join DamageFileChapter damagefile20_ on process19_.damageFileChapter_id=damagefile20_.id where damagefile20_.id=damagefile12_.id)) as col_4_0_, damagefile14_.damageId as col_5_0_, (select count(damagefile21_.id) from DamageFileChapter damagefile21_ where damagefile21_.damageFile_id=damagefile13_.damageFile_id and damagefile21_.bus   </inputbuf>
    </process>
    <process id="process145e9f41848" taskpriority="0" logused="10000" waittime="3905" schedulerid="10" kpid="9492" status="suspended" spid="120" sbid="0" ecid="18" priority="0" trancount="0" lastbatchstarted="2018-07-13T08:53:21.247" lastbatchcompleted="2018-07-13T08:53:21.247" lastattention="1900-01-01T00:00:00.247" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxx" hostpid="0" isolationlevel="read committed (2)" xactid="827548366" currentdb="7" currentdbname="YAP_LIVE" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="380" stmtend="8196" sqlhandle="0x02000000f28e4e300a119dabe9b8c2f6faf02b459cb08d700000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@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_.principalPartnerId) as col_1_0_, participan9_.id as col_2_0_, participan9_.name as col_3_0_, (select process18_1_.lastEditorId from Process process18_ inner join ProcessCommissionBase process18_1_ on process18_.id=process18_1_.id where process18_.id=(select max(process19_.id) from Process process19_ inner join ProcessCommissionBase process19_1_ on process19_.id=process19_1_.id inner join DamageFileChapter damagefile20_ on process19_.damageFileChapter_id=damagefile20_.id where damagefile20_.id=damagefile12_.id)) as col_4_0_, damagefile14_.damageId as col_5_0_, (select count(damagefile21_.id) from DamageFileChapter damagefile21_ where damagefile21_.damageFile_id=damagefile13_.damageFile_id and damagefile21_.bus   </inputbuf>
    </process>
    <process id="process145e9f4b848" taskpriority="0" logused="10000" waittime="3904" schedulerid="5" kpid="7260" status="suspended" spid="120" sbid="0" ecid="20" priority="0" trancount="0" lastbatchstarted="2018-07-13T08:53:21.247" lastbatchcompleted="2018-07-13T08:53:21.247" lastattention="1900-01-01T00:00:00.247" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxx" hostpid="0" isolationlevel="read committed (2)" xactid="827548366" currentdb="7" currentdbname="YAP_LIVE" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="380" stmtend="8196" sqlhandle="0x02000000f28e4e300a119dabe9b8c2f6faf02b459cb08d700000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@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_.principalPartnerId) as col_1_0_, participan9_.id as col_2_0_, participan9_.name as col_3_0_, (select process18_1_.lastEditorId from Process process18_ inner join ProcessCommissionBase process18_1_ on process18_.id=process18_1_.id where process18_.id=(select max(process19_.id) from Process process19_ inner join ProcessCommissionBase process19_1_ on process19_.id=process19_1_.id inner join DamageFileChapter damagefile20_ on process19_.damageFileChapter_id=damagefile20_.id where damagefile20_.id=damagefile12_.id)) as col_4_0_, damagefile14_.damageId as col_5_0_, (select count(damagefile21_.id) from DamageFileChapter damagefile21_ where damagefile21_.damageFile_id=damagefile13_.damageFile_id and damagefile21_.bus   </inputbuf>
    </process>
    <process id="process145e9f6b848" taskpriority="0" logused="10000" waittime="3904" schedulerid="11" kpid="13508" status="suspended" spid="120" sbid="0" ecid="19" priority="0" trancount="0" lastbatchstarted="2018-07-13T08:53:21.247" lastbatchcompleted="2018-07-13T08:53:21.247" lastattention="1900-01-01T00:00:00.247" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxx" hostpid="0" isolationlevel="read committed (2)" xactid="827548366" currentdb="7" currentdbname="YAP_LIVE" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="380" stmtend="8196" sqlhandle="0x02000000f28e4e300a119dabe9b8c2f6faf02b459cb08d700000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@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_.principalPartnerId) as col_1_0_, participan9_.id as col_2_0_, participan9_.name as col_3_0_, (select process18_1_.lastEditorId from Process process18_ inner join ProcessCommissionBase process18_1_ on process18_.id=process18_1_.id where process18_.id=(select max(process19_.id) from Process process19_ inner join ProcessCommissionBase process19_1_ on process19_.id=process19_1_.id inner join DamageFileChapter damagefile20_ on process19_.damageFileChapter_id=damagefile20_.id where damagefile20_.id=damagefile12_.id)) as col_4_0_, damagefile14_.damageId as col_5_0_, (select count(damagefile21_.id) from DamageFileChapter damagefile21_ where damagefile21_.damageFile_id=damagefile13_.damageFile_id and damagefile21_.bus   </inputbuf>
    </process>
    <process id="process14000895c28" taskpriority="0" logused="10000" waittime="3904" schedulerid="3" kpid="5752" status="suspended" spid="120" sbid="0" ecid="17" priority="0" trancount="0" lastbatchstarted="2018-07-13T08:53:21.247" lastbatchcompleted="2018-07-13T08:53:21.247" lastattention="1900-01-01T00:00:00.247" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxx" hostpid="0" isolationlevel="read committed (2)" xactid="827548366" currentdb="7" currentdbname="YAP_LIVE" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="380" stmtend="8196" sqlhandle="0x02000000f28e4e300a119dabe9b8c2f6faf02b459cb08d700000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@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_.principalPartnerId) as col_1_0_, participan9_.id as col_2_0_, participan9_.name as col_3_0_, (select process18_1_.lastEditorId from Process process18_ inner join ProcessCommissionBase process18_1_ on process18_.id=process18_1_.id where process18_.id=(select max(process19_.id) from Process process19_ inner join ProcessCommissionBase process19_1_ on process19_.id=process19_1_.id inner join DamageFileChapter damagefile20_ on process19_.damageFileChapter_id=damagefile20_.id where damagefile20_.id=damagefile12_.id)) as col_4_0_, damagefile14_.damageId as col_5_0_, (select count(damagefile21_.id) from DamageFileChapter damagefile21_ where damagefile21_.damageFile_id=damagefile13_.damageFile_id and damagefile21_.bus   </inputbuf>
    </process>
    <process id="process14000894ca8" taskpriority="0" logused="10000" waittime="3904" schedulerid="3" kpid="7816" status="suspended" spid="120" sbid="0" ecid="15" priority="0" trancount="0" lastbatchstarted="2018-07-13T08:53:21.247" lastbatchcompleted="2018-07-13T08:53:21.247" lastattention="1900-01-01T00:00:00.247" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxx" hostpid="0" isolationlevel="read committed (2)" xactid="827548366" currentdb="7" currentdbname="YAP_LIVE" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="380" stmtend="8196" sqlhandle="0x02000000f28e4e300a119dabe9b8c2f6faf02b459cb08d700000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@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_.principalPartnerId) as col_1_0_, participan9_.id as col_2_0_, participan9_.name as col_3_0_, (select process18_1_.lastEditorId from Process process18_ inner join ProcessCommissionBase process18_1_ on process18_.id=process18_1_.id where process18_.id=(select max(process19_.id) from Process process19_ inner join ProcessCommissionBase process19_1_ on process19_.id=process19_1_.id inner join DamageFileChapter damagefile20_ on process19_.damageFileChapter_id=damagefile20_.id where damagefile20_.id=damagefile12_.id)) as col_4_0_, damagefile14_.damageId as col_5_0_, (select count(damagefile21_.id) from DamageFileChapter damagefile21_ where damagefile21_.damageFile_id=damagefile13_.damageFile_id and damagefile21_.bus   </inputbuf>
    </process>
    <process id="process145e9f6a8c8" taskpriority="0" logused="10000" waittime="3904" schedulerid="11" kpid="11104" status="suspended" spid="120" sbid="0" ecid="16" priority="0" trancount="0" lastbatchstarted="2018-07-13T08:53:21.247" lastbatchcompleted="2018-07-13T08:53:21.247" lastattention="1900-01-01T00:00:00.247" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxx" hostpid="0" isolationlevel="read committed (2)" xactid="827548366" currentdb="7" currentdbname="YAP_LIVE" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="380" stmtend="8196" sqlhandle="0x02000000f28e4e300a119dabe9b8c2f6faf02b459cb08d700000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@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_.principalPartnerId) as col_1_0_, participan9_.id as col_2_0_, participan9_.name as col_3_0_, (select process18_1_.lastEditorId from Process process18_ inner join ProcessCommissionBase process18_1_ on process18_.id=process18_1_.id where process18_.id=(select max(process19_.id) from Process process19_ inner join ProcessCommissionBase process19_1_ on process19_.id=process19_1_.id inner join DamageFileChapter damagefile20_ on process19_.damageFileChapter_id=damagefile20_.id where damagefile20_.id=damagefile12_.id)) as col_4_0_, damagefile14_.damageId as col_5_0_, (select count(damagefile21_.id) from DamageFileChapter damagefile21_ where damagefile21_.damageFile_id=damagefile13_.damageFile_id and damagefile21_.bus   </inputbuf>
    </process>
    <process id="process145e9f4a8c8" taskpriority="0" logused="10000" waittime="3907" schedulerid="5" kpid="12852" status="suspended" spid="120" sbid="0" ecid="13" priority="0" trancount="0" lastbatchstarted="2018-07-13T08:53:21.247" lastbatchcompleted="2018-07-13T08:53:21.247" lastattention="1900-01-01T00:00:00.247" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxx" hostpid="0" isolationlevel="read committed (2)" xactid="827548366" currentdb="7" currentdbname="YAP_LIVE" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="380" stmtend="8196" sqlhandle="0x02000000f28e4e300a119dabe9b8c2f6faf02b459cb08d700000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@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_.principalPartnerId) as col_1_0_, participan9_.id as col_2_0_, participan9_.name as col_3_0_, (select process18_1_.lastEditorId from Process process18_ inner join ProcessCommissionBase process18_1_ on process18_.id=process18_1_.id where process18_.id=(select max(process19_.id) from Process process19_ inner join ProcessCommissionBase process19_1_ on process19_.id=process19_1_.id inner join DamageFileChapter damagefile20_ on process19_.damageFileChapter_id=damagefile20_.id where damagefile20_.id=damagefile12_.id)) as col_4_0_, damagefile14_.damageId as col_5_0_, (select count(damagefile21_.id) from DamageFileChapter damagefile21_ where damagefile21_.damageFile_id=damagefile13_.damageFile_id and damagefile21_.bus   </inputbuf>
    </process>
    <process id="process145e9f408c8" taskpriority="0" logused="10000" waittime="3905" schedulerid="10" kpid="13108" status="suspended" spid="120" sbid="0" ecid="14" priority="0" trancount="0" lastbatchstarted="2018-07-13T08:53:21.247" lastbatchcompleted="2018-07-13T08:53:21.247" lastattention="1900-01-01T00:00:00.247" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxx" hostpid="0" isolationlevel="read committed (2)" xactid="827548366" currentdb="7" currentdbname="YAP_LIVE" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="380" stmtend="8196" sqlhandle="0x02000000f28e4e300a119dabe9b8c2f6faf02b459cb08d700000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@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_.principalPartnerId) as col_1_0_, participan9_.id as col_2_0_, participan9_.name as col_3_0_, (select process18_1_.lastEditorId from Process process18_ inner join ProcessCommissionBase process18_1_ on process18_.id=process18_1_.id where process18_.id=(select max(process19_.id) from Process process19_ inner join ProcessCommissionBase process19_1_ on process19_.id=process19_1_.id inner join DamageFileChapter damagefile20_ on process19_.damageFileChapter_id=damagefile20_.id where damagefile20_.id=damagefile12_.id)) as col_4_0_, damagefile14_.damageId as col_5_0_, (select count(damagefile21_.id) from DamageFileChapter damagefile21_ where damagefile21_.damageFile_id=damagefile13_.damageFile_id and damagefile21_.bus   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <exchangeEvent id="Pipe15cf7e300c0" WaitType="e_waitPipeNewRow" waiterType="Producer" nodeId="58" tid="1" ownerActivity="receivedData" waiterActivity="tryToSendData" merging="true" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process145e9f4a8c8" />
      </owner-list>
      <waiter-list>
        <waiter id="process145e9f6b088" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Pipe1573f9424f0" WaitType="e_waitPipeNewRow" waiterType="Producer" nodeId="62" tid="3" ownerActivity="receivedData" waiterActivity="tryToSendData" merging="true" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process14000894ca8" />
      </owner-list>
      <waiter-list>
        <waiter id="process145e9f41848" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Pipe1468ea0d760" WaitType="e_waitPipeNewRow" waiterType="Producer" nodeId="62" tid="2" ownerActivity="receivedData" waiterActivity="tryToSendData" merging="true" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process145e9f408c8" />
      </owner-list>
      <waiter-list>
        <waiter id="process145e9f4b848" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Pipe13c1b8f16c0" WaitType="e_waitPipeNewRow" waiterType="Producer" nodeId="62" tid="1" ownerActivity="receivedData" waiterActivity="tryToSendData" merging="true" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process145e9f6a8c8" />
      </owner-list>
      <waiter-list>
        <waiter id="process145e9f6b848" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Pipe15a8ec37490" WaitType="e_waitPipeNewRow" waiterType="Producer" nodeId="62" tid="4" ownerActivity="receivedData" waiterActivity="tryToSendData" merging="true" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process145e9f6a8c8" />
      </owner-list>
      <waiter-list>
        <waiter id="process14000895c28" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Pipe149efce72c0" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="58" tid="4" ownerActivity="sentData" waiterActivity="needMoreData" merging="true" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process145e9f6b088" />
      </owner-list>
      <waiter-list>
        <waiter id="process14000894ca8" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Pipe15cf7e41c50" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="58" tid="1" ownerActivity="sentData" waiterActivity="needMoreData" merging="true" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process145e9f6b088" />
      </owner-list>
      <waiter-list>
        <waiter id="process145e9f6a8c8" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Port162fa78aa00" WaitType="e_waitPortClose" waiterType="Consumer" nodeId="62" tid="2" ownerActivity="opened" waiterActivity="waitForAllOwnersToClose">
      <owner-list>
        <owner id="process145e9f6b848" />
        <owner id="process145e9f4b848" />
        <owner id="process145e9f41848" />
        <owner id="process14000895c28" />
      </owner-list>
      <waiter-list>
        <waiter id="process145e9f4a8c8" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Pipe15cf7e30ba0" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="58" tid="3" ownerActivity="sentData" waiterActivity="needMoreData" merging="true" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process145e9f6b088" />
      </owner-list>
      <waiter-list>
        <waiter id="process145e9f408c8" />
      </waiter-list>
    </exchangeEvent>
  </resource-list>
</deadlock>

Thanks in advance for your help

Martin

Best Answer

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

Related Question