question

smtdba avatar image
smtdba asked

2017 System_Health ee deadlock w/o victim

I query system_health ee file and filter on just xml_deadlock_report.

under "/event/data/value/child::*" I'm finding a deadlock graph.

<deadlock>
  <victim-list />
  <process-list>

No victim. Nothing dies. Nobody kills a process. The processes all compete.

This only happens on my 2017 instance. The 2016 instances only show deadlocks with a victim.

My assumption is that this is showing blocking and the waiting caused and that this is new in 2017.

Am I making the correct assumption or am I missing something?

-- Adding resource-list

These are both threads of the same query

  <resource-list>
    <exchangeEvent id="Pipe28858c12690" WaitType="e_waitPipeNewRow" waiterType="Producer" nodeId="16" tid="1" ownerActivity="receivedData" waiterActivity="tryToSendData" merging="true" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process288aa523c28" />
      </owner-list>
      <waiter-list>
        <waiter id="process285fc5fc8c8" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Pipe286af65e8b0" WaitType="e_waitPipeNewRow" waiterType="Producer" nodeId="4" tid="1" ownerActivity="receivedData" waiterActivity="tryToSendData" merging="true" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process285fc5fcca8" />
      </owner-list>
      <waiter-list>
        <waiter id="process288aa523c28" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Pipe286af65fe80" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="4" tid="1" ownerActivity="sentData" waiterActivity="needMoreData" merging="true" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process285a442aca8" />
      </owner-list>
      <waiter-list>
        <waiter id="process285fc5fcca8" />
      </waiter-list>
    </exchangeEvent>
    <exchangeEvent id="Pipe28549b18e90" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="16" tid="2" ownerActivity="sentData" waiterActivity="needMoreData" merging="true" spilling="false" waitingToClose="false">
      <owner-list>
        <owner id="process285fc5fc8c8" />
      </owner-list>
      <waiter-list>
        <waiter id="process285a442aca8" />
      </waiter-list>
    </exchangeEvent>
  </resource-list>
deadlockextended-eventssql-server 2017
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·

What is shown in the resource-list elements?

0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered

So this is an intra-query parallelism deadlock (https://blogs.msdn.microsoft.com/bartd/2008/09/24/todays-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks/) and as such has no victim as the deadlock is between threads of the same process. Specifically, it has 'exchange event' resources, so you can either force this to be non-parallel, or tune the query to use a serial plan

These are not new in SQL2017, and depend on your workload/settings/statistics/etc

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.