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

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.

• sp_showplan can run in sessions using chained transactions after you use sp_procxmode to change the transaction mode to anymode.

• 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:

See also