Viewing a nested procedure

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.

Table 5-4: Columns added to sysprocesses

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

NoteYou do not need the sa_role to run this select statement.