Compares two abstract plans.
sp_cmp_qplans id1, id2
are the IDs of two abstract plans.
Compares abstract plan 411252620 to 1383780087:
sp_cmp_qplans 411252620, 1383780087
The queries are the same. The query plans are the same.
Compares abstract plan 2091258605 to 647777465:
sp_cmp_qplans 2091258605, 647777465
The queries are the same. The query plans are different.
sp_cmp_qplans compares the queries, abstract plans, and hash keys of two abstract plans, and reports whether the queries are the same, and whether the plans are the same. It prints one of these messages for the query:
The queries are the same.
The queries are different.
The queries are different but have the same hash key.
It prints one of these messages for the abstract plan:
The query plans are the same.
The query plans are different.
sp_cmp_qplans also prints a return status showing the results of the comparison. The status values 1, 2 and 10 are additive. The status values are show in Table 1-12:
Return value |
Meaning |
|---|---|
0 |
The query text and abstract plans are the same. |
+1 |
The queries and hash keys are different. |
+2 |
The queries are different, but the hash keys are the same. |
+10 |
The abstract plans are different. |
100 |
One or both of the plan IDs does not exist. |
To find the ID of a plan, use sp_help_qpgroup or sp_find_qplan. Plan IDs are also returned by create plan and are included in showplan output.
Any user can execute sp_cmp_qplans to compare plans that he or she owns. Only a system administrator or the database owner can compare plans owned by another user.
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
System procedures sp_cmp_all_qplans, sp_help_qpgroup
