sp_help_qplan

Description

Reports information about an abstract plan.

Syntax

sp_help_qplan id [, mode ]

Parameters

id: is the ID of the abstract plan.

mode: is the type of report to print, one of the following:

Mode

Information returned

full

The plan ID, group ID, and hash key, and the full query and plan text.

brief

The same as full, but only prints about 80 characters of the query and plan, rather than the full query and plan. This is the default mode.

list

The hash key, ID, and first 20 characters of the query and plan.

Mode	Information returned
full	The plan ID, group ID, and hash key, and the full query and plan text.
brief	The same as full, but only prints about 80 characters of the query and plan, rather than the full query and plan. This is the default mode.
list	The hash key, ID, and first 20 characters of the query and plan.

Examples

Example 1

Prints the brief abstract plan report:

sp_help_qplan 800005881

gid         hashkey     id
 ----------- ----------- -----------
           5  2054169974   937054374

 query
-------------------------------------------------------------------------- 
 select type, avg(price) from titles group by type

 query_plan
-------------------------------------------------------------------------- 
 ( plan
    ( store Worktab1
        ( i_scan type_price titles )
    ) 
    ( t_scan ( ...

Example 2

Prints the full abstract plan report:

sp_help_qplan 784005824, full

Usage

If you do not supply a value for the mode parameter, the default is brief.

Permissions

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

Granular permissions enabled	With granular permissions enabled, you must be a user with manage abstract plans privilege. Any user can execute sp_help_qplan for their own abstract plan.
Granular permissions disabled	With granular permissions disabled, you must be the database owner or a user with sa_role. Any user can execute sp_help_qplan for their own abstract plan.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect