Comparing two abstract plans

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:

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 32-1

Table 32-1: Return status values for sp_cmp_qplans

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.