Displaying the SQL Plan for Cached Statements

Use the show_plan function to view the plan for a cached statement.

The syntax is:
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.

Note: A single entry in the statement cache may be associated with multiple, and possibly different, SQL plans. show_plan displays only one of them.
Parent topic: Setting the Statement Cache