View the plan for a cached statement using these functions:
show_plan(spid, batch_id, context_id, statement_number)
Where:
spid – process ID for any user connection.
batch_id – unique number for a batch.
context_id – unique number for every procedure (or trigger)
statement_number – number of the current statement within a batch.
For a statement that is not performing well, you can change the plans by altering the optimizer settings or specifying an abstract plan.
When you specify the first int variable in the existing show_plan argument as “-1”, show_plan treats the second parameter as a SSQLID.
A single entry in the statement cache may be associated
with multiple, and possibly different, SQL plans. show_plan displays
only one of them.
