sp_cmp_qplans

Description

Compares two abstract plans.

Syntax

sp_cmp_qplans id1, id2

Parameters

id1, id2: are the IDs of two abstract plans.

Examples

Example 1

Compares abstract plan 411252620 to 1383780087:

sp_cmp_qplans 411252620, 1383780087

The queries are the same.
The query plans are the same.

Example 2

Compares abstract plan 2091258605 to 647777465:

sp_cmp_qplans 2091258605, 647777465

The queries are the same.
The query plans are different.

Usage

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:

Table 1-12: 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.

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.

Permissions

The permission checks for sp_cmp_qplans differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must be a user with manage abstract plans privilege or monitor qp performance privilege.

Any user can compare plans that they own.

Granular permissions disabled

With granular permissions disabled, you must be the database owner or a user with sa_role.