Chapter 1: System Procedures

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.

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

 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

Any user can execute sp_help_qplan to see the abstract plan of a query that he or she owns. Only the System Administrator and the Database Owner can display an abstract plan owned by another user.

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