Here are some additional tips for writing and using abstract plans:
Look at the current plan for the query and at plans that use the same query execution steps as the plan you need to write. It is often easier to modify an existing plan than to write a full plan from scratch.
Capture the plan for the query.
Use sp_help_qplan to display the SQL text and plan.
Edit this output to generate a create plan command, or attach an edited plan to the SQL query using the plan clause.
It is often best to specify partial plans for query tuning in cases where most optimizer decisions are appropriate, but only an index choice, for example, needs improvement.
By using partial plans, the optimizer can choose other paths for other tables as the data in other tables changes.
Once saved, abstract plans are static. Data volumes and distributions may change so that saved abstract plans are no longer optimal.
Subsequent tuning changes made by adding indexes, partitioning a table, or adding buffer pools may mean that some saved plans are not performing as well as possible under current conditions. Most of the time, you want to operate with a small number of abstract plans that solve specific problems.
Perform periodic plan checks to verify that the saved plans are still better than the plan that the optimizer would choose.
