Chapter 1: System Procedures

sp_cmp_all_qplans

Description

Compares all abstract plans in two abstract plan groups.

Syntax

sp_cmp_all_qplans group1, group2 [, mode]

Parameters

group1, group2: are the names of the 2 abstract plan groups.

mode: is the display option, one of: counts, brief, same, diff, first, second, offending and full. The default mode is counts.

Examples

Example 1

Generates a default report on 2 abstract plan groups:

sp_cmp_all_qplans dev_plans, prod_plans

If the two query plans groups are large, this might take some time.
Query plans that are the same
 count
 ----------- 
          49 
Different query plans that have the same association key
 count
 ----------- 
           1 
Query plans present only in group ’dev_plans’ :
 count
 ----------- 
           1 
Query plans present only in group ’prod_plans’ :
 count
 ----------- 
           0

Example 2

Generates a report using the brief mode:

sp_cmp_all_qplans dev_plans, prod_plans, brief

Usage

Use sp_cmp_all_qplans to check for differences in abstract plans in two groups of plans.

sp_cmp_all_qplans matches pairs of plans where the plans in each group have the same user ID and query text. The plans are classified as follows:

Plans that are the same
Plans that have the same association key in both groups, but have different abstract plans. The association key is the group ID, user ID and query text.
Plans that exist in one group, but do not exist in the other group

Table 1-14 shows the report modes and what type of information is reported for each mode.

**Table 1-14: Report modes for sp_cmp_all_qplans**
Mode	Reported information
counts	The counts of: plans that are the same, plans that have the same association key, but different groups, and plans that exist in one group, but not the other. This is the default report mode.
brief	The information provided by counts, plus the IDs of the abstract plans in each group where the plans are different, but the association key is the same, and the IDs of plans that are in one group, but not in the other.
same	All counts, plus the IDs, queries, and plans for all abstract plans where the queries and plans match.
diff	All counts, plus the IDs, queries, and plans for all abstract plans where the queries and plans are different.
first	All counts, plus the IDs, queries, and plans for all abstract plans that are in the first plan group, but not in the second plan group.
second	All counts, plus the IDs, queries, and plans for all abstract plans that are in the second plan group, but not in the first plan group.
offending	All counts, plus the IDs, queries, and plans for all abstract plans that have different association keys or that do not exist in both groups. This is the combination of the diff, first and second modes
full	All counts, plus the IDs, queries, and plans for all abstract plans. This is the combination of same and offending modes.

To compare two individual abstract plans, use sp_cmp_qplans. To see the names of abstract plan groups, use sp_help_qpgroup.
When a System Administrator or Database Owner runs sp_cmp_all_qplans, it reports on all plans in the two groups. When another user executes sp_cmp_all_qplans, it reports only on plans that have the user’s ID.

Permissions

Any user can execute sp_cmp_all_qplans.

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