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

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.

Granular permissions 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.

Granular permissions disabled

With granular permissions disabled, you must be the database owner or a user with sa_role.