sp_help_qpgroup

Syntax

sp_help_qpgroup [ group [, mode ]]

Parameters

group – is the name of an abstract plan group.
mode – is the type of report to print, and is one of the following:
- full – returns 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 – returns all of the information from the “full” report, except hash key information.
- hash – returns the number of rows and number of abstract plans in the group, the number of hash keys, and hash-key collision information.
- list – returns 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 – returns 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 – returns 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 – returns 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.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage abstract plans` privilege.
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:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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