1

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
4

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.

4

Well, this seems to be pretty conclusive:

[Database Id = 49 Object Id = 1285631673]

Even though the database isn't listed explicitly, these seem like sequential entries for the same spid. If deadlocks involved other databases, surely they would be mentioned somewhere in this flurry of messages, but this is the only reference I can see.

Take a look at the system_health session - it will have a lot more information about these deadlocks that you can use to help troubleshoot. And grab SentryOne Plan Explorer - 100% free - it can help make deadlock diagrams much simpler to analyze / solve. See this post, for example.

  • I just gave a snip consecutive with our database reference. There are a lot of other references to other databases in the same file. The "sequential entries for same SPID" is what I was hoping (or not hoping) to hear. Thank you. – royappa Apr 3 at 10:44

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.