sp_cmp_all_qplans

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 two abstract plan groups.
mode – is the display option. The modes and what information they report are:
- counts – the default mode, this option reports plans that:
  - Are the same
  - Have the same association key, but different groups
  - Exist in one group, but not the other
- 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
  - 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.

Examples

Example 1 – Generates a default report on two 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

There are additional considerations when using sp_cmp_all_qplans:

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
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

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage abstract plans` privilege or a user with `monitor qp performance` privilege. Any user can compare plans that they own.
Disabled	With granular permissions disabled, you must be the database owner or a user with sa_role. Any user can compare plans that they own.

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