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
is the process ID for any user connection. Use sp_who to see spids.
is a unique, nonnegative number for a batch
is a unique number for every procedure (or trigger) executed in a batch.
is the number of the current statement within a batch. The stmt_num must be a positive number.
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
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
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.
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.
Only a System Administrator can execute sp_showplan.
System procedures sp_who