Although sp_showplan allows you to view the query plan for the current statement, the actual statement that is running may exist within a procedure (or within a nested chain of procedures) called from the original SQL batch. Table 5-4 shows the columns in sysprocesses that contain information about these nested statements.
Column |
Datatype |
Specifies |
|---|---|---|
id |
Integer |
The object ID of the running procedure (or 0 if no procedure is running) |
stmtnum |
Integer |
The current statement number within the running procedure (or the SQL batch statement number if no procedure is running) |
linenum |
Integer |
The line number of the current statement within the running stored procedure (or the line number of the current SQL batch statement if no procedure is running) |
This information is saved in sysprocesses, regardless of whether SQL text is enabled or any memory is allocated for SQL text.
To display the id, stmtnum, and linenum columns, enter:
select id, stmtnum, linenum from sysprocesses where spid = spid_of_hung_session
You do not need the sa_role to
run this select statement.
