Unanswered: Finding source of deadlock problem
Have an Web application which calls a stored procedure when a user selects a Save button. The process transfers a moderate amount of data which the stored procedure validates before writing data to tables (SQL2008).
The stored procedure contains about 30 SQL statements and, during testing, the procedure has never failed. In the production instance, however, I recently checked the Application Event Log on the server and I see that there are hourly occurrences of deadlocks associated with this stored procedure. The error message states that the system has killed one of the deadlocked process.
This system is associated with a timecard application and it is used by up to 5,000 employees every day. It's hard to say how many of these users are concurrent, but I would say that it is possible that there are several hundred logged-on at any time. It is unlikely, however, that any two users are sharing the same data and certainly not to the degree that I would see this error happening more than a dozen times a day.
Amazingly I have never had a single complaint relating to the processes associated with this procedure, but I don't like the idea of a problem out there that I do not understand.
I am thinking I am going to need to use SQL Trace/Profiler to investigate this problem. I have never used SQL Trace/Profiler.
It looks like I can filter for the invocation of this particular stored procedure (by name) and I can even see the individual T-SQL statements that are executed. I am hoping to match the occurrence of the Event Log failure with activity in the SQL Trace/Profiler to find my issue. I am hoping to see that most invocations of this procedure execute all 30 of the T-SQL statements, but that when an invocation matches an Event Log failure, I am hoping to see that the procedure exits at a particular SQL statement, narrowing-down my focus.
As someone who has not done this before, does this sound like the methodology that I am going to have to use to find this deadlock problem, or is there some other method out there that anyone can recommend?
Maverick Software Design
(847) 864-3600 x2