July 9, 2012 at 12:25 pm
Hi guys ...
Just want to know what kind of deadlock Info is displaying in below message? repeating only one SPID on all nodes. Can any one explain in details?
I have TF 1204 and 1222 is on and getting this message in sql error message when deadlock occurs.
Many thanks in advance
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x0000001EAE8A19D0 Xid Slot: 7, Wait Slot: 5, Task: 0x00000001232F2988, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' [highlight=#ffff11]SPID:3101[/highlight] BatchID:0 ECID:40 TaskProxy:(0x0000001E646B67A0) Value:0x232f2988 Cost:(20/0)
NULL
Node:2
Port: 0x000000174A0586A0 Xid Slot: 10, Wait Slot: 2, Task: 0x00000003D8D0E508, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' [highlight=#ffff11]SPID:3101[/highlight] BatchID:0 ECID:31 TaskProxy:(0x0000001E646B6440) Value:0xd8d0e508 Cost:(20/0)
NULL
Node:3
Port: 0x000000162A690B90 Xid Slot: 5, Wait Slot: 10, Task: 0x0000001AA9542508, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' [highlight=#ffff11]SPID:3101[/highlight] BatchID:0 ECID:30 TaskProxy:(0x0000001E646B63E0) Value:0xa9542508 Cost:(0/10000)
SPID: 3101 ECID: 30 Statement Type: SELECT Line #: 1
Input Buf: No Event:
NULL
Node:4
Port: 0x00000000B53DFA50 Xid Slot: 2, Wait Slot: 7, Task: 0x000000154006A088, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:27 TaskProxy:(0x0000001E646B62C0) Value:0x4006a088 Cost:(0/10000)
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x00000000D7D22AC0 Xid Slot: 7, Wait Slot: 0, Task: 0x00000001232F2988, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' [highlight=#ffff11]SPID:3101 [/highlight]BatchID:0 ECID:40 TaskProxy:(0x0000001E646B67A0) Value:0x232f2988 Cost:(20/0)
NULL
Node:2
Port: 0x000000174A0586A0 Xid Slot: 10, Wait Slot: 2, Task: 0x00000003D8D0E508, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:31 TaskProxy:(0x0000001E646B6440) Value:0xd8d0e508 Cost:(20/0)
NULL
Node:3
Port: 0x000000010C7CF740 Xid Slot: 0, Wait Slot: 10, Task: 0x0000000112524088, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' [highlight=#ffff11]SPID:3101[/highlight] BatchID:0 ECID:28 TaskProxy:(0x0000001E646B6320) Value:0x12524088 Cost:(0/10000)
SPID: 3101 ECID: 28 Statement Type: SELECT Line #: 1
Input Buf: No Event:
NULL
Node:4
Port: 0x00000000B53DFA50 Xid Slot: 2, Wait Slot: 7, Task: 0x000000154006A088, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:27 TaskProxy:(0x0000001E646B62C0) Value:0x4006a088 Cost:(0/10000)
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x0000000E325EA0F0 Xid Slot: 6, Wait Slot: 3, Task: 0x0000000112524748, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:39 TaskProxy:(0x0000001E646B6740) Value:0x12524748 Cost:(20/0)
SPID: 3101 ECID: 39 Statement Type: SELECT Line #: 1
Input Buf: No Event:
NULL
Node:2
Port: 0x0000001ED45B3220 Xid Slot: 8, Wait Slot: 4, Task: 0x0000000112524508, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:33 TaskProxy:(0x0000001E646B6500) Value:0x12524508 Cost:(20/0)
SPID: 3101 ECID: 33 Statement Type: SELECT Line #: 1
Input Buf: No Event:
NULL
Node:3
Port: 0x0000001EDAE438B0 Xid Slot: 3, Wait Slot: 8, Task: 0x00000001223262C8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:26 TaskProxy:(0x0000001E646B6260) Value:0x223262c8 Cost:(0/10000)
NULL
Node:4
Port: 0x0000001F14D01280 Xid Slot: 4, Wait Slot: 6, Task: 0x00000003D8D0E088, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:29 TaskProxy:(0x0000001E646B6380) Value:0xd8d0e088 Cost:(0/10000)
SPID: 3101 ECID: 29 Statement Type: SELECT Line #: 1
Input Buf: No Event:
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x00000000B53DE060 Xid Slot: 11, Wait Slot: 0, Task: 0x0000001AA9542BC8, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:41 TaskProxy:(0x0000001E646B6800) Value:0xa9542bc8 Cost:(20/0)
SPID: 3101 ECID: 41 Statement Type: SELECT Line #: 1
Input Buf: No Event:
NULL
Node:2
Port: 0x0000001ED45B3220 Xid Slot: 8, Wait Slot: 4, Task: 0x0000000112524508, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:33 TaskProxy:(0x0000001E646B6500) Value:0x12524508 Cost:(20/0)
NULL
Node:3
Port: 0x00000000B1AFBE20 Xid Slot: 0, Wait Slot: 8, Task: 0x0000000112524088, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:28 TaskProxy:(0x0000001E646B6320) Value:0x12524088 Cost:(0/10000)
NULL
Node:4
Port: 0x00000000ABF61310 Xid Slot: 4, Wait Slot: 11, Task: 0x00000003D8D0E088, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:29 TaskProxy:(0x0000001E646B6380) Value:0xd8d0e088 Cost:(0/10000)
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
Port: 0x00000000AC353330 Xid Slot: 9, Wait Slot: 5, Task: 0x0000000122326988, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:37 TaskProxy:(0x0000001E646B6680) Value:0x22326988 Cost:(20/0)
SPID: 3101 ECID: 37 Statement Type: SELECT Line #: 1
Input Buf: No Event:
NULL
Node:2
Port: 0x0000001ED45B3220 Xid Slot: 8, Wait Slot: 4, Task: 0x0000000112524508, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:33 TaskProxy:(0x0000001E646B6500) Value:0x12524508 Cost:(20/0)
NULL
Node:3
Port: 0x0000001A398B7580 Xid Slot: 5, Wait Slot: 8, Task: 0x0000001AA9542508, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:30 TaskProxy:(0x0000001E646B63E0) Value:0xa9542508 Cost:(0/10000)
NULL
Node:4
Port: 0x0000001EEEA13840 Xid Slot: 4, Wait Slot: 9, Task: 0x00000003D8D0E088, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:3101 BatchID:0 ECID:29 TaskProxy:(0x0000001E646B6380) Value:0xd8d0e088 Cost:(0/10000)
Deadlock encountered .... Printing deadlock information
Wait-for graph
July 9, 2012 at 12:28 pm
Looks like it is related to parallelism.
See below link
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 9, 2012 at 12:37 pm
Thanks ..
It really helps to understand now.
July 9, 2012 at 12:41 pm
That's the output from1204, which is very limited and very hard to interpret. 1204 should only be used on SQL 2000 where it's the only option. 1222 produces much better output on SQL 2005+
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply