Use sp_showplan and the spid of the user connection in question to retrieve the query plan for the statement currently running on the connection. You can also use sp_showplan to view the query plan for a previous statement in the same batch.
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, enter:
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.
