Identify the actual query involved in deadlock when it is not visible in deadlock graph

Sometimes, actual query involved in deadlock is not visible in deadlock graph. We can use below SQL to identify the query in such cases based on stmtstart, stmtend, sqlhandle information that is available in deadlock graph.

————————–

DECLARE @sqlhandle varbinary(64)
DECLARE @stmtstart INT
DECLARE @stmtend INT

SET @sqlhandle = 0x03000500eab1e835d941410165a400000100000000000000
SET @stmtstart = 20244
SET @stmtend = 20368

SELECT
       substring(qt.[text],qs.statement_start_offset/2,
         (case when qs.statement_end_offset = -1
          then len(convert(nvarchar(max), qt.[text])) * 2
          else qs.statement_end_offset end – qs.statement_start_offset)/2
         ) SQLText,
  qt.[text] batch,
  db.[name] datbase,
  qt.objectid,
  qs.execution_count,
  qs.total_worker_time,
  qs.total_elapsed_time,
  qs.total_logical_reads,
  qs.total_physical_reads,
  qs.total_logical_writes,
  qs.[sql_handle],
  qs.statement_start_offset,
  qs.statement_end_offset  
FROM    sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) qt
        LEFT JOIN sys.databases db ON qt.[dbid] = db.database_id
WHERE   qs.[sql_handle] = @sqlhandle
AND     qs.statement_start_offset = @stmtstart
AND     qs.statement_end_offset = @stmtend

From

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/58833801-02cc-4f33-a4bc-4a8501e7d227/statement-start-and-end-in-deadlock-graph
https://gallery.technet.microsoft.com/scriptcenter/4df31ad5-227c-495d-9c6c-d91d047e89ee

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s