sp_cmp_qplans

Syntax

sp_cmp_qplans id1, id2

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.

There are additional considerations when using sp_cmp_qplans:

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 and their meanings are:
- 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.

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

Setting	Description
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.
Disabled	With granular permissions disabled, you must be the database owner or a user with sa_role. Any user can compare plans that they own.

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect