SQL Server – Is This Deadlock Related to Our Database?

deadlocksql serversql-server-2012

our application is running in a SQL enterprise cluster hosted at a client. We don't have administrative access. They are saying that our database is generating deadlocks, and sent us a huge trace file. The clip below shows a "Deadlock encountered" paragraph, and it has some paragraphs relating to our "Database Id = 49". But I don't see any identifiers connecting that "Deadlock encountered" paragraph to ours. We use the read committed mode in C# with "read committed snapshot" isolation mode turned on in SQL.

My simple question is, from this log, how to tell that this "deadlock encountered" is related to our database #49? Because elsewhere in that same log file they have other deadlocks more clearly identified with other databases in the same cluster.

We've asked for extended event tracing but don't know how long they will take. Thanks.

03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,Input Buf: RPC Event: Proc [Database Id = 49 Object Id = 1285631673]
03/20/2019 11:13:58,spid3s,Unknown,SPID: 72 ECID: 20 Statement Type: SELECT Line #: 44
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:20 TaskProxy:(0x00000003E0F2BE20) Value:0x5380c188 Cost:(0/10000)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x00000002221A7400  Xid Slot: 3<c/> Wait Slot: 6<c/> Task: 0x000000015380C188<c/> (Consumer)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0
03/20/2019 11:13:58,spid3s,Unknown,Node:6
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:25 TaskProxy:(0x0000000141F060A0) Value:0x72f3c188 Cost:(20/0)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x000000066ED0F160  Xid Slot: 4<c/> Wait Slot: 1<c/> Task: 0x0000000772F3C188<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 1
03/20/2019 11:13:58,spid3s,Unknown,Node:5
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:26 TaskProxy:(0x00000003E0F2BAC0) Value:0x59403498 Cost:(20/0)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x00000001FC9157D0  Xid Slot: 6<c/> Wait Slot: 3<c/> Task: 0x0000000759403498<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 1
03/20/2019 11:13:58,spid3s,Unknown,Node:4
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:26 TaskProxy:(0x0000000141F06100) Value:0x11dc1868 Cost:(20/0)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x000000018A587F20  Xid Slot: 5<c/> Wait Slot: 0<c/> Task: 0x0000000511DC1868<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 1
03/20/2019 11:13:58,spid3s,Unknown,Node:3
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:21 TaskProxy:(0x00000003E0F2BE80) Value:0x72f3d868 Cost:(20/0)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x00000004A9A250E0  Xid Slot: 4<c/> Wait Slot: 0<c/> Task: 0x0000000772F3D868<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 1
03/20/2019 11:13:58,spid3s,Unknown,Node:2
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:22 TaskProxy:(0x00000003E0F2BEE0) Value:0x11dc0cf8 Cost:(20/0)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x00000012F70769F0  Xid Slot: 7<c/> Wait Slot: 3<c/> Task: 0x0000000511DC0CF8<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 1
03/20/2019 11:13:58,spid3s,Unknown,Node:1
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,Wait-for graph
03/20/2019 11:13:58,spid3s,Unknown,Deadlock encountered .... Printing deadlock information
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:27 TaskProxy:(0x0000000141F06160) Value:0xb8ebdc38 Cost:(0/10000)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x00000001FC914160  Xid Slot: 7<c/> Wait Slot: 0<c/> Task: 0x00000001B8EBDC38<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 0
03/20/2019 11:13:58,spid3s,Unknown,Node:10
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:23 TaskProxy:(0x00000003E0F2BF40) Value:0x59402188 Cost:(0/10000)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x000000066ED0FCC0  Xid Slot: 1<c/> Wait Slot: 5<c/> Task: 0x0000000759402188<c/> (Consumer)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0
03/20/2019 11:13:58,spid3s,Unknown,Node:9
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,Input Buf: RPC Event: Proc [Database Id = 49 Object Id = 1285631673]
03/20/2019 11:13:58,spid3s,Unknown,SPID: 72 ECID: 19 Statement Type: SELECT Line #: 44
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:19 TaskProxy:(0x00000003E0F2BDC0) Value:0x59402558 Cost:(0/10000)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x0000000225F57790  Xid Slot: 2<c/> Wait Slot: 6<c/> Task: 0x0000000759402558<c/> (Consumer)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0

Best Answer

It is related to your database, but it's a little more complicated than a normal deadlock.

The deadlock you're hitting is within a parallel query, not between two or more other queries.

You can tell by the XML produced:

(Consumer)<c/> Exchange Wait Type: e_waitPipeGetRow

There's a similar Q&A here: Why are there Victimless Entries on the Deadlock Graph?

You can do all the stuff Aaron noted, or head over to the First Responder Kit and use sp_BlitzLock. It'll check the system health session by default, but you can point it at an Extended Event session that captures deadlock XML, too.

Related Question