Retrieves the query plan for a specified server process (the target process) and a SQL statement. This function is called several times by sp_showplan because a built-in function can return just one value per call, but sp_showplan must return several values to the client.


show_plan(spid, batch_id, context_id, statement_number)




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 “-”, 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.

See also sp_showplan in Reference Manual: Procedures


ANSI SQL – Compliance level: Transact-SQL extension.


The permission checks for show_plan differ based on your granular permissions settings.

Granular PermissionsDescription

With granular permissions enabled, you must be a user with monitor qp performance permission to execute show_plan.


With granular permissions disabled, you must be a user with sa_role to execute show_plan.