Capturing Deadlock Info
-
Has anyone ever read an article that describes how to capture which two stored procedures or queries are involved in a deadlock and placing that info into a table for future study?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
If you are running a Profiler trace and have the Deadlocking events turned on, you can capture that sort of information. Also, if I remember right, the blocking scripts provided by Microsoft capture the information as well, but the recommendation is for that to be set to capture every so often, say 15 minutes, so it'll only capture that information during the instanteous slice of time it's capturing.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
My focus of concern is as continuous a check as possible and without using profiler.
These blocking scripts. Where can I find them? and do they capture all blocking or can they focus exclusively on deadlocks?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
SQL Server 2000:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271509
SQL Server 7:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q251004
The amount of information captured can be pretty extensive. As a result, it's generally not a good idea to run the script continuously, but rather to sample at specified time intervals.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Thanks for the links. I have read the article on blocking for SQL Server 7.0 and it doesn't appear to allow specific checking for deadlocks. It looks like it would capture a deadlock while capturing other locking info, but you'd have to scrutinize the entire result set. Plus it outputs to a text file. If the SQL Server 2000 article is similar then it won't do much different.
I have searched Microsofts knowledge base, PASS's knowledge portal, and http://www.swynk.com/ and have found nothing that does what I am developing. This was the reason I asked if others had read such an article. I am planning to write an article about the process and don't want to write something that has already been written.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
I've not seen any in depth articles on deadlocking. You are right about the Microsoft scripts. They are designed for blocking in general.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Haven't seen anything specific to deadlocking. Part of the problem is that you need to capture the events up to the deadlock to track it down, not once the deadlock exists.
I'd be interested in seeing an article about how you can capture this. Go for it, Robert!
Steve Jones
-
I'm already able to capture the spids (only the ones involved in the deadlock). I'm trying to figure out the best way to continuously scan for deadlocks. I tried putting a trigger on sysprocesses but every attempt has been blocked by SQL Server. I'm trying a different approach now.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
Triggers aren't allowed on system tables. Besides, a trigger on sysprocesses would fire every time a connection was made to the database.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
I've discovered that. However, I think I could have made the trigger only fire when blocking occured.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
Well that was an excersice in futility. I can make the process work, but without a trigger on sysprocesses, the performance hit is very high. I just experimented with profiler and can get the same info with almost no processors being used to do it. And I just learned that profiler allows you to save the stats it gathers in a table. The only advantage what I developed has over profiler is that mine only gathers deadlock info and no other activity on the server.
However I doubt that would be enough to over come the performance disadvantage.
Is there a way to make profiler only capture the queries that were involved in the deadlock?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
Unfortunately, no. Profiler is merely a running log of user activity. As a result, it would have to keep a cache of info and do interpretation on the fly in order to report only on deadlock information, since the processes will be executing commands prior to the deadlock occuring.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
What you could do is make some esimate of how long it takes for the deadlock to appear, then every xx minutes, remove older information from your profiler table. This would limit the size of the table, but still allow you to capture information. Of course, once you have the info, you have to then moveit or mark it so it doesn't get deleted. And you have to figure out what information you need to keep.
Steve Jones
-
I found what blocks you from adding a trigger to a system table; the flag in sysobjects that indicates the table is a system table. Using a test table, I added triggers, then changed it to a system table, and tested the triggers. They still worked. Using another test table I made it a system table and then tried to add a trigger and was blocked.
Now, I know I am treading on thin ground, or whatever you want to call it when some starts talking about modifying system tables. In any case, here goes: what would you think of a process that required you to change sysprocesses to a user table, add a trigger, and then switch it back to a system table? This is all theoretical at this point because I haven't tested it yet. I'm worried that as soon as I change sysprocesses to a user table the entire SQL Server on that computer will stop functioning (worst case scenerio). Has anyone ever tried anything like this before? I'll need to set up a test SQL Server so that if I crash it, it won't matter.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
The main thing is that changes to system tables, even querying system tables, is unsupported. If you have an issue and MS finds out, you get no support, zero, nada, nothing. Not something you want to go to yor boss with. At least I don't.
The problem with capturing deadlock info is that you need information PRIOR to the actual event. You need to know what activity occurred between two connections prior to the deadlock being detected. This is why I think you need profiler plus some process to filter out the deadlock info based on a deadlock being detected.
It's a challenge and I'd be intersted in reading it, but in my ten years, I have rarely encountered deadlocks. When I do it is always the result of poor programming practices.
Steve Jones
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply