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

 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

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

See also

System procedures sp_find_qplan, sp_help_qpgroup