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

Using DMVs to identify long running queries within a stored procedure

SELECT TOP 10 Min(query_stats.statement_text)                                                      AS statement_text,
( Sum(query_stats.total_elapsed_time) / Sum(query_stats.execution_count) ) / 1000000 AS avg_elapsed_time_seconds,
Sum(query_stats.total_elapsed_time) / 1000000                                        AS total_elapsed_time seconds,
Max(query_stats.total_elapsed_time) / 1000000                                        AS max_elapsed_time_seconds,
Sum(query_stats.execution_count)                                                     run_count,
query_stats.query_hash                                                               AS query_hash
FROM   (SELECT QS.*,
Substring(ST.text, ( QS.statement_start_offset / 2 ) + 1, ( ( CASE statement_end_offset
WHEN -1 THEN Datalength(ST.text)
ELSE QS.statement_end_offset
END – QS.statement_start_offset ) / 2 ) + 1) AS statement_text
FROM   sys.dm_exec_query_stats AS QS
CROSS APPLY sys.Dm_exec_sql_text(QS.sql_handle) AS ST
WHERE  st.objectid = Object_id(‘stored_procedure’)) AS query_stats
GROUP  BY query_stats.query_hash
ORDER  BY 2 DESC;

GO

 

Source: https://msdn.microsoft.com/en-us/library/ms189741%28v=sql.105%29.aspx