sp_find_qplan

Description

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

Syntax

sp_find_qplan pattern [, group ]

Parameters

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

• 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

Any user can execute sp_find_qplan. It reports only on abstract plans owned by the user who executes it, except when executed by a System Administrator or the Database Owner.

Auditing

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

See also