https://www.insidesql.org/blogs/andreaswolter/?disp=comments en-EU http://backend.userland.com/rss 60 Tue, 05 Apr 2016 07:21:45 +0000 dirkhondong [Member] c8336@https://www.insidesql.org/blogs/ Hi Andreas, you already got an email from me with my solution to get it up and running. It seems that it now works like a charm. To answer oyur "call to action": AffectedProcesses Number of Deadlocks 2 102 3 348 4 106 5 5 7 1 https://www.insidesql.org/blogs/andreaswolter/2015/02/free-deadlock-collector-parser-based-on-extended-events-system_health-session#c8336 Fri, 01 Apr 2016 11:47:42 +0000 andreaswolter [Member] c8334@https://www.insidesql.org/blogs/ Hi Dirk, indeed there is an issue that under circumstances the current duplicate avoidance is not sufficient. I already have a fixed version but not tested and uploaded yet. It's on my workbench for months already :-( I hope by May I can provide you with the code Sorry about the inconvenience. https://www.insidesql.org/blogs/andreaswolter/2015/02/free-deadlock-collector-parser-based-on-extended-events-system_health-session#c8334 Fri, 01 Apr 2016 11:24:09 +0000 Dirk Hondong [Visitor] c8333@https://www.insidesql.org/blogs/ Hi Andreas, 1st of all thank you for sharing such a great tool. I really appreciate your work. I just have set up the Collector and started the collector job for the first time. Unfortunately I get a duplicate key error Msg 2601, Level 14, State 1, Procedure ins_DeadLock, Line 365 Cannot insert duplicate key row in object 'Locking.DeadLock' with unique index 'UQNCL_DeadLock_DeadlockHash'. The duplicate key value is (0x02df629045332d56ecc2b32d746fe03d9813bbee). I am currently investigating what went wrong. If you need further information just let me know and I'll provide you more details. https://www.insidesql.org/blogs/andreaswolter/2015/02/free-deadlock-collector-parser-based-on-extended-events-system_health-session#c8333 Sun, 04 Oct 2015 18:54:42 +0000 andreaswolter [Member] c8283@https://www.insidesql.org/blogs/ Hello Martin again, sorry for the late answer - Somehow the notifications for new comments seem to not get through. Your deadlock graph is very interesting. It is not really big. That cannot be the reason. Either it fits in the buffer, or it's not there at all. But as I can see all the processes belong to the same SPID. This and the Wait events show that it has do do with parallelism.So basically it must have gotten killed himself. https://www.insidesql.org/blogs/andreaswolter/2015/02/free-deadlock-collector-parser-based-on-extended-events-system_health-session#c8283 Tue, 25 Aug 2015 06:21:29 +0000 Parse Integration Services [Visitor] c8276@https://www.insidesql.org/blogs/ Love it! hanks for the insight. After a bit of poking around I have just understood the concrete deadlock realizing that deadlocks could be much more complex than just two DDL Statements conflicting in different sessions. https://www.insidesql.org/blogs/andreaswolter/2015/02/free-deadlock-collector-parser-based-on-extended-events-system_health-session#c8276 Thu, 06 Aug 2015 06:54:38 +0000 Martin Guth [Visitor] c8271@https://www.insidesql.org/blogs/ Hi Andreas, I got quite a number (150 a day) of deadlocks collected with seemingly non sense information. AffectedProcesses are 5. SQLText_1 and _2 are NULL. InputBuffer_1 and _2 contains an empty string. For deadlock graph see the end of the message. Odd is with these deadlocks that the victim-list XML element is empty. XDL file cannot be opened in SSMS: error message is "Failed to initialize deadlock control. Key cannot be null. Parameter name:key". SQL Server Version is 2008 with SP4 - 10.0.6000.29 Could this be resulting from a situation where there is not enough space in the ring buffer to store the deadlock information? Thanks in advance for sharing any insight Martin Here's an example of the deadlock graph xml (tags are replaced by HTML entities in order for them to be inserted in this message): <deadlock> <victim-list /> <process-list> <process id="processd71500088" taskpriority="0" logused="10000" waittime="2650" schedulerid="13" kpid="928" status="suspended" spid="243" sbid="0" ecid="8" priority="0" trancount="0" lastbatchstarted="2015-08-05T08:52:09.973" lastbatchcompleted="2015-08-05T08:52:09.973" clientapp="jTDS" hostname="3CDNWEB02" hostpid="123" isolationlevel="read committed (2)" xactid="255195918" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000006d15c906920385a930f40a7319bee76fb1fb7f71" /> </executionStack> <inputbuf /> </process> <process id="processae3322c8" taskpriority="0" logused="10000" waittime="2649" schedulerid="9" kpid="5680" status="suspended" spid="243" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2015-08-05T08:52:09.973" lastbatchcompleted="2015-08-05T08:52:09.973" clientapp="jTDS" hostname="3CDNWEB02" hostpid="123" isolationlevel="read committed (2)" xactid="255195918" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000006d15c906920385a930f40a7319bee76fb1fb7f71" /> </executionStack> <inputbuf /> </process> <process id="processd71501288" taskpriority="0" logused="10000" waittime="2656" schedulerid="13" kpid="5436" status="suspended" spid="243" sbid="0" ecid="6" priority="0" trancount="0" lastbatchstarted="2015-08-05T08:52:09.973" lastbatchcompleted="2015-08-05T08:52:09.973" clientapp="jTDS" hostname="3CDNWEB02" hostpid="123" isolationlevel="read committed (2)" xactid="255195918" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000006d15c906920385a930f40a7319bee76fb1fb7f71" /> </executionStack> <inputbuf /> </process> <process id="processd71501948" taskpriority="0" logused="10000" waittime="2656" schedulerid="13" kpid="4956" status="suspended" spid="243" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2015-08-05T08:52:09.973" lastbatchcompleted="2015-08-05T08:52:09.973" clientapp="jTDS" hostname="3CDNWEB02" hostpid="123" isolationlevel="read committed (2)" xactid="255195918" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000006d15c906920385a930f40a7319bee76fb1fb7f71" /> </executionStack> <inputbuf /> </process> <process id="process468f4c8" taskpriority="0" logused="10000" waittime="2656" schedulerid="9" kpid="5496" status="suspended" spid="243" sbid="0" ecid="6" priority="0" trancount="0" lastbatchstarted="2015-08-05T08:52:09.973" lastbatchcompleted="2015-08-05T08:52:09.973" clientapp="jTDS" hostname="3CDNWEB02" hostpid="123" isolationlevel="read committed (2)" xactid="255195918" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame procname="" line="1" sqlhandle="0x020000006d15c906920385a930f40a7319bee76fb1fb7f71" /> </executionStack> <inputbuf /> </process> </process-list> <resource-list> <exchangeEvent id="Pipeb96d834b0" WaitType="e_waitPipeNewRow" nodeId="67"> <owner-list> <owner id="processd71501288" /> </owner-list> <waiter-list> <waiter id="processd71500088" /> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipeb93439cb0" WaitType="e_waitPipeGetRow" nodeId="67"> <owner-list> <owner id="processd71500088" /> </owner-list> <waiter-list> <waiter id="processae3322c8" /> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipeb7cee8680" WaitType="e_waitPipeGetRow" nodeId="14"> <owner-list> <owner id="processd71501948" /> <owner id="process468f4c8" /> </owner-list> <waiter-list> <waiter id="processd71501288" /> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipeb7cee8700" WaitType="e_waitPipeNewRow" nodeId="14"> <owner-list> <owner id="processae3322c8" /> </owner-list> <waiter-list> <waiter id="processd71501948" /> </waiter-list> </exchangeEvent> <exchangeEvent id="Pipeb7cee8700" WaitType="e_waitPipeNewRow" nodeId="14"> <owner-list> <owner id="processae3322c8" /> </owner-list> <waiter-list> <waiter id="process468f4c8" /> </waiter-list> </exchangeEvent> </resource-list> </deadlock> https://www.insidesql.org/blogs/andreaswolter/2015/02/free-deadlock-collector-parser-based-on-extended-events-system_health-session#c8271 Tue, 28 Apr 2015 13:13:24 +0000 Martin Guth [Visitor] c8158@https://www.insidesql.org/blogs/ Hi Andreas, thanks for the insight. After a bit of poking around I have just understood the concrete deadlock realizing that deadlocks could be much more complex than just two DDL Statements conflicting in different sessions. Regarding my version...the problem I mentioned ocurred on Microsoft SQL Server 2014 - 12.0.2480.0 (X64) . Martin https://www.insidesql.org/blogs/andreaswolter/2015/02/free-deadlock-collector-parser-based-on-extended-events-system_health-session#c8158 Tue, 28 Apr 2015 12:13:27 +0000 andreaswolter [Member] c8157@https://www.insidesql.org/blogs/ Hello Martin, your guess is absolutely right: InputBuffer indeed is what it's called: the current buffer taken from the deadlock graph. SQLText is what sys.dm_exec_sql_text() returns after I provide it with the sql_handle from the deadlocks process-executionStack So as an example SQLText may contain the whole Procedure/Trigger-Text whereas InputBuffer contains the call including parameter values. - In case of a trigger it's the statement that lead to the trigger being executed :-) I just saw your comment (notifications were not set up). I will check on that as soon as I can. Normally I should have a Where-Clause to prevent double entries which otherwise would occur all the time. I must have missed something. Which version are you on btw? Thanks for the feedback Andreas https://www.insidesql.org/blogs/andreaswolter/2015/02/free-deadlock-collector-parser-based-on-extended-events-system_health-session#c8157