Profiler is an incredible performance hog, running it will certainly cause your phone to ring off the wall within moments of activating it. Picture instantly doubling the load on your production server.

is it possible to connect your sql2005 profiler to a sql2000 instance and get the same kind of troubleshooting info?

Is it possible to obtain the full sql that was passed in order to cause the deadlock, eg the parameters as well as the other statements within the same transaction?

Other than running a full trace and trying to match it up afterwards. I will have gigs of data to sift through if this was the case.

Thanks

If Profiler is used properly, the performance hit is minimal. They key is to only collect those events and data columns that are absolutely necessary. I have used in on production databases with no problems and no visible impact on users. In SQL Server 2008, you have the option to use Extended Events to capture data very similar to what Profiler captures, and it uses even less resources.

---

Brad M McGehee
Director of DBA Education
Red Gate Software

SQL Server 2005 Profiler can collect SQL Server 2000 data with no problem. But, SQL Server 2005 Profiler can't collect events from SQL Server 2000 that don't exist in 2000. Unfortunately, the Deadlock Graph event in 2005 does not exist in 2000.

---

Brad M McGehee
Director of DBA Education
Red Gate Software

The Deadlock event unfortuntely doesn't capture every last bit of data that you might want to troubleshoot the problem. That's why I always try to capture the code before the event so I can get a better picture of what is happening, assuming this data is necessary to resolve the problem. Yes, this might end up producing a lot of data on busy servers. But the data you need will be just before the deadlock event, so it should not be hard to find. Of course, if the server is so busy that collecting the data produces a performance problem because you have to run the trace for long time periods in order to "catch" the deadlock, you may not be able to collect the surrounding data, instead just capturing the deadlock event and using it to resolve the problem. This may make the troubleshooting process more difficult, but not impossible.

---

Brad M McGehee
Director of DBA Education
Red Gate Software

Thanks, exelent article! Brad, I got an "PAG: 9:1:3778" in logs for both nodes involved in the deadlock (2YK MsSql Server).. so, how can deal with this information?

There is little typo mistake on 1st paragraph

process one also wants an exclusive lock on object two, and object two wants an exclusive lock on object one.

It should be as below (it should be process instead of Object):

process one also wants an exclusive lock on object two, and object (Process) two wants an exclusive lock on object one.

Great article. Thanks.
I've a question when I see the deadlock graph of my database on hovering the mouse on right side oval shape box I see "FETCH API_CURSOR0000000009727CAE"
What does it mean?
Please help me understanding this one.

Hi, this is really very good article. i come to know about lot of new things. now i m going use it on my production server. Thx

Thanks, good article.

---

Robert Dennyson
Software Programmer,
BandyWorks Solutions.

Hi Brad, Thanks for posting this great article. I followed the steps provided by you to create deadlock graph. I was successfully able to create graph for deadlock occuring in my app. Only problem with deadlock graph in my application case is it has same index is cause of problem.
In above example two indexes held by two processes each one waiting for another. But in my application its same index (same proc) causing deadlock issue. Its key level lock. There aremultiple select, update stmts in proc.
Can you provide your valuable analysis on this problem? Highly appreciate it,
Thanks

Hi Brad, Thanks for posting this great article. I followed the steps provided by you to create deadlock graph. I was successfully able to create graph for deadlock occuring in my app. Only problem with deadlock graph in my application case is it has same index is cause of problem.
In above example two indexes held by two processes each one waiting for another. But in my application its same index (same proc) causing deadlock issue. Its key level lock. There aremultiple select, update stmts in proc.
Can you provide your valuable analysis on this problem? Highly appreciate it,
Thanks

Hi Brad, Thanks for posting this great article. I followed the steps provided by you to create deadlock graph. I was successfully able to create graph for deadlock occuring in my app. Only problem with deadlock graph in my application case is it has same index is cause of problem.
In above example two indexes held by two processes each one waiting for another. But in my application its same index (same proc) causing deadlock issue. Its key level lock. There aremultiple select, update stmts in proc.
Can you provide your valuable analysis on this problem? Highly appreciate it,
Thanks