Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What kind of Deadlock info is this..!!!!! Expand / Collapse
Author
Message
Posted Monday, July 9, 2012 12:25 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:39 AM
Points: 321, Visits: 1,294
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 TaskProxy0x0000001E646B67A0) Value:0x232f2988 Cost20/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 TaskProxy0x0000001E646B6440) Value:0xd8d0e508 Cost20/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 TaskProxy0x0000001E646B63E0) Value:0xa9542508 Cost0/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 TaskProxy0x0000001E646B62C0) Value:0x4006a088 Cost0/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 TaskProxy0x0000001E646B67A0) Value:0x232f2988 Cost20/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 TaskProxy0x0000001E646B6440) Value:0xd8d0e508 Cost20/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 TaskProxy0x0000001E646B6320) Value:0x12524088 Cost0/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 TaskProxy0x0000001E646B62C0) Value:0x4006a088 Cost0/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 TaskProxy0x0000001E646B6740) Value:0x12524748 Cost20/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 TaskProxy0x0000001E646B6500) Value:0x12524508 Cost20/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 TaskProxy0x0000001E646B6260) Value:0x223262c8 Cost0/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 TaskProxy0x0000001E646B6380) Value:0xd8d0e088 Cost0/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 TaskProxy0x0000001E646B6800) Value:0xa9542bc8 Cost20/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 TaskProxy0x0000001E646B6500) Value:0x12524508 Cost20/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 TaskProxy0x0000001E646B6320) Value:0x12524088 Cost0/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 TaskProxy0x0000001E646B6380) Value:0xd8d0e088 Cost0/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 TaskProxy0x0000001E646B6680) Value:0x22326988 Cost20/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 TaskProxy0x0000001E646B6500) Value:0x12524508 Cost20/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 TaskProxy0x0000001E646B63E0) Value:0xa9542508 Cost0/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 TaskProxy0x0000001E646B6380) Value:0xd8d0e088 Cost0/10000)
Deadlock encountered .... Printing deadlock information
Wait-for graph


Aim to inspire rather than to teach.
SQL Server DBA
Post #1327047
Posted Monday, July 9, 2012 12:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, June 23, 2013 10:39 PM
Points: 231, Visits: 506
Looks like it is related to parallelism.

See below link

http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx


GulliMeel

Finding top n Worst Performing queries
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw
Post #1327051
Posted Monday, July 9, 2012 12:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:39 AM
Points: 321, Visits: 1,294
Thanks ..
It really helps to understand now.


Aim to inspire rather than to teach.
SQL Server DBA
Post #1327063
Posted Monday, July 9, 2012 12:41 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1327069
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse