sp_help_qpgroup

Description

Reports information on an abstract plan group.

Syntax

sp_help_qpgroup [ group [, mode ]]

Parameters

group: is the name of an abstract plan group.

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

Mode

Information returned

full

The number of rows and number of plans in the group, the number of plans that use two or more rows, the number of rows and plan IDs for the longest plans, and number of hash keys and hash key collision information. This is the default report mode.

stats

All of the information from the “full” report, except hash key information.

hash

The number of rows and number of abstract plans in the group, the number of hash keys, and hash-key collision information.

list

The number of rows and number of abstract plans in the group, and the following information for each query/plan pair: hash key, plan ID, first few characters of the query, and the first few characters of the plan.

queries

The number of rows and number of abstract plans in the group, and the following information for each query: hash key, plan ID, first few characters of the query.

plans

The number of rows and number of abstract plans in the group, and the following information for each plan: hash key, plan ID, first few characters of the plan.

counts

The number of rows and number of abstract plans in the group, and the following information for each plan: number of rows, number of characters, hash key, plan ID, first few characters of the query.

Mode	Information returned
full	The number of rows and number of plans in the group, the number of plans that use two or more rows, the number of rows and plan IDs for the longest plans, and number of hash keys and hash key collision information. This is the default report mode.
stats	All of the information from the “full” report, except hash key information.
hash	The number of rows and number of abstract plans in the group, the number of hash keys, and hash-key collision information.
list	The number of rows and number of abstract plans in the group, and the following information for each query/plan pair: hash key, plan ID, first few characters of the query, and the first few characters of the plan.
queries	The number of rows and number of abstract plans in the group, and the following information for each query: hash key, plan ID, first few characters of the query.
plans	The number of rows and number of abstract plans in the group, and the following information for each plan: hash key, plan ID, first few characters of the plan.
counts	The number of rows and number of abstract plans in the group, and the following information for each plan: number of rows, number of characters, hash key, plan ID, first few characters of the query.

Examples

Example 1

Reports summary information about all abstract plan groups in the database:

sp_help_qpgroup

Group                  GID         Plans 
----------------------- ----------- ----------- 
ap_stdin                         1           0 
ap_stdout                        2           0 
dev_test                         3         209

Example 2

Reports on the test_plans group:

sp_help_qpgroup test_plans

Query plans group 'test_plans', GID 8
 
 Total Rows  Total QueryPlans 
 ----------- ---------------- 
           6                3 
sysqueryplans rows consumption, number of query plans per row count

 Rows        Plans
 ----------- ----------- 
           2           3 

 Hashkeys    
 ----------- 
           3 
There is no hash key collision in this group.

Usage

When used with an abstract plan group name, and no mode parameter, the default mode for sp_help_qpgroup is full.
Hash-key collisions indicate that more than one plan for a particular user has the same hash-key value. When there are hash key collisions, the query text of each query with the matching hash key must be compared to the user’s query text in order to identify the matching query, so performance is slightly degraded.

Permissions

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

Granular permissions enabled	With granular permissions enabled, you must be a user with manage abstract plans privilege.
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_qpgroup for their own abstract plan group.

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