Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    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?

    Thanks.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I haven't used this in the the field (frankly not had one in our apps), but I did play around with it in the lab when this was written:
    Immediate deadlock notifications without changing existing code
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2010
    Posts
    19
    There are a couple of DMVs you can check. sys.dm_tran_locks will tell you about the resource (probably a DB) and whoever is trying to use it. sys.dm_exec_requests will give you the session ID, the database, and the type of command causing the lock. Best of all, you don't need Profiler to use them.

    If you have the time, though, it's a good idea to learn and use Profiler. You'll be able to tune your system much more closely (assuming it needs tuning).

  4. #4
    Join Date
    Oct 2009
    Posts
    19
    This query may help

    SELECT DB_NAME(p.DBID) AS 'Database name', waittime AS 'Wait time (milliseconds)',
    waittime/1000 AS 'Wait time (seconds)', [Status], program_name AS 'Program name',
    loginame AS 'Login name', login_time AS 'Login time', H.text
    FROM master..sysprocesses AS p
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS H
    WHERE blocked <> 0 ORDER BY spid
    And this site
    Detecting Deadlocks using Profiler in SQL Server 2005 - ExtremeExperts
    -----------------------------------
    Free SQL server monitoring for DBA's
    SQL DBA manager by BlueThames.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •