Chapter 1: System Procedures

sp_showplan

Description

Displays the showplan output for any user connection for the current SQL statement or for a previous statement in the same batch.

Syntax

sp_showplan spid, batch_id output, context_id output, stmt_num output

To display the showplan output for the current SQL statement without specifying the batch_id, context_id, or stmt_num:

sp_showplan spid, null, null, null

Parameters

spid: is the process ID for any user connection. Use sp_who to see spids.

batch_id: is a unique, nonnegative number for a batch

context_id: is a unique number for every procedure (or trigger) executed in a batch.

stmt_num: is the number of the current statement within a batch. The stmt_num must be a positive number.

Examples

Example 1

Displays the query plan for the current statement running in the user session with a spid value of 99, as well as values for the batch_id, context_id, and statement_id parameters. These values can be used to retrieve query plans in subsequent iterations of sp_showplan for the user session with a spid of 99:

declare @batch int
declare @context int
declare @statement int
exec sp_showplan 99, @batch output, @context output, @statement output

Example 2

Displays the showplan output for the current statement running in the user session with a spid value of 99:

sp_showplan 99, null, null, null

Usage

sp_showplan displays the showplan output for a currently executing SQL statement or for a previous statement in the same batch.
To see the query plan for the previous statement within the same batch, execute sp_showplan again with the same parameter values, but subtract 1 from the statement number. Using this method, you can view all the statements in the statement batch back to query number one.
sp_showplan can be run independently of Adaptive Server Monitor™ Server.
If the context_id is greater than 0 for a SQL batch, the current statement is embedded in a stored procedure (or trigger) called from the original SQL batch. Select the sysprocesses row with the same spid value to display the procedure ID and statement ID.

Permissions

Only a System Administrator can execute sp_showplan.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect