sp_showplan

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

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.

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
```

There are additional considerations when using sp_showplan:

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 SAP ASE 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.

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `monitor qp performance` privilege.
Disabled	With granular permissions disabled, you must be a user with sa_role.

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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