Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What kind of Deadlock info is this..!!!!!


What kind of Deadlock info is this..!!!!!

Author
Message
logicinside22
logicinside22
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1391
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 TaskProxySad0x0000001E646B67A0) Value:0x232f2988 CostSad20/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 TaskProxySad0x0000001E646B6440) Value:0xd8d0e508 CostSad20/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 TaskProxySad0x0000001E646B63E0) Value:0xa9542508 CostSad0/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 TaskProxySad0x0000001E646B62C0) Value:0x4006a088 CostSad0/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 TaskProxySad0x0000001E646B67A0) Value:0x232f2988 CostSad20/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 TaskProxySad0x0000001E646B6440) Value:0xd8d0e508 CostSad20/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 TaskProxySad0x0000001E646B6320) Value:0x12524088 CostSad0/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 TaskProxySad0x0000001E646B62C0) Value:0x4006a088 CostSad0/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 TaskProxySad0x0000001E646B6740) Value:0x12524748 CostSad20/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 TaskProxySad0x0000001E646B6500) Value:0x12524508 CostSad20/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 TaskProxySad0x0000001E646B6260) Value:0x223262c8 CostSad0/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 TaskProxySad0x0000001E646B6380) Value:0xd8d0e088 CostSad0/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 TaskProxySad0x0000001E646B6800) Value:0xa9542bc8 CostSad20/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 TaskProxySad0x0000001E646B6500) Value:0x12524508 CostSad20/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 TaskProxySad0x0000001E646B6320) Value:0x12524088 CostSad0/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 TaskProxySad0x0000001E646B6380) Value:0xd8d0e088 CostSad0/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 TaskProxySad0x0000001E646B6680) Value:0x22326988 CostSad20/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 TaskProxySad0x0000001E646B6500) Value:0x12524508 CostSad20/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 TaskProxySad0x0000001E646B63E0) Value:0xa9542508 CostSad0/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 TaskProxySad0x0000001E646B6380) Value:0xd8d0e088 CostSad0/10000)
Deadlock encountered .... Printing deadlock information
Wait-for graph

Aim to inspire rather than to teach.
SQL Server DBA
Gullimeel
Gullimeel
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 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
logicinside22
logicinside22
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1391
Thanks ..
It really helps to understand now.

Aim to inspire rather than to teach.
SQL Server DBA
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47309 Visits: 44392
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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search