show_plan

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.

Syntax

show_plan(spid, batch_id, context_id, statement_number)

Parameters

Examples

Usage

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

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

Granular PermissionsDescription
Enabled

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

Disabled

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