SQl statement to monitor Sql Server Locks

Question

Wich SQL sentence (function or stored procedure) i can use to monitor an SQL Server DeadLock , caused by an UPDATE statement?

Answer 1

I think you're better placed to use SQL Profiler to monitor deadlocks - see here. SQL Profiler shows the deadlocks in a diagram to make it easier to determine what caused them.

Answer 2

try this query to see blocking processes, including the actual SQL query text:

SELECT
    r.session_id AS spid
        ,r.cpu_time,r.reads,r.writes,r.logical_reads 
        ,r.blocking_session_id AS BlockingSPID
        ,LEFT(OBJECT_NAME(st.objectid, st.dbid),50) AS ShortObjectName
        ,LEFT(DB_NAME(r.database_id),50) AS DatabaseName
        ,s.program_name
        ,s.login_name
        ,OBJECT_NAME(st.objectid, st.dbid) AS ObjectName
        ,SUBSTRING(st.text, (r.statement_start_offset/2)+1,( (CASE r.statement_end_offset
                                                                  WHEN -1 THEN DATALENGTH(st.text)
                                                                  ELSE r.statement_end_offset
                                                              END - r.statement_start_offset
                                                             )/2
                                                           ) + 1
                  ) AS SQLText
    FROM sys.dm_exec_requests                          r
        JOIN sys.dm_exec_sessions                      s ON r.session_id = s.session_id
        CROSS APPLY sys.dm_exec_sql_text (sql_handle) st
    --WHERE r.session_id!=@@SPID --uncomment to not see this query

deadlocks are easily trapped with the profiler:

Try running the run a trace in the profiler (pick the blank template), select the "deadlock graph event", and on the new tab that appears (Events Extraction Settings), save each (check "save deadlock XML events separately") in its own file. Open this file in an xml viewer and it will be easy to tell what is happening. Each process is contained, with an execution stack of procedure/trigger/etc calls, etc. and all locks are in there too.

Look at the "resource list" section of the file, it will show what is being locked and held by each process causing the deadlock. Figure out how to not have one of these locked and the deadlock will be resolved.

Let this trace run until the deadlock happens again, info is only recorded when a deadlock happens, so there isn't much overhead. If it never happens again, good it is solved, if not you have captured all the info.

Answer 3

SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0

But that will show you requests that are blocking, which is normal for SQL Server and doesn't indicate a deadlock. If SQL Server detects a deadlock it will kill one of the processes to allow the others to continue. So you can't use a function or stored procedure to monitor this as by the time it happens it has already finished.

You can either use the SQL Profiler or Trace Flags