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:
The total number of abstract plans, and the total number of rows in the sysqueryplans table.
The number of plans that have multiple rows in sysqueryplans. They are listed in descending order, starting with the plans with the largest number of rows.
Information about the number of hash keys and hash-key collisions. Abstract plans are associated with queries by a hashing algorithm over the entire query.
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 =...
