sp_cmp_qplans

Compares two abstract plans.

Syntax

sp_cmp_qplans id1, id2

Parameters

Examples

Usage

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.

Permissions

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

SettingDescription
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.

Auditing

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

InformationValues
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

Related reference
sp_cmp_all_qplans
sp_help_qpgroup