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

Generate 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

Permissions

Any user can execute sp_cmp_all_qplans.

See also

System procedures sp_cmp_qplans