February 14, 2012 at 11:08 am
Hi all,
I currently have Redgate's SQL Monitor running on our SQL environment, and I noticed yesterday afternoon that a Deadlock was detected and deadlock alerts began rolling in every couple minutes for several hours. There was no noticeable latency issues on the production server, and user experience was not interrupted (or at least if it was, I was never made aware of it). Anyway, as soon as I noticed the deadlock's i began investigating possible causes. Unfortunately the monitor was not providing me with much detail to go on, so I resorted to the Error Logs and ran a Profiler trace looking for deadlock events.
Profiler caught one or two in the time i was monitoring it, but the only message it provided was "A parallel worker thread was involved in a deadlock". Deadlock Chain and Graph were enabled in the trace. I even induced a deadlock purposely to ensure I was capturing the trace correctly...i was, and a graph was produced.
Then, in the error logs, all i continue to see, repeated hundreds and hundreds of times for several hours is...
2012-02-14 08:52:17.74 spid8s Deadlock encountered .... Printing deadlock information
2012-02-14 08:52:17.74 spid8s Wait-for graph
2012-02-14 08:52:17.74 spid8s
2012-02-14 08:52:17.74 spid8s Node:1
2012-02-14 08:52:17.74 spid8s Port: 0x000000055FF75040 Xid Slot: 45, Wait Slot: 18, Task: 0x0000000007BEB708, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 08:52:17.74 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:64 TaskProxy:(0x00000004D324B9B0) Value:0x7beb708 Cost:(20/0)
2012-02-14 08:52:17.74 spid8s
2012-02-14 08:52:17.74 spid8s Node:2
2012-02-14 08:52:17.74 spid8s Port: 0x00000002EB106C40 Xid Slot: 27, Wait Slot: 7, Task: 0x0000000005189B88, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 08:52:17.74 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:46 TaskProxy:(0x00000003D5F5C5C0) Value:0x5189b88 Cost:(20/0)
2012-02-14 08:52:17.74 spid8s
2012-02-14 08:52:17.74 spid8s Node:3
2012-02-14 08:52:17.75 spid8s Port: 0x000000048CBB2D30 Xid Slot: 34, Wait Slot: 21, Task: 0x0000000000714988, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 08:52:17.75 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:27 TaskProxy:(0x00000004D324BDD0) Value:0x714988 Cost:(20/0)
2012-02-14 08:52:17.75 spid8s
2012-02-14 08:52:17.75 spid8s Node:4
2012-02-14 08:52:17.75 spid8s Port: 0x00000007C0870B00 Xid Slot: 30, Wait Slot: 9, Task: 0x00000005A51622C8, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 08:52:17.75 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:26 TaskProxy:(0x00000004D324BD70) Value:0xa51622c8 Cost:(20/0)
2012-02-14 08:52:17.75 spid8s
2012-02-14 08:52:17.75 spid8s Node:5
2012-02-14 08:52:17.75 spid8s Port: 0x000000019F3AD680 Xid Slot: 7, Wait Slot: 34, Task: 0x0000000000DDD948, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 08:52:17.75 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:72 TaskProxy:(0x00000004D324BCB0) Value:0xddd948 Cost:(0/10000)
2012-02-14 08:52:17.75 spid8s
2012-02-14 08:52:17.75 spid8s Node:6
2012-02-14 08:52:17.75 spid8s Port: 0x000000043A78CEF0 Xid Slot: 9, Wait Slot: 27, Task: 0x00000001A8B24748, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 08:52:17.75 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:71 TaskProxy:(0x00000004D324BC50) Value:0xa8b24748 Cost:(0/10000)
2012-02-14 08:52:17.75 spid8s
2012-02-14 08:52:17.75 spid8s Node:7
2012-02-14 08:52:17.75 spid8s Port: 0x00000008595E0CA0 Xid Slot: 18, Wait Slot: 47, Task: 0x00000000076BEBC8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 08:52:17.75 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:70 TaskProxy:(0x00000003D5F5CEC0) Value:0x76bebc8 Cost:(0/10000)
2012-02-14 08:52:17.75 spid8s
2012-02-14 08:52:17.75 spid8s Node:8
2012-02-14 08:52:17.75 spid8s Port: 0x0000000115B8BEE0 Xid Slot: 23, Wait Slot: 30, Task: 0x0000000005A40BC8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 08:52:17.75 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:49 TaskProxy:(0x00000004D324AF10) Value:0x5a40bc8 Cost:(0/10000)
2012-02-14 08:52:17.85 spid8s Deadlock encountered .... Printing deadlock information
2012-02-14 08:52:17.85 spid8s Wait-for graph
2012-02-14 08:52:17.85 spid8s
2012-02-14 08:52:17.85 spid8s Node:1
2012-02-14 08:52:17.85 spid8s Port: 0x000000038C4B9F50 Xid Slot: 45, Wait Slot: 19, Task: 0x0000000007BEB708, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 08:52:17.85 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:64 TaskProxy:(0x00000004D324B9B0) Value:0x7beb708 Cost:(20/0)
2012-02-14 08:52:17.85 spid8s
2012-02-14 08:52:17.85 spid8s Node:2
2012-02-14 08:52:17.85 spid8s Port: 0x00000002EB106C40 Xid Slot: 27, Wait Slot: 7, Task: 0x0000000005189B88, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 08:52:17.85 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:46 TaskProxy:(0x00000003D5F5C5C0) Value:0x5189b88 Cost:(20/0)
2012-02-14 08:52:17.85 spid8s
2012-02-14 08:52:17.85 spid8s Node:3
2012-02-14 08:52:17.85 spid8s Port: 0x000000048CBB2D30 Xid Slot: 34, Wait Slot: 21, Task: 0x0000000000714988, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 08:52:17.85 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:27 TaskProxy:(0x00000004D324BDD0) Value:0x714988 Cost:(20/0)
2012-02-14 08:52:17.85 spid8s
2012-02-14 08:52:17.85 spid8s Node:4
2012-02-14 08:52:17.85 spid8s Port: 0x00000007C0870B00 Xid Slot: 30, Wait Slot: 9, Task: 0x00000005A51622C8, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 08:52:17.85 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:26 TaskProxy:(0x00000004D324BD70) Value:0xa51622c8 Cost:(20/0)
2012-02-14 08:52:17.85 spid8s
2012-02-14 08:52:17.85 spid8s Node:5
2012-02-14 08:52:17.85 spid8s Port: 0x000000019F3AD680 Xid Slot: 7, Wait Slot: 34, Task: 0x0000000000DDD948, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 08:52:17.85 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:72 TaskProxy:(0x00000004D324BCB0) Value:0xddd948 Cost:(0/10000)
2012-02-14 08:52:17.85 spid8s
2012-02-14 08:52:17.85 spid8s Node:6
2012-02-14 08:52:17.85 spid8s Port: 0x000000043A78CEF0 Xid Slot: 9, Wait Slot: 27, Task: 0x00000001A8B24748, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 08:52:17.85 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:71 TaskProxy:(0x00000004D324BC50) Value:0xa8b24748 Cost:(0/10000)
2012-02-14 08:52:17.85 spid8s
2012-02-14 08:52:17.85 spid8s Node:7
2012-02-14 08:52:17.85 spid8s Port: 0x00000003F05DF0F0 Xid Slot: 19, Wait Slot: 47, Task: 0x00000000076E94C8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 08:52:17.85 spid8s ResType:ExchangeId Stype:'AND' SPID:57 BatchID:0 ECID:69 TaskProxy:(0x00000003D5F5CE60) Value:0x76e94c8 Cost:(0/10000)
2012-02-14 08:52:17.85 spid8s
2012-02-14 08:52:17.85 spid8s Node:8
Doesn't seem to provide much data...or at least not that I can see. I am fairly new to deadlock detection and resolution, though I do understand what a deadlock is and what causes them. But I am stumped on this...I cannot seem to find any usable data that would allow me to figure out what the cause of the deadlock is.
Any ideas would be seriously helpful.
-Brian
February 14, 2012 at 12:29 pm
Switch traceflag 1222 on. That will result in a detailed deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here. Redgate's tool uses traceflag 1204 which produces less useful, less detailed information.
DBCC TRACEON(1222,-1)
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
February 14, 2012 at 2:09 pm
Awesome, thanks. Ill post the results.
February 14, 2012 at 2:31 pm
I enabled that trace flag, and about 15 minutes into it, it looks like I am still getting the same results as before. I have noticed that at least on a few of the log entries it tells me statement type ("SELECT")
2012-02-14 13:26:37.09 spid8s Deadlock encountered .... Printing deadlock information
2012-02-14 13:26:37.09 spid8s Wait-for graph
2012-02-14 13:26:37.09 spid8s
2012-02-14 13:26:37.09 spid8s Node:1
2012-02-14 13:26:37.09 spid8s Port: 0x000000031D246060 Xid Slot: 26, Wait Slot: 15, Task: 0x000000000071FDC8, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 13:26:37.09 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:59 TaskProxy:(0x0000000793F137D0) Value:0x71fdc8 Cost:(20/0)
2012-02-14 13:26:37.09 spid8s SPID: 118 ECID: 59 Statement Type: SELECT Line #: 12
2012-02-14 13:26:37.09 spid8s Input Buf: No Event:
2012-02-14 13:26:37.09 spid8s
2012-02-14 13:26:37.09 spid8s Node:2
2012-02-14 13:26:37.09 spid8s Port: 0x00000003A1532060 Xid Slot: 25, Wait Slot: 2, Task: 0x00000006E16C4748, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 13:26:37.09 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:26 TaskProxy:(0x0000000793F13D70) Value:0xe16c4748 Cost:(20/0)
2012-02-14 13:26:37.09 spid8s
2012-02-14 13:26:37.09 spid8s Node:3
2012-02-14 13:26:37.09 spid8s Port: 0x0000000352C99B80 Xid Slot: 15, Wait Slot: 34, Task: 0x0000000007702508, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 13:26:37.09 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:61 TaskProxy:(0x00000002C78B6B60) Value:0x7702508 Cost:(0/10000)
2012-02-14 13:26:37.09 spid8s
2012-02-14 13:26:37.09 spid8s Node:4
2012-02-14 13:26:37.09 spid8s Port: 0x000000068C990910 Xid Slot: 10, Wait Slot: 25, Task: 0x0000000868884E08, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 13:26:37.09 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:70 TaskProxy:(0x0000000793F13BF0) Value:0x68884e08 Cost:(0/10000)
2012-02-14 13:26:37.20 spid8s Deadlock encountered .... Printing deadlock information
2012-02-14 13:26:37.20 spid8s Wait-for graph
2012-02-14 13:26:37.20 spid8s
2012-02-14 13:26:37.20 spid8s Node:1
2012-02-14 13:26:37.20 spid8s Port: 0x0000000227388F20 Xid Slot: 26, Wait Slot: 17, Task: 0x000000000071FDC8, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 13:26:37.20 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:59 TaskProxy:(0x0000000793F137D0) Value:0x71fdc8 Cost:(20/0)
2012-02-14 13:26:37.20 spid8s
2012-02-14 13:26:37.20 spid8s Node:2
2012-02-14 13:26:37.20 spid8s Port: 0x00000003A1532060 Xid Slot: 25, Wait Slot: 2, Task: 0x00000006E16C4748, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 13:26:37.20 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:26 TaskProxy:(0x0000000793F13D70) Value:0xe16c4748 Cost:(20/0)
2012-02-14 13:26:37.20 spid8s
2012-02-14 13:26:37.20 spid8s Node:3
2012-02-14 13:26:37.20 spid8s Port: 0x00000003E2DA9E50 Xid Slot: 17, Wait Slot: 34, Task: 0x00000000076B4748, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 13:26:37.20 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:64 TaskProxy:(0x00000002C78B6C80) Value:0x76b4748 Cost:(0/10000)
2012-02-14 13:26:37.20 spid8s
2012-02-14 13:26:37.20 spid8s Node:4
2012-02-14 13:26:37.20 spid8s Port: 0x000000068C990910 Xid Slot: 10, Wait Slot: 25, Task: 0x0000000868884E08, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 13:26:37.20 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:70 TaskProxy:(0x0000000793F13BF0) Value:0x68884e08 Cost:(0/10000)
2012-02-14 13:26:37.31 spid8s Deadlock encountered .... Printing deadlock information
2012-02-14 13:26:37.31 spid8s Wait-for graph
2012-02-14 13:26:37.31 spid8s
2012-02-14 13:26:37.31 spid8s Node:1
2012-02-14 13:26:37.31 spid8s Port: 0x00000002C3C8F620 Xid Slot: 26, Wait Slot: 0, Task: 0x000000000071FDC8, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 13:26:37.31 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:59 TaskProxy:(0x0000000793F137D0) Value:0x71fdc8 Cost:(20/0)
2012-02-14 13:26:37.31 spid8s
2012-02-14 13:26:37.31 spid8s Node:2
2012-02-14 13:26:37.31 spid8s Port: 0x00000003A1532060 Xid Slot: 25, Wait Slot: 2, Task: 0x00000006E16C4748, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 13:26:37.31 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:26 TaskProxy:(0x0000000793F13D70) Value:0xe16c4748 Cost:(20/0)
2012-02-14 13:26:37.31 spid8s
2012-02-14 13:26:37.31 spid8s Node:3
2012-02-14 13:26:37.31 spid8s Port: 0x00000007C5CD2AB0 Xid Slot: 0, Wait Slot: 34, Task: 0x0000000005188988, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 13:26:37.31 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:50 TaskProxy:(0x00000002C78B6740) Value:0x5188988 Cost:(0/10000)
2012-02-14 13:26:37.31 spid8s
2012-02-14 13:26:37.31 spid8s Node:4
2012-02-14 13:26:37.31 spid8s Port: 0x000000068C990910 Xid Slot: 10, Wait Slot: 25, Task: 0x0000000868884E08, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 13:26:37.31 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:70 TaskProxy:(0x0000000793F13BF0) Value:0x68884e08 Cost:(0/10000)
2012-02-14 13:26:37.42 spid8s Deadlock encountered .... Printing deadlock information
2012-02-14 13:26:37.42 spid8s Wait-for graph
2012-02-14 13:26:37.42 spid8s
2012-02-14 13:26:37.42 spid8s Node:1
2012-02-14 13:26:37.42 spid8s Port: 0x000000062E608A70 Xid Slot: 26, Wait Slot: 1, Task: 0x000000000071FDC8, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 13:26:37.42 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:59 TaskProxy:(0x0000000793F137D0) Value:0x71fdc8 Cost:(20/0)
2012-02-14 13:26:37.42 spid8s
2012-02-14 13:26:37.42 spid8s Node:2
2012-02-14 13:26:37.42 spid8s Port: 0x00000003A1532060 Xid Slot: 25, Wait Slot: 2, Task: 0x00000006E16C4748, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-14 13:26:37.42 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:26 TaskProxy:(0x0000000793F13D70) Value:0xe16c4748 Cost:(20/0)
2012-02-14 13:26:37.42 spid8s
2012-02-14 13:26:37.42 spid8s Node:3
2012-02-14 13:26:37.42 spid8s Port: 0x00000001F8A4C820 Xid Slot: 1, Wait Slot: 34, Task: 0x000000058FA7E088, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 13:26:37.42 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:49 TaskProxy:(0x00000002C78B66E0) Value:0x8fa7e088 Cost:(0/10000)
2012-02-14 13:26:37.42 spid8s
2012-02-14 13:26:37.42 spid8s Node:4
2012-02-14 13:26:37.42 spid8s Port: 0x000000068C990910 Xid Slot: 10, Wait Slot: 25, Task: 0x0000000868884E08, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-14 13:26:37.42 spid8s ResType:ExchangeId Stype:'AND' SPID:118 BatchID:0 ECID:70 TaskProxy:(0x0000000793F13BF0) Value:0x68884e08 Cost:(0/10000)
February 14, 2012 at 2:39 pm
That's the output from 1204, there should also be another deadlock graph before or after, far more detail.
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
February 14, 2012 at 5:06 pm
Unfortunately I am not seeing any additional information, before, or after - Probably why I am having trouble tracking anything down...The error log has been pretty much the same for the past several hours. No additional info, and no
less, and I have confirmed trace flag 1204 and 1222 are on.
February 15, 2012 at 4:42 am
If you click on the Deadlock Alert in SQL Monitor it should bring up a window that has the basic deadlock information in it.
You can see the victim and the blocked resource and the query that each was running. If you click on the details tab you should see the output from traceflag 1222. You should be able to get everything you need right from there.
Click on the attachment to see an example.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 15, 2012 at 9:13 am
So far, I have not seen that information from SQL Monitor nor have I seen it in the error logs. I have somewhere around 1600 deadlock alerts as of now and they all have almost the exact information as in the attached images. The only thing that differs is the SPID number. I have not seen any information on who the victim is, what the query executed was, or anything else. SQL Profiler (set to trace all deadlock events) continues to produce only one deadlock event "Parallel query worker thread was involved in a deadlock", but displays nothing else of use.
To date, I have tried disabling various jobs in the SQL Server Agent that I thought could be causing the conflict, I have disabled the agent all together, and I have rebooted the server. Neither solution has stopped the deadlock alerts from flowing in.
Since restarting, and setting trace flag's 1204 and 1222 on, here is all that I am seeing in the error log, repeated over and over...the only difference is the SPID.
2012-02-15 07:51:29.87 spid63 DBCC TRACEON 1222, server process ID (SPID) 63. This is an informational message only; no user action is required.
2012-02-15 07:54:28.17 spid104 DBCC TRACEON 1204, server process ID (SPID) 104. This is an informational message only; no user action is required.
2012-02-15 07:58:21.92 spid8s Deadlock encountered .... Printing deadlock information
2012-02-15 07:58:21.92 spid8s Wait-for graph
2012-02-15 07:58:21.92 spid8s
2012-02-15 07:58:21.92 spid8s Node:1
2012-02-15 07:58:21.92 spid8s Port: 0x0000000387BE8CA0 Xid Slot: 27, Wait Slot: 7, Task: 0x0000000004E4FB88, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-15 07:58:21.93 spid8s ResType:ExchangeId Stype:'AND' SPID:95 BatchID:0 ECID:35 TaskProxy:(0x000000068257A1A0) Value:0x4e4fb88 Cost:(20/0)
2012-02-15 07:58:21.93 spid8s SPID: 95 ECID: 35 Statement Type: SELECT Line #: 12
2012-02-15 07:58:21.93 spid8s Input Buf: No Event:
2012-02-15 07:58:21.93 spid8s
2012-02-15 07:58:21.93 spid8s Node:2
2012-02-15 07:58:21.93 spid8s Port: 0x00000006A9C7BBB0 Xid Slot: 31, Wait Slot: 14, Task: 0x0000000004E2B708, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-15 07:58:21.93 spid8s ResType:ExchangeId Stype:'AND' SPID:95 BatchID:0 ECID:68 TaskProxy:(0x00000005CEE77B30) Value:0x4e2b708 Cost:(20/0)
2012-02-15 07:58:21.93 spid8s SPID: 95 ECID: 68 Statement Type: SELECT Line #: 12
2012-02-15 07:58:21.93 spid8s Input Buf: No Event:
2012-02-15 07:58:21.93 spid8s
2012-02-15 07:58:21.93 spid8s Node:3
2012-02-15 07:58:21.93 spid8s Port: 0x0000000534546800 Xid Slot: 0, Wait Slot: 27, Task: 0x0000000004687708, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-15 07:58:21.93 spid8s ResType:ExchangeId Stype:'AND' SPID:95 BatchID:0 ECID:58 TaskProxy:(0x00000005CEE77770) Value:0x4687708 Cost:(0/10000)
2012-02-15 07:58:21.93 spid8s SPID: 95 ECID: 58 Statement Type: SELECT Line #: 12
2012-02-15 07:58:21.93 spid8s Input Buf: No Event:
2012-02-15 07:58:21.93 spid8s
2012-02-15 07:58:21.93 spid8s Node:4
2012-02-15 07:58:21.93 spid8s Port: 0x00000003CD939720 Xid Slot: 14, Wait Slot: 24, Task: 0x0000000007534988, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-15 07:58:21.93 spid8s ResType:ExchangeId Stype:'AND' SPID:95 BatchID:0 ECID:72 TaskProxy:(0x000000068257AF80) Value:0x7534988 Cost:(0/10000)
2012-02-15 07:58:21.93 spid8s SPID: 95 ECID: 72 Statement Type: SELECT Line #: 12
2012-02-15 07:58:21.93 spid8s Input Buf: No Event:
2012-02-15 07:58:26.93 spid8s Deadlock encountered .... Printing deadlock information
2012-02-15 07:58:26.93 spid8s Wait-for graph
2012-02-15 07:58:26.93 spid8s
2012-02-15 07:58:26.93 spid8s Node:1
2012-02-15 07:58:26.93 spid8s Port: 0x00000004EE648A70 Xid Slot: 40, Wait Slot: 14, Task: 0x000000000759D4C8, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-15 07:58:26.93 spid8s ResType:ExchangeId Stype:'AND' SPID:95 BatchID:0 ECID:52 TaskProxy:(0x00000005CEE770F0) Value:0x759d4c8 Cost:(20/0)
2012-02-15 07:58:26.93 spid8s SPID: 95 ECID: 52 Statement Type: SELECT Line #: 12
2012-02-15 07:58:26.93 spid8s Input Buf: No Event:
2012-02-15 07:58:26.93 spid8s
2012-02-15 07:58:26.93 spid8s Node:2
2012-02-15 07:58:26.93 spid8s Port: 0x00000003D80951F0 Xid Slot: 31, Wait Slot: 16, Task: 0x0000000004E2B948, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-15 07:58:26.93 spid8s ResType:ExchangeId Stype:'AND' SPID:95 BatchID:0 ECID:38 TaskProxy:(0x000000068257A2C0) Value:0x4e2b948 Cost:(20/0)
2012-02-15 07:58:26.93 spid8s SPID: 95 ECID: 38 Statement Type: SELECT Line #: 12
2012-02-15 07:58:26.93 spid8s Input Buf: No Event:
2012-02-15 07:58:26.93 spid8s
2012-02-15 07:58:26.93 spid8s Node:3
2012-02-15 07:58:26.93 spid8s Port: 0x00000005347DAFD0 Xid Slot: 0, Wait Slot: 31, Task: 0x0000000004687708, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-15 07:58:26.93 spid8s ResType:ExchangeId Stype:'AND' SPID:95 BatchID:0 ECID:58 TaskProxy:(0x00000005CEE77770) Value:0x4687708 Cost:(0/10000)
2012-02-15 07:58:26.93 spid8s
2012-02-15 07:58:26.93 spid8s Node:4
2012-02-15 07:58:26.93 spid8s Port: 0x00000003CD939720 Xid Slot: 14, Wait Slot: 24, Task: 0x0000000007534988, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-15 07:58:26.93 spid8s ResType:ExchangeId Stype:'AND' SPID:95 BatchID:0 ECID:72 TaskProxy:(0x000000068257AF80) Value:0x7534988 Cost:(0/10000)
2012-02-15 07:58:29.44 spid8s Deadlock encountered .... Printing deadlock information
2012-02-15 07:58:29.44 spid8s Wait-for graph
2012-02-15 07:58:29.44 spid8s
2012-02-15 07:58:29.44 spid8s Node:1
2012-02-15 07:58:29.44 spid8s Port: 0x000000078E0BCBE0 Xid Slot: 28, Wait Slot: 0, Task: 0x000000000438EE08, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-15 07:58:29.44 spid8s ResType:ExchangeId Stype:'AND' SPID:95 BatchID:0 ECID:64 TaskProxy:(0x00000005CEE779B0) Value:0x438ee08 Cost:(20/0)
2012-02-15 07:58:29.44 spid8s SPID: 95 ECID: 64 Statement Type: SELECT Line #: 12
2012-02-15 07:58:29.44 spid8s Input Buf: No Event:
2012-02-15 07:58:29.44 spid8s
2012-02-15 07:58:29.44 spid8s Node:2
2012-02-15 07:58:29.44 spid8s Port: 0x00000002AC4B31C0 Xid Slot: 31, Wait Slot: 4, Task: 0x0000000004E2B948, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1
2012-02-15 07:58:29.44 spid8s ResType:ExchangeId Stype:'AND' SPID:95 BatchID:0 ECID:38 TaskProxy:(0x000000068257A2C0) Value:0x4e2b948 Cost:(20/0)
2012-02-15 07:58:29.44 spid8s
2012-02-15 07:58:29.44 spid8s Node:3
2012-02-15 07:58:29.44 spid8s Port: 0x00000005347DAFD0 Xid Slot: 0, Wait Slot: 31, Task: 0x0000000004687708, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-15 07:58:29.44 spid8s ResType:ExchangeId Stype:'AND' SPID:95 BatchID:0 ECID:58 TaskProxy:(0x00000005CEE77770) Value:0x4687708 Cost:(0/10000)
2012-02-15 07:58:29.44 spid8s
2012-02-15 07:58:29.44 spid8s Node:4
2012-02-15 07:58:29.44 spid8s Port: 0x00000002F8B40470 Xid Slot: 0, Wait Slot: 24, Task: 0x00000000046874C8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-02-15 07:58:29.44 spid8s ResType:ExchangeId Stype:'AND' SPID:95 BatchID:0 ECID:51 TaskProxy:(0x000000068257A7A0) Value:0x46874c8 Cost:(0/10000)
2012-02-15 07:58:29.44 spid8s SPID: 95 ECID: 51 Statement Type: SELECT Line #: 12
2012-02-15 07:58:29.44 spid8s Input Buf: No Event:
Based on SQL Monitor's performance graphs, it appears the sever is under no unusual distress either...throughout the entire time I have been receiving these deadlock notifications I am almost spot on with my normal server baseline.
This all started early Monday morning, around 3 am when a weekly maintenance plan runs that rebuilds the indices. At that time, I noticed a deadlock when trying to alter an index. Since then, I have been continually alerted to deadlock events. Im stumped...
February 15, 2012 at 9:22 am
Would it be possible for you to post the entire errorlog?
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
February 15, 2012 at 9:37 am
Ah, you have a parallel execution deadlock. Here's a blog detailing this one. It is hard to troubleshoot. The article should help. This is frequently a bug and there's possibly a service pack or hotfix you can apply to your server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 15, 2012 at 9:56 am
I was actually just starting to think that might be what was going on...I just found this article about 20 minutes before your response. Thanks for the reply...I'll take a look at the article in more detail. Ill post the results.
February 16, 2012 at 7:13 am
You can try the brute-force-hammer of OPTION (MAXDOP 1) or you can dig into it more and try to find a fix from bad programming, suboptimal indexing or bad schema design. If you are not familiar with this type of stuff consider getting a professional tuner on board to assist and mentor you on how to approach this type of problem in the future.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply