SQL Response reports deadlocks where SQl server does not rep
JeroenvH
Posts: 4
Hello,
I'm evaluating SQL Response right now, and I encounter the following:
SQL Reponse is reporting strange deadlocks: The victim SPID for example is 0.
The running Porofiler trace (with deadlock graph on) does NOT report a deadlock; also does the perfmon counter deadlock/sec not increase.
Why does sql-response report a deadlock where SQL-Server does not ?
The detailed info from sql-reponse:
Deadlock encountered .... Printing deadlock information
Wait-for graph
Node:1
Port: 0x00000001D467C960 Xid Slot: 12, Wait Slot: 3, Task: 0x0000000006AF62C8, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:93 BatchID:0 ECID:19 TaskProxy:(0x00000001EFF24200) Value:0x6af62c8 Cost:(20/0)
SPID: 93 ECID: 19 Statement Type: SELECT Line #: 3
Input Buf: No Event:
Node:2
Port: 0x0000000151B5B430 Xid Slot: 13, Wait Slot: 2, Task: 0x00000002A46042C8, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:93 BatchID:0 ECID:21 TaskProxy:(0x00000001EFF242C0) Value:0xa46042c8 Cost:(20/0)
SPID: 93 ECID: 21 Statement Type: SELECT Line #: 3
Input Buf: No Event:
Node:3
Port: 0x00000003AD44BEE0 Xid Slot: 3, Wait Slot: 13, Task: 0x000000037C3782C8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:93 BatchID:0 ECID:9 TaskProxy:(0x000000022D837D90) Value:0x7c3782c8 Cost:(0/10000)
SPID: 93 ECID: 9 Statement Type: SELECT Line #: 3
Input Buf: No Event:
Node:4
Port: 0x000000018BB5F270 Xid Slot: 2, Wait Slot: 12, Task: 0x000000039612B948, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:93 BatchID:0 ECID:11 TaskProxy:(0x000000022D837E50) Value:0x9612b948 Cost:(0/10000)
SPID: 93 ECID: 11 Statement Type: SELECT Line #: 3
Input Buf: No Event:
I'm evaluating SQL Response right now, and I encounter the following:
SQL Reponse is reporting strange deadlocks: The victim SPID for example is 0.
The running Porofiler trace (with deadlock graph on) does NOT report a deadlock; also does the perfmon counter deadlock/sec not increase.
Why does sql-response report a deadlock where SQL-Server does not ?
The detailed info from sql-reponse:
Deadlock encountered .... Printing deadlock information
Wait-for graph
Node:1
Port: 0x00000001D467C960 Xid Slot: 12, Wait Slot: 3, Task: 0x0000000006AF62C8, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:93 BatchID:0 ECID:19 TaskProxy:(0x00000001EFF24200) Value:0x6af62c8 Cost:(20/0)
SPID: 93 ECID: 19 Statement Type: SELECT Line #: 3
Input Buf: No Event:
Node:2
Port: 0x0000000151B5B430 Xid Slot: 13, Wait Slot: 2, Task: 0x00000002A46042C8, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
ResType:ExchangeId Stype:'AND' SPID:93 BatchID:0 ECID:21 TaskProxy:(0x00000001EFF242C0) Value:0xa46042c8 Cost:(20/0)
SPID: 93 ECID: 21 Statement Type: SELECT Line #: 3
Input Buf: No Event:
Node:3
Port: 0x00000003AD44BEE0 Xid Slot: 3, Wait Slot: 13, Task: 0x000000037C3782C8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:93 BatchID:0 ECID:9 TaskProxy:(0x000000022D837D90) Value:0x7c3782c8 Cost:(0/10000)
SPID: 93 ECID: 9 Statement Type: SELECT Line #: 3
Input Buf: No Event:
Node:4
Port: 0x000000018BB5F270 Xid Slot: 2, Wait Slot: 12, Task: 0x000000039612B948, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:93 BatchID:0 ECID:11 TaskProxy:(0x000000022D837E50) Value:0x9612b948 Cost:(0/10000)
SPID: 93 ECID: 11 Statement Type: SELECT Line #: 3
Input Buf: No Event:
Comments
SQL Response sets a trace flag on SQL Server that causes deadlock information to be written to the SQL Server's ERRORLOG, and then it reads the ERRORLOG to gather the output that you had quoted above.
I'm not sure how profiler and performance counters do this, but I would imagine that you have to set the trace flags to detect deadlocks there as well? For instance, DBCC TRACEON(1204) will put deadlock information into the SQL Server log.
Since yesterday SQL-Response reported several more 'ghost' deadlocks and one actual deadlock (with an Victim spid) which also was trapped by profiler with an dead-lock graph.
I'll look into it.