Viewing the query plan of a SQL statement

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:

Adaptive Server uses the unique batch ID to synchronize the query plan with the batch text and other data retrieved by Adaptive Server Monitor.

NoteYou 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.