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

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

See also

System procedures sp_who