Sql-server – Why are there Victimless Entries on the Deadlock Graph

deadlocksql serversql-server-2008-r2

I am trying to learn how to analyze SQL Server 2008's deadlock graph, and I'm finding alot of entries with an empty <victim-list> node. I don't understand what these entries represent: if there is no victim, how can I identify the waitresource that is causing the deadlock? What do these entries mean?

Here's a quick example of the entries i'm seeing:

<deadlock-list>
 <deadlock>
  <victim-list />
  <process-list>
   <process id="processd2b6508" taskpriority="0" logused="10000" waittime="31" schedulerid="63" kpid="9104" status="suspended" spid="69" sbid="0" ecid="184" priority="0" trancount="0" lastbatchstarted="2012-07-30T01:10:45.550" lastbatchcompleted="2012-07-30T01:10:45.550" clientapp=".Net SqlClient Data Provider" hostname="XXXXXXX" hostpid="3648" isolationlevel="read committed (2)" xactid="30461033" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="" line="1" sqlhandle="0x020000002340c50225c17d0eec9bf7c51129348edffd1c70" /> 
     <!--About 2 more frame tags... -->
    </executionStack>
    <inputbuf /> 
   </process>
   <!-- 3 or so more process tags... -->
  </process-list>
  <resource-list>
   <exchangeEvent id="Pipeb005eeba0" WaitType="e_waitPipeNewRow" nodeId="7">
    <owner-list>
     <owner id="processd23fdc8" /> 
    </owner-list>
    <waiter-list>
     <waiter id="processd2b6508" /> 
    </waiter-list>
   </exchangeEvent>
   <!-- 2 more exchangeEvents -->
  </resource-list>
 </deadlock>
</deadlock-list>

** edit **
As requested, here is the query used to identify a query by it's sqlhandle:

select sql_handle as Handle,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS Text

from sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where sql_handle = --0x04000D00E3572A56542E4601CE9E00010100001000000000

from RyanBoyer.net

Best Answer

ExchangeEvent & e_waitPipeNewRow suggests you've run into what Bart Duncan refers too as Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks".

Most intra-query parallelism deadlocks are considered bugs, although some of them can be risky bugs to fix so a fix may not be possible. If you run into one and you're already on the latest SQL service pack, your best bet may be to investigate workarounds.

So, not much you can do other than:

  • Ensure you're on the latest service pack and cumulative update.
  • Try to identify indexes and/or other optimisations to improve the performance of the query. You mention that inputbuf isn't populated but you may be able to identify the query in play via the sqlhandle in the graph XML. If you get nothing from that try running a trace and correlating with the times these deadlocks occur.
  • Reduce MAXDOP for this query or try MAXDOP(1) to force single-threaded execution. Be aware that you might fix the deadlocks but introduce a different set of performance issues by restricting parallelism.
  • Open a support call with Microsoft. Possible that a) they have a non-public hotfix for this scenario or b) as these intra-query deadlocks are deemed to be bugs they may want to work with you to find a fix.
Related Question