sp_find_qplan

Syntax

sp_find_qplan pattern [, group ]

Parameters

pattern – is a string to find in the text of the query or abstract plan.
group – is the name of the abstract plan group.

Examples

Example 1 – Reports on all abstract plans that have the string “from titles” in the query:

sp_find_qplan "%from titles%"

gid id          text
--- ----------- --------------------------------------------------
2   921054317 select count(*) from titles
2   921054317
         ( plan
        ( i_scan t_pub_id_ix titles )
        ( )
)
( prop titles
        ( parallel 1 )
        ( prefetch 16 )
        ( lru )
)
5   937054374 select type, avg(price) from titles group by type
5   937054374
         ( plan
        ( store Worktab1
                ( i_scan type_price titles )
        )
        ( t_scan ( work_t Worktab1 ) )
)
( prop titles
        ( parallel 1 )
        ( prefetch 16 )
        ( lru )

Example 2 – Finds all plans that include a table scan operator:
```
sp_find_qplan "%t_scan%"
```
Example 3 – Uses the range pattern matching to look for strings such as “table1”, “table2”, and so forth, in plans in the dev_plans group:
```
sp_find_qplan "%table[0-9]%", dev_plans
```

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.

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 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:

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