|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL 2205, Profiler and DeadlocksWe are having trouble tracking down some deadlocks and so I decided to use
the new event Lock: Deadlock Graph Event and I set the Events Extraction Settings to Save Deadlock XML events separately, which was the only option I could check. When I attempt to open the file in Management Studio I get the following: "Failed to initialize deadlock control. There is an error in XML document (0, 0) Root element is missing." What did I do wrong? How do I get Profiler to actually record some useful information about deadlocks? Any help is greatly appreciated. Michael MacGregor Database Architect Hi Michael
Profiler probably DID record some useful information. You have to stop the trace that generated the deadlock trace before you can open the file. Show quote "Michael MacGregor" <nospam@nospam.com> wrote in message news:uEVbqxeAHHA.4060@TK2MSFTNGP03.phx.gbl... > We are having trouble tracking down some deadlocks and so I decided to use > the new event Lock: Deadlock Graph Event and I set the Events Extraction > Settings to Save Deadlock XML events separately, which was the only option > I could check. When I attempt to open the file in Management Studio I get > the following: > > "Failed to initialize deadlock control. > There is an error in XML document (0, 0) > Root element is missing." > > What did I do wrong? How do I get Profiler to actually record some useful > information about deadlocks? > > Any help is greatly appreciated. > > Michael MacGregor > Database Architect > Wow! I got a reply from Kalen Delaney. I'm honoured!
Profiler trace was stopped when I tried to open the deadlock trace. I can see the deadlock events (deadlock chain and deadlock event) in the main trace but I'm not sure how to interpret the information and was hoping the deadlock graph would be more useful. Michael MacGregor Database Architect Are you sure the trace was stopped and not just paused? Can you see the
deadlock graph in the Profiler window? Show quote "Michael MacGregor" <nospam@nospam.com> wrote in message news:O4wvS6eAHHA.1780@TK2MSFTNGP03.phx.gbl... > Wow! I got a reply from Kalen Delaney. I'm honoured! > > Profiler trace was stopped when I tried to open the deadlock trace. I can > see the deadlock events (deadlock chain and deadlock event) in the main > trace but I'm not sure how to interpret the information and was hoping the > deadlock graph would be more useful. > > Michael MacGregor > Database Architect > Definitely stopped. We had finished our load test. No I can't see the
deadlock graph in the Profiler window. I presumed that the event only actually gets logged to the xml file, should it also appear in the trace? Michael MacGregor Database Architect Ok, I tried again, this time I had two traces running, both with the
Deadlock Graph event selected, and neither displayed anything in the trace window when deadlocks occurred, I could see the Deadlock Chain and the Deadlock events but not the Deadlock Graph. What am I doing wrong? What should I be choosing on the Events Extraction Settings tab? Should I be checking the Save Deadlock XMP event separately checkbox? Is there another setting that I need to check? I find this very frustrating that I can't get it to work. Michael MacGregor Database Architect Yes, it sounds frustrating. It usually works with no trouble. When you
select the line in the trace for Deadlock Graph, the lower window should show the graph. Are you saying you don't even get a line in the trace for Deadlock graph, even though you have it selected in trace properties? Do you have filter set on it by any chance? The only problem I ever had was when the trace was still running, then I couldn't open the saved file in SSMS. As soon as I stopped the trace, I could open the file and would see the same graph in SSMS and I do in the Profiler Window. It's actually nicer in the Profiler window because you can zoom in to certain areas if you want to enlarge it. I don't think it matters if you choose to save the deadlock XML separately or in the same file, but I usually choose separately. If you're not getting a deadlock graph event in the trace itself, that is the bigger problem that what is saved in the file. Check for filters. And make sure you're connecting to SQL 2005. Show quote "Michael MacGregor" <nospam@nospam.com> wrote in message news:uIOXPjoAHHA.4348@TK2MSFTNGP04.phx.gbl... > Ok, I tried again, this time I had two traces running, both with the > Deadlock Graph event selected, and neither displayed anything in the trace > window when deadlocks occurred, I could see the Deadlock Chain and the > Deadlock events but not the Deadlock Graph. > > What am I doing wrong? > > What should I be choosing on the Events Extraction Settings tab? Should I > be checking the Save Deadlock XMP event separately checkbox? Is there > another setting that I need to check? > > I find this very frustrating that I can't get it to work. > > Michael MacGregor > Database Architect > I am seeing nothing. No graph in the Profiler itself.
The server is 2005 and the database I am monitoring is set for 2005 as well not 2000. Does the Deadlock Graph event not like a filter value, such as DatabaseID? Because I do have that set. Should I remove it? The trace is definitely stopped. I'm now trying to manually go thru the trace and find the deadlock chain and deadlock events, and match them up with the transactions that were involved. It'a real pain and extremely time-consuming. Thanks for all your help. This is a new job for me, I only started here in Sept, but I worked at another company for 6 years and we only ever had deadlock problems twice, and they were dead easy (no pun intended) to find and fix with some judicious indexing. According to the developers here they have never had deadlock problems before, which I find hard to believe because there doesn't seem to have been any major changes to the schema or the queries being used. These deadlocks are proving really difficult for me to track down, and there are so many, involving different tables and/or queries, it really concerns me. Due to the fact that there are remote locations and publication involved, they have used GUIDs universally as PKs, but even worse than that, have made almost all of them clustered which just makes me shudder. Michael MacGregor Database Architec I would suggest removing the filter and see if that changes anything. You
should be seeing the deadlock graph in the Profiler output if you also see Deadlock chain, etc. Show quote "Michael MacGregor" <nospam@nospam.com> wrote in message news:Obco9hpAHHA.4592@TK2MSFTNGP03.phx.gbl... >I am seeing nothing. No graph in the Profiler itself. > > The server is 2005 and the database I am monitoring is set for 2005 as > well not 2000. > > Does the Deadlock Graph event not like a filter value, such as DatabaseID? > Because I do have that set. Should I remove it? > > The trace is definitely stopped. > > I'm now trying to manually go thru the trace and find the deadlock chain > and deadlock events, and match them up with the transactions that were > involved. It'a real pain and extremely time-consuming. > > Thanks for all your help. > > This is a new job for me, I only started here in Sept, but I worked at > another company for 6 years and we only ever had deadlock problems twice, > and they were dead easy (no pun intended) to find and fix with some > judicious indexing. According to the developers here they have never had > deadlock problems before, which I find hard to believe because there > doesn't seem to have been any major changes to the schema or the queries > being used. These deadlocks are proving really difficult for me to track > down, and there are so many, involving different tables and/or queries, it > really concerns me. Due to the fact that there are remote locations and > publication involved, they have used GUIDs universally as PKs, but even > worse than that, have made almost all of them clustered which just makes > me shudder. > > Michael MacGregor > Database Architec > Well I haven't been able to retest so far today but we are going to do
another test tomorrow, and we are going to run Profiler on a different machine as well. And if all else fails, then I will enable the trace flags and restart the server and try it that way. Thanks again for your help and I will let you know how it goes. Michael MacGregor Database Architect My apologies, I should have posted an update. Everything is working fine
now. I find it a little surprising though that the deadlock graph event won't work when a DatabaseID is specified. Thanks again. Michael MacGregor Database Architect Thanks for the followup, Michael.
Unfortunately, deadlocks are just not associated with a particular database. Deadlocks are kept track of as an instance-wide phenomenon. If you look in the profiler output, you'll see that the databaseName and databaseID columns are not populated for the Deadlock Graph event. That means that internally they are being treated as NULL. So when you filter on databaseID, you are filtering on WHERE databaseID = <your dbid number> and a NULL will never return TRUE, so no deadlock graph rows will be returned. Show quote "Michael MacGregor" <nospam@nospam.com> wrote in message news:OG1XLQxDHHA.3224@TK2MSFTNGP04.phx.gbl... > My apologies, I should have posted an update. Everything is working fine > now. > > I find it a little surprising though that the deadlock graph event won't > work when a DatabaseID is specified. > > Thanks again. > > Michael MacGregor > Database Architect > Thanks for the explanation.
Now all I need to do is find a way to eliminate the deadlocks! ;-) Michael MacGregor Database Architect Michael MacGregor (nospam@nospam.com) writes:
> My apologies, I should have posted an update. Everything is working fine Provided that you have not checked "Exclude rows that do not have values", > now. > > I find it a little surprising though that the deadlock graph event won't > work when a DatabaseID is specified. the event should appear in the trace. That is, in theory it should, but in practice it doesn't. It's all quite strange, because if you filter for NTUserName or Duration instead, you do get the Deadlock Graph, and neither these two columns are populated by DeadlockGraph. And if filter for Database name and add the event LockAcquired to the trace, you get that event, even if does not populate DatabaseName. Why DeadlockGraph + Filter on database name/id is so special, I don't know, but in my thinking it is a bug, so I've submitted https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240737 -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks, Erland!
Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9886BA70A9B4FYazorman@127.0.0.1... > Michael MacGregor (nospam@nospam.com) writes: >> My apologies, I should have posted an update. Everything is working fine >> now. >> >> I find it a little surprising though that the deadlock graph event won't >> work when a DatabaseID is specified. > > Provided that you have not checked "Exclude rows that do not have values", > the event should appear in the trace. That is, in theory it should, but > in practice it doesn't. It's all quite strange, because if you filter for > NTUserName or Duration instead, you do get the Deadlock Graph, and neither > these two columns are populated by DeadlockGraph. And if filter for > Database name and add the event LockAcquired to the trace, you get that > event, even > if does not populate DatabaseName. > > Why DeadlockGraph + Filter on database name/id is so special, I don't > know, > but in my thinking it is a bug, so I've submitted > https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=240737 > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics