Given two plan IDs, sp_cmp_qplans compares two abstract plans and the associated queries. For example:
sp_cmp_qplans 588529130, 1932533918
sp_cmp_qplans prints one message reporting the comparison of the query, and a second message about the plan, as follows:
For the two queries, one of:
The queries are the same.
The queries are different.
The queries are different but have the same hash key.
For the plans:
The query plans are the same.
The query plans are different.
This example compares two plans where the queries and plans both match:
sp_cmp_qplans 411252620, 1383780087
The queries are the same. The query plans are the same.
This example compares two plans where the queries match, but the plans are different:
sp_cmp_qplans 2091258605, 647777465
The queries are the same. The query plans are different.
sp_cmp_qplans returns a status value showing the results of the comparison. The status values are shown in Table 10-1
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. |
A System Administrator or Database Owner can compare any two abstract plans in the database. Other users can compare only plans that they own.
