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

The permission checks for sp_showplan differ based on your granular permissions settings.

Auditing

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

See also