0

I encountered several deadlocks in my application over the last 2 months and i checked the Extended Events to find the details. I tried to understand the dealock XML and i came across this great article. Based on the article i learned that i am having deadlock related to parallelism,specificlaly intra-query parallelism.

The queries my application generated are mostly dymaic/adhoc sql queries and are complex(involving views with join of six or more tables and joins of views etc).So executing parallel is the best option for me.

Help me to understand more about the below deadlock XML.

 <deadlock>
 <victim-list />
 <process-list>
  <process id="process24cb1b2e108" taskpriority="0" logused="10000" waittime="233" schedulerid="37" kpid="52984" status="suspended" spid="85" sbid="2" ecid="44" priority="0" trancount="0" lastbatchstarted="2019-01-03T16:31:28.313" lastbatchcompleted="2019-01-03T16:31:28.310" lastattention="1900-01-01T00:00:00.310" clientapp="iyeTek.Services.MSP.AICS.Host.MSP" hostname="EAICS-APP-03" hostpid="7264" isolationlevel="read uncommitted (1)" xactid="44894170012" currentdb="6" currentdbname="iyeTek.Services.MSP.AICS" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="472" stmtend="25834" sqlhandle="0x02000000a22caa3ae361bc0fcae15136dcf16dd41e2ac38f0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@p__linq__0 int,@p__linq__1 int,@p__linq__2 int,@p__linq__3 varchar(8000),@p__linq__4 varchar(8000),@p__linq__5 varchar(8000),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 varchar(8000),@p__linq__9 int,@p__linq__10 int)SELECT TOP (@p__linq__10) 
    [Project2].[FormId] AS [FormId], 
    [Project2].[ReportId] AS [ReportId], 
    [Project2].[ReportDateTime] AS [ReportDateTime], 
    [Project2].[ReportIncidentNumber] AS [ReportIncidentNumber], 
    [Project2].[IncidentNumber] AS [IncidentNumber], 
    [Project2].[SubUnit] AS [SubUnit], 
    [Project2].[FileClass] AS [FileClass], 
    [Project2].[NatureOfIncident] AS [NatureOfIncident], 
    [Project2].[IncidentType] AS [IncidentType], 
    [Project2].[SupplementNumber] AS [SupplementNumber], 
    [Project2].[IncidentStatus] AS [IncidentStatus], 
    [Project2].[ReportDate] AS [ReportDate], 
    [Project2].[SupplementDate] AS [SupplementDate], 
    [Project2].[ComplainantFirstName] AS [ComplainantFirstName], 
    [Project2].[Compl   </inputbuf>
  </process>
  <process id="process24fe5a6b848" taskpriority="0" logused="10000" waittime="233" schedulerid="34" kpid="25296" status="suspended" spid="85" sbid="2" ecid="31" priority="0" trancount="0" lastbatchstarted="2019-01-03T16:31:28.313" lastbatchcompleted="2019-01-03T16:31:28.310" lastattention="1900-01-01T00:00:00.310" clientapp="iyeTek.Services.MSP.AICS.Host.MSP" hostname="EAICS-APP-03" hostpid="7264" isolationlevel="read uncommitted (1)" xactid="44894170012" currentdb="6" currentdbname="iyeTek.Services.MSP.AICS" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="472" stmtend="25834" sqlhandle="0x02000000a22caa3ae361bc0fcae15136dcf16dd41e2ac38f0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@p__linq__0 int,@p__linq__1 int,@p__linq__2 int,@p__linq__3 varchar(8000),@p__linq__4 varchar(8000),@p__linq__5 varchar(8000),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 varchar(8000),@p__linq__9 int,@p__linq__10 int)SELECT TOP (@p__linq__10) 
    [Project2].[FormId] AS [FormId], 
    [Project2].[ReportId] AS [ReportId], 
    [Project2].[ReportDateTime] AS [ReportDateTime], 
    [Project2].[ReportIncidentNumber] AS [ReportIncidentNumber], 
    [Project2].[IncidentNumber] AS [IncidentNumber], 
    [Project2].[SubUnit] AS [SubUnit], 
    [Project2].[FileClass] AS [FileClass], 
    [Project2].[NatureOfIncident] AS [NatureOfIncident], 
    [Project2].[IncidentType] AS [IncidentType], 
    [Project2].[SupplementNumber] AS [SupplementNumber], 
    [Project2].[IncidentStatus] AS [IncidentStatus], 
    [Project2].[ReportDate] AS [ReportDate], 
    [Project2].[SupplementDate] AS [SupplementDate], 
    [Project2].[ComplainantFirstName] AS [ComplainantFirstName], 
    [Project2].[Compl   </inputbuf>
  </process>
  <process id="process23e2d9cb088" taskpriority="0" logused="10000" waittime="229" schedulerid="20" kpid="38536" status="suspended" spid="85" sbid="2" ecid="25" priority="0" trancount="0" lastbatchstarted="2019-01-03T16:31:28.313" lastbatchcompleted="2019-01-03T16:31:28.310" lastattention="1900-01-01T00:00:00.310" clientapp="iyeTek.Services.MSP.AICS.Host.MSP" hostname="EAICS-APP-03" hostpid="7264" isolationlevel="read uncommitted (1)" xactid="44894170012" currentdb="6" currentdbname="iyeTek.Services.MSP.AICS" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="472" stmtend="25834" sqlhandle="0x02000000a22caa3ae361bc0fcae15136dcf16dd41e2ac38f0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@p__linq__0 int,@p__linq__1 int,@p__linq__2 int,@p__linq__3 varchar(8000),@p__linq__4 varchar(8000),@p__linq__5 varchar(8000),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 varchar(8000),@p__linq__9 int,@p__linq__10 int)SELECT TOP (@p__linq__10) 
    [Project2].[FormId] AS [FormId], 
    [Project2].[ReportId] AS [ReportId], 
    [Project2].[ReportDateTime] AS [ReportDateTime], 
    [Project2].[ReportIncidentNumber] AS [ReportIncidentNumber], 
    [Project2].[IncidentNumber] AS [IncidentNumber], 
    [Project2].[SubUnit] AS [SubUnit], 
    [Project2].[FileClass] AS [FileClass], 
    [Project2].[NatureOfIncident] AS [NatureOfIncident], 
    [Project2].[IncidentType] AS [IncidentType], 
    [Project2].[SupplementNumber] AS [SupplementNumber], 
    [Project2].[IncidentStatus] AS [IncidentStatus], 
    [Project2].[ReportDate] AS [ReportDate], 
    [Project2].[SupplementDate] AS [SupplementDate], 
    [Project2].[ComplainantFirstName] AS [ComplainantFirstName], 
    [Project2].[Compl   </inputbuf>
  </process>
  <process id="process23d809cc108" taskpriority="0" logused="10000" waittime="11152" schedulerid="34" kpid="55096" status="suspended" spid="85" sbid="2" ecid="36" priority="0" trancount="0" lastbatchstarted="2019-01-03T16:31:28.313" lastbatchcompleted="2019-01-03T16:31:28.310" lastattention="1900-01-01T00:00:00.310" clientapp="iyeTek.Services.MSP.AICS.Host.MSP" hostname="EAICS-APP-03" hostpid="7264" isolationlevel="read uncommitted (1)" xactid="44894170012" currentdb="6" currentdbname="iyeTek.Services.MSP.AICS" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="472" stmtend="25834" sqlhandle="0x02000000a22caa3ae361bc0fcae15136dcf16dd41e2ac38f0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@p__linq__0 int,@p__linq__1 int,@p__linq__2 int,@p__linq__3 varchar(8000),@p__linq__4 varchar(8000),@p__linq__5 varchar(8000),@p__linq__6 datetime2(7),@p__linq__7 datetime2(7),@p__linq__8 varchar(8000),@p__linq__9 int,@p__linq__10 int)SELECT TOP (@p__linq__10) 
    [Project2].[FormId] AS [FormId], 
    [Project2].[ReportId] AS [ReportId], 
    [Project2].[ReportDateTime] AS [ReportDateTime], 
    [Project2].[ReportIncidentNumber] AS [ReportIncidentNumber], 
    [Project2].[IncidentNumber] AS [IncidentNumber], 
    [Project2].[SubUnit] AS [SubUnit], 
    [Project2].[FileClass] AS [FileClass], 
    [Project2].[NatureOfIncident] AS [NatureOfIncident], 
    [Project2].[IncidentType] AS [IncidentType], 
    [Project2].[SupplementNumber] AS [SupplementNumber], 
    [Project2].[IncidentStatus] AS [IncidentStatus], 
    [Project2].[ReportDate] AS [ReportDate], 
    [Project2].[SupplementDate] AS [SupplementDate], 
    [Project2].[ComplainantFirstName] AS [ComplainantFirstName], 
    [Project2].[Compl   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <exchangeEvent id="Pipe252fd257a20" WaitType="e_waitPipeNewRow" waiterType="Producer" nodeId="94" tid="7" ownerActivity="receivedData" waiterActivity="tryToSendData" merging="true" spilling="false" waitingToClose="false">
   <owner-list>
    <owner id="process24fe5a6b848" />
   </owner-list>
   <waiter-list>
    <waiter id="process24cb1b2e108" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe24eb8b66b00" WaitType="e_waitPipeNewRow" waiterType="Producer" nodeId="43" tid="7" ownerActivity="receivedData" waiterActivity="tryToSendData" merging="true" spilling="false" waitingToClose="false">
   <owner-list>
    <owner id="process23d809cc108" />
   </owner-list>
   <waiter-list>
    <waiter id="process24fe5a6b848" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe25226bee470" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="94" tid="2" ownerActivity="sentData" waiterActivity="needMoreData" merging="true" spilling="false" waitingToClose="false">
   <owner-list>
    <owner id="process24cb1b2e108" />
   </owner-list>
   <waiter-list>
    <waiter id="process23e2d9cb088" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe24b59462060" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="43" tid="7" ownerActivity="sentData" waiterActivity="needMoreData" merging="true" spilling="false" waitingToClose="false">
   <owner-list>
    <owner id="process23e2d9cb088" />
   </owner-list>
   <waiter-list>
    <waiter id="process23d809cc108" />
   </waiter-list>
  </exchangeEvent>
 </resource-list>
</deadlock>
  • Additional Info: SQL Server 2016
  • 128 GB RAM
  • 1.5 TB data
  • 40 Logical CPU
  • Cost of Threshold : 20
  • MaxDOP : 8

I am not sure whether increasing MaxDOP is a good option. Any direction will be helpful.

Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.