Getting information about a group

sp_help_qpgroup prints information about an abstract plan group, or about all abstract plan groups in a database.

When you use sp_help_qpgroup without a group name, it prints the names of all abstract plan groups, the group IDs, and the number of plans in each group:

sp_help_qpgroup
Query plan groups in database ‘pubtune’
 Group                          GID         Plans       
 ------------------------------ ----------- ----------- 
 ap_stdin                                 1           0 
 ap_stdout                                2           2 
 p_prod                                   4           0 
 priv_test                                8           1 
 ptest                                    3          51 
 ptest2                                   7         189

When you use sp_help_qpgroup with a group name, the report provides statistics about plans in the specified group. This example reports on the group ptest2:

sp_help_qpgroup ptest2
Query plans group ’ptest2’, GID 7
 
 Total Rows  Total QueryPlans 
 ----------- ---------------- 
         452              189 
sysqueryplans rows consumption, number of query plans per row count
 Rows        Plans       
 ----------- ----------- 
           5           2 
           3          68 
           2         119 
Query plans that use the most sysqueryplans rows
 Rows        Plan        
 ----------- ----------- 
           5  1932533918 
           5  1964534032 
 Hashkeys    
 ----------- 
         123 
There is no hash key collision in this group.

When reporting on an individual group, sp_help_qpgroup reports:

When a System Administrator or the Database Owner executes sp_help_qpgroup, the procedure reports on all of the plans in the database or in the specified group. When any other user executes sp_help_qpgroup, it reports only on plans that he or she owns.

sp_help_qpgroup provides several report modes. The report modes are:

Mode

Information returned

full

The number of rows and number of plans in the group, the number of plans that use two or more rows, the number of rows and plan IDs for the longest plans, and number of hash keys, and has- key collision information. This is the default report mode.

stats

All of the information from the full report, except hash-key information.

hash

The number of rows and number of abstract plans in the group, the number of hash keys, and hash-key collision information.

list

The number of rows and number of abstract plans in the group, and the following information for each query/plan pair: hash key, plan ID, first few characters of the query, and the first few characters of the plan.

queries

The number of rows and number of abstract plans in the group, and the following information for each query: hash key, plan ID, first few characters of the query.

plans

The number of rows and number of abstract plans in the group, and the following information for each plan: hash key, plan ID, first few characters of the plan.

counts

The number of rows and number of abstract plans in the group, and the following information for each plan: number of rows, number of characters, hash key, plan ID, first few characters of the query.

This example shows the output for the counts mode:

sp_help_qpgroup ptest1, counts
Query plans group ’ptest1’, GID 3

 Total Rows  Total QueryPlans 
 ----------- ---------------- 
          48               19 
 
Query plans in this group

Rows  Chars     hashkey     id          query                                 
----- --------- ----------- ----------- ---------------------------- 
    3      623  1801454852   876530156 select title from titles ... 
    3      576   476063777   700529529 select au_lname, au_fname... 
    3      513   444226348   652529358 select au1.au_lname, au1.... 
    3      470   792078608   716529586 select au_lname, au_fname... 
    3      430   789259291   684529472 select au1.au_lname, au1.... 
    3      425  1929666826   668529415 select au_lname, au_fname... 
    3      421   169283426   860530099 select title from titles ... 
    3      382   571605257   524528902 select pub_name from publ... 
    3      355   845230887   764529757 delete salesdetail where ... 
    3      347   846937663   796529871 delete salesdetail where ... 
    2      379  1400470361   732529643 update titles set price =...