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


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




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.


With granular permissions enabled, you must be a user with monitor qp performance privilege.


With granular permissions disabled, you must be a user with sa_role.


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



Audit option


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

Related reference