Comparing all plans in a group

sp_cmp_all_qplans compares all abstract plans in two groups and reports:

The number of plans that are the same in both groups
The number of plans that have the same association key, but different abstract plans
The number of plans that are present in one group, but not the other

This example compares the plans in ap_stdout and ap_stdin:

sp_cmp_all_qplans ap_stdout, ap_stdin

If the two query plans groups are large, this might take some time.
Query plans that are the same
 count
 -----------
         338
Different query plans that have the same association key

 count
 -----------
          25
Query plans present only in group ’ap_stdout’ :

 count
 -----------
           0
Query plans present only in group ’ap_stdin’ :

 count
 -----------
           1

With the additional specification of a report-mode parameter, sp_cmp_all_qplans provides detailed information, including the IDs, queries, and abstract plans of the queries in the groups. The mode parameter lets you get the detailed information for all plans, or just those with specific types of differences.Table 17-2 shows the report modes and what type of information is reported for each mode.

**Table 17-2: 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.

This example shows the brief report mode:

sp_cmp_all_qplans ptest1, ptest2, brief

If the two query plans groups are large, this might take some time.
Query plans that are the same
 count       
 ----------- 
          39 
Different query plans that have the same association key

 count       
 ----------- 
           4 
 
    ptest1    ptest2

 id1         id2         
 ----------- ----------- 
   764529757  1580532664 
   780529814  1596532721 
   796529871  1612532778 
   908530270  1724533177 
Query plans present only in group ’ptest1’ :

 count       
 ----------- 
           3 
 

 id          
 ----------- 
   524528902 
  1292531638 
  1308531695 
 
Query plans present only in group ’ptest2’ :

 count       
 ----------- 
           1 
 

 id          
 ----------- 
  2108534545