2

Captured the XML graph for a given deadlock involving some large / multi query sp's. However, not really sure how to interpret the line, stmtstart, stmtend of the frame element to determine which line of sproc TSQL encountered the deadlock. Line 214 doesn't seem to line up with any query in the SSMS editor but I am not sure how the engine normalizes sproc TSQL when line numbering. How do you resolve the captured line number with the line number as displayed in SSMS sproc definition?

<frame procname="MyDB.dbo.MyRecordsCleanup" line="214" stmtstart="19333" stmtend="19435" sqlhandle="0x030005007c47eb4c669a8d004eaf000001000000000000000000000000000000000000000000000000000000">
   exec sp_executesql @NSQL, N'@ARG1 int', @ARG1 = @MyArg1    
</frame>
user2368632
  • 1,133
  • 1
  • 15
  • 33

1 Answers1

4

The line number and statement offsets in the deadlock report are based on the actual stored procedure text. SSMS scripting will add additional lines for the sticky QUOTED_IDENTIFIER and ANSI_NULLS, add comments according to scripting options (Tools-->Options-->SQL Server Object Explorer-->Scripting), and alter the text to enclose identifiers or fix the name of a renamed procedure. As a result, offsets and line numbers in the scripted proc may differ from the actual text.

To avoid this behavior, retrieve the actual proc definition from sys.sql_modules or OBJECT_DEFINITION (per @SolomonRutzky's comment) and copy/paste the result into a new query window for analysis with this query:

SELECT definition
FROM sys.sql_modules AS m
WHERE object_id = OBJECT_ID(N'dbo.YourProc');

SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.YourProc'));

Note SSMS may truncate the result for large procedures according to query result options. Make sure the maximum characters retrieved for grid or text results is greater than your proc size. This is specified under Tools-->Options-->Query Results-->SQL Server. For grid results, ensure the Retain CR/LF on copy or save option is checked since it is off by default.

Another option to retrieve the proc text with sp_helptext with results to text, taking care the maximum characters retrieved is greater than the longest line.

EXEC sp_helptext N'dbo.YourProc';
Dan Guzman
  • 28,989
  • 2
  • 46
  • 71