Finding abstract plans

sp_find_qplan searches both the query text and the plan text to find plans that match a given pattern.

This example finds all plans where the query includes the string “from titles”:

sp_find_qplan "%from titles%"

This example searches for all abstract plans that perform a table scan:

sp_find_qplan "%t_scan%"

When a System Administrator or Database Owner executes sp_find_qplan, the procedure examines and reports on plans owned by all users. When other users execute the procedure, it searches and reports on only plans that they own.

If you want to search just one abstract plan group, specify the group name with sp_find_qplan. This example searches only the test_plans group, finding all plans that use a particular index:

sp_find_qplan "%i_scan title_id_ix%", test_plans

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