InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 01:17:35 GMT20http://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxI have been successful in setting up the automated WMI deadlock capture but what I can't find is how to display the data after I've captured it. I've looked multiple places and have been trying on my own to get it displayed but without any success. I'm not a developer but just a DBA who needs some help. thanksdebbieFri, 13 Aug 2010 12:17:43 GMTdebrunklehttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxNice article... It will be more helpful for the beginners like us:-)Thu, 08 Oct 2009 06:30:24 GMTASP.SQL.Learnerhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxExtremely practical and useful! Thank you for sharing.Wed, 22 Jul 2009 23:00:20 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxBeside creating proper indexes, developers also need to lock tables in the same order all the time to avoid deadlock situation.DerekMon, 20 Jul 2009 18:20:49 GMTDerek Jinhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspx[quote][b]Jonathan Kehayias (2/3/2009)[/b][hr][quote][b]doobya (2/3/2009)[/b][hr]SQL 2005 has xml deadlock report in profilerwhich will track deadlock down to tables and stored procedure statementsI think your solution does not solve the deadlock - just makes it less likelyIf the db+app had been designed correctly the omission of the index would have reduced performance but not created deadlocks[/quote]Take a look at the article from yesterdy. A missing/incorrect index can most definitely be the cause of a deadlock. In fact most deadlocks are simple index issues. Very rarely do I see a deadlock that doesn't involve cross locking indexes on a single table, though it does happen at times.[/quote]Here's a repo of one half of a deadlock scenario where a seemimgly innocent join caused an index scan.The solution I used was to use a temporary table instead.[code]CREATE TABLE MyTable(PK INT NOT NULL PRIMARY KEY, Val INT)INSERT INTO MyTable(PK,Val)SELECT 1,10 UNION ALL SELECT 2,10----DECLARE @x XMLSET @x='<ROOT><ROW PK="1" VAL="50"/><ROW PK="2" VAL="100"/></ROOT>'------ Joining directly to XML caused a clustered index scan on MyTable.PKUPDATE MyTableSET Val=r.value('@VAL','INT')FROM @x.nodes('/ROOT/ROW') AS x(r)WHERE MyTable.PK=r.value('@PK','INT')---- DECLARE @T TABLE(PK INT NOT NULL PRIMARY KEY, Val INT)INSERT INTO @T(PK,Val)SELECT r.value('@PK','INT'), r.value('@VAL','INT')FROM @x.nodes('/ROOT/ROW') AS x(r)------ Joining to a temporary table, this turns into a clustered index seek on MyTable.PKUPDATE MyTableSET Val=r.ValFROM @T rWHERE MyTable.PK=r.PK----DROP TABLE MyTable[/code]Mon, 20 Jul 2009 02:36:29 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxBrilliant article and great comments!Sun, 19 Jul 2009 18:59:21 GMThunkwangshzhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxFound it to be an interesting article; but, am unlikely to have that kind of problem as we always have primary keys on our tables. :-)Fri, 17 Jul 2009 17:49:59 GMTEd Sanfordhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxVery nice. Quick question, if there was only RID info (Node:1 RID: 8:1:231:0) in the deadlock entry could you still lookup the object info?Fri, 17 Jul 2009 12:45:29 GMTRob-406527http://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxVery nice step wise article.Thanks for sharing Praveen!Fri, 17 Jul 2009 00:36:10 GMTAmit Whttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspx[quote][b]harry9katz (2/6/2009)[/b][hr]Right U are but U can see all the blocked paid's by order of waittime and can see what spid A is Doing by DBCC inputbuffur(spid a)and decide if to kill the process or not.[/quote]But if it's a genuine deadlock, SQL server will have beaten you to it - more or less as soon as the circular dependency arises (i.e. 1 has A and wants B, and 2 has B and wants A - or perhaps a more complicated case) the lock monitor will choose the one with the least to lose (i.e. usually the one holding the least locks) and kills the transaction it is in, which rolls back and releases the locks, so the other can proceed. Even if you were aware that it had happened, nothing is left blocking anything - this is specifically what a deadlock is (sometimes called a "deadly embrace" in the two-process case particularly) - which is distinct from the case of one process just blocking another (for example if an application had opened a transaction and not closed it)Fri, 06 Feb 2009 08:12:46 GMTTony Websterhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxRight U are but U can see all the blocked paid's by order of waittime and can see what spid A is Doing by DBCC inputbuffur(spid a)and decide if to kill the process or not.this is a fast solution not clean as in the articleFri, 06 Feb 2009 06:51:09 GMTharry9katzhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspx[quote][b]harry9katz (2/4/2009)[/b][hr]U can find the deadlock on line by running the script:select * from master..sysprocesses where blocked&gt;0 and spid&lt;&gt;blockedorder by waittime descand find the spidthen use : dbcc inputbuffer (spid no) [/quote]That isn't quite the same thing - that finds a blocked process. Put simply, a deadlock is when process 1 is locking resource A and wants resource B, and process 2 is locking B and wants A - obviously, if you leave them to it, they'll wait forever, so SQL Server has the lock monitor thread to detect this and pick one of the two processes to summarily execute, leaving the information in the log if you have the trace flag enabled.Thu, 05 Feb 2009 09:25:29 GMTTony Websterhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxU can find the deadlock on line by running the script:select * from master..sysprocesses where blocked&gt;0 and spid&lt;&gt;blockedorder by waittime descand find the spidthen use : dbcc inputbuffer (spid no) Wed, 04 Feb 2009 09:23:11 GMTharry9katzhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxUseful intro on identifying deadlocks. The part that concerned me more was:"As part of the upgrade, the application team performed modifications to the stored procedure code such that every insertion may cause up to 10 updates in the table."Why would so many updates need to happen on the same table from a single insert? The sprocs/functions used in between must be low level and small to have caused so many updates?If it's status updates, that's ALOT of status updates.I guess as long as the code works now and it's no deadlocking it's ok? Wed, 04 Feb 2009 08:56:13 GMTblurpyblurphttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspx"As there was only one non-clustered index existing on that table, I created a clustered index on the table on the ID column with this command"I understand not having an index on the ID column.Would creating a non-clustered index on this column haved solved the problem as well,or did you have a specific reason to chose for the clusterred one.Wed, 04 Feb 2009 04:07:22 GMTbvandenbrinkhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspx:D thanks for taking the time to put this together...very usefulTue, 03 Feb 2009 21:56:19 GMTmordikhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspx[quote][b]doobya (2/3/2009)[/b][hr][quote][b]Jonathan Kehayias (2/3/2009)[/b]Take a look at the article from yesterdy. A missing/incorrect index can most definitely be the cause of a deadlock. In fact most deadlocks are simple index issues. Very rarely do I see a deadlock that doesn't involve cross locking indexes on a single table, though it does happen at times.[/quote]That is an interesting article.I am surprised that the lock acquisition against a single table for a single statement isn't batched and serialized![/quote]I am not sure what you mean in this statement? The SH locks are required to ensure consistency during the lookup process across resources. You can certainly change issolation levels and affect they types of locks taken by the SELECT, but that isn't necessarily a solution to the problem depending on the issolation level that you decide to use.Tue, 03 Feb 2009 16:18:22 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspx[quote][b]amiller (2/3/2009)[/b][hr]:) Very interesting and with many information I didn't know. One more, does the ALLOW_PAGE_LOCKS option on indexes in the tables involved matters for deadlocks?thanksAlbert[/quote]Albert,YES. These options make a difference. See the following thread where having ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS OFF caused deadlocking to occur.[url]http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/c5356da9-167e-4143-a64b-9a39dbc176b6/[/url]Tue, 03 Feb 2009 16:15:47 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspx[quote][b]Jonathan Kehayias (2/3/2009)[/b]Take a look at the article from yesterdy. A missing/incorrect index can most definitely be the cause of a deadlock. In fact most deadlocks are simple index issues. Very rarely do I see a deadlock that doesn't involve cross locking indexes on a single table, though it does happen at times.[/quote]That is an interesting article.I am surprised that the lock acquisition against a single table for a single statement isn't batched and serialized!Tue, 03 Feb 2009 14:18:54 GMTDataDoghttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxgreat response, except, what if the indexes exist? The article is valid as you say, for finding deadlocks in general and therefore has worth.rTue, 03 Feb 2009 14:17:47 GMTrgranucchttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxtotally agree: how hard would an interface using the simple steps outlined in the article be to generate a quick pull of that page.Would help in profiling and devo evoluton too.sigh.....Tue, 03 Feb 2009 14:14:47 GMTrgranucchttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspx:) Very interesting and with many information I didn't know. One more, does the ALLOW_PAGE_LOCKS option on indexes in the tables involved matters for deadlocks?thanksAlbertTue, 03 Feb 2009 12:47:04 GMTamiller-724327http://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspx[quote][b]doobya (2/3/2009)[/b][hr]SQL 2005 has xml deadlock report in profilerwhich will track deadlock down to tables and stored procedure statementsI think your solution does not solve the deadlock - just makes it less likelyIf the db+app had been designed correctly the omission of the index would have reduced performance but not created deadlocks[/quote]Take a look at the article from yesterdy. A missing/incorrect index can most definitely be the cause of a deadlock. In fact most deadlocks are simple index issues. Very rarely do I see a deadlock that doesn't involve cross locking indexes on a single table, though it does happen at times.Tue, 03 Feb 2009 12:18:56 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxSQL 2005 has xml deadlock report in profilerwhich will track deadlock down to tables and stored procedure statementsI think your solution does not solve the deadlock - just makes it less likelyIf the db+app had been designed correctly the omission of the index would have reduced performance but not created deadlocksTue, 03 Feb 2009 12:10:17 GMTDataDoghttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxVery interesting news about SQL 2008 and Extended Events. I'm looking forward to when we can upgrade our production servers to that.For now the WMI deadlock system has provided a nice and easy to read history of deadlocks for us and we've hooked in an e-mail notification as well. CPU usage for WMI is roughly 2-3% on our box and we have capacity to spare so it's been worth it.Tue, 03 Feb 2009 12:00:34 GMTsureshothttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxSQL 101 - Always make sure your tables have indexes to avoid full table scans.Looks as though the DBA and/or developers need a little coaching.Tue, 03 Feb 2009 11:53:20 GMTDallas Martinhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspx[quote][b]sureshot (2/3/2009)[/b][hr]Great article on investigating the deadlock. Just a note that if you're running SQL 2005/2008, the trace flag 1222 (http://msdn.microsoft.com/en-us/library/ms178104.aspx) provides a lot more information so you don't have to do quite a much undocumented digging (though I found it very interesting).Instead of the trace flag, I'd personally recommend setting up WMI to log the deadlock info: http://technet.microsoft.com/en-us/library/ms186385(SQL.90).aspx.[/quote]The WMI alerts can actually be a bit more resource intensive and problemattic to setup. There are a number of more moving parts involved with the WMI alerts where as the Trace Flags or a SQL Trace has minimal configuration required. In the end, if you use Trace Flag 1222 in SQL Server 2005/2008, the deadlock graph is the same from WMI, SQL Trace, Profiler, and the Trace Flags.SQL 2008 offers Extended Events, watch for an upcoming article that I have already submitted which capture deadlock information natively while your server is running. Watch out though, the deadlock information changed to a new format in SQL 2008 that is only output by Extended Events and only Extended Events can capture and display a multi-victim deadlock, which is why there was a change made to the deadlock graph formatting in 2008 from Extended Events.As I mentioned yesterday, deadlocks aren't necessarily the problem. If your app is coded correctly it can handled the error raised and rerun the statement and it will most likely succeed on the second attempt.Tue, 03 Feb 2009 11:21:29 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxGreat article on investigating the deadlock. Just a note that if you're running SQL 2005/2008, the trace flag 1222 (http://msdn.microsoft.com/en-us/library/ms178104.aspx) provides a lot more information so you don't have to do quite a much undocumented digging (though I found it very interesting).Instead of the trace flag, I'd personally recommend setting up WMI to log the deadlock info: http://technet.microsoft.com/en-us/library/ms186385(SQL.90).aspx.Tue, 03 Feb 2009 08:39:27 GMTsureshothttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspx;) Nice article, thanks for sharing!Tue, 03 Feb 2009 08:32:56 GMTbondistricthttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxNice how-to article. But I wonder why Microsoft does not provide better support for deadlock situations. Hunting SQL server deadlocks is still like in the stone age days of programming ( in the above article even with non-documented features). Assumed SQL Server "knows" the reason, why not simply tell it?Tue, 03 Feb 2009 07:31:30 GMTGerhard Schmeusserhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxJust a quick one - don't forget that SQL Server isn't the only source of deadlocks. I recently helped out on an issue where a single-threaded application DLL on one thread was blocked by a SQL Server lock, but at the same time, access to the application DLL from the locking process was also blocked. No deadlock victim, just timed-out queries and a lot of pain to the system's users.Classic deadlock... but only one of the locks was within SQL Server. 1204 is great, but troubleshooting SQL Server deadlocks is simple compared with tracking down the problem I was helping to diagnose.Tue, 03 Feb 2009 06:12:15 GMTJeremy Huppatzhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxVery good article.Tue, 03 Feb 2009 05:03:28 GMTMarkusBhttp://www.sqlservercentral.com/Forums/Topic648621-1470-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/deadlock/64315/">A Deadlock Occurence and Resolution</A>[/B]Tue, 03 Feb 2009 01:12:51 GMTpraveen voleti