Chapter 11: Diagnosing System Problems

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 11-4 shows the columns in sysprocesses that contain information about these nested statements.

**Table 11-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

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

View this book as PDF