sp_find_qplan

Finds an abstract plan, given a pattern from the query text or plan text.

Syntax

sp_find_qplan pattern [, group ]

Parameters

Examples

Usage

There are additional considerations when using sp_find_qplan:
  • Use sp_find_qplan to find an abstract plan that contains a particular string. You can match strings from either the query text or from the abstract plan text.

  • For each matching plan, sp_find_qplan prints the group ID, plan ID, query text and abstract plan text.

  • If you include a group name, sp_find_qplan searches for the string in the specified group. If you do not provide a group name, sp_find_plan searches all queries and plans for all groups.

  • You must supply the “%” wildcard characters, as shown in the examples, unless you are searching for a string at the start or end of a query or plan. You can use any Transact-SQL pattern matching syntax, such as that shown in Example 3.

  • The text of queries in sysqueryplans is broken into 255-byte column values. sp_find_qplan may miss matches that span one of these boundaries, but finds all matches that are less than 127 bytes, even if they span two rows.

Permissions

The permission checks for sp_find_qplan 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 execute sp_find_qplan to find and display report plans that they own.

Disabled

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

Any user can execute sp_find_qplan to find and display report 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_help_qpgroup
sp_help_qplan