Use sp_showplan and the spid of the user connection in question to retrieve the query plan for the statement currently running on this connection. You can also use sp_showplan to view the query plan for a previous statement in the same batch.
The syntax is:
declare @batch int declare @context int declare @statement int execute sp_showplan <spid_value>, @batch_id= @batch output, @context_id= @context output, @stmt_num=@statement output
where:
batch_id – is the unique number for a batch
context_id – is a unique number for every procedure (or trigger) executed in the batch
stmt_num – is the number of the current statement within a batch
Adaptive Server uses the unique batch ID to synchronize the query plan with the batch text and other data retrieved by Adaptive Server Monitor.
You must be a System Administrator to execute sp_showplan.
For example, to see the query plan for the current statement for spid 99:
declare @batch int declare @context int declare @statement int exec sp_showplan 99, @batch output, @context output, @statement output
You can run the query plan procedure independently of Adaptive Server Monitor, regardless of whether or not Adaptive Server has allocated shared memory for SQL text.
