- Don’t interleave DDL and DML statements.
- Avoid recompilation caused by statistics changes: use the
KEEPFIXED PLAN option or disable the auto update statistics on the relevant table.
- Use table variables instead of temporary tables (as statistics are not created for table variables)
- Avoid changing SET options within the stored procedure.
- Use the
OPTIMIZE FOR query hint that uses parameter values
supplied by you to compile the plan, regardless of the values of the parameter passed in by the calling application. It is recommended to use it in case you have a set of parameters that are "representative" from statistics point of view. This way you'll have a plan that works best most of the time.
- Using plan guides. A plan guide allows you to use query hint or other optimization techniques without having to modify the query or procedure text. For example when the query is part of a third-party application and you are not able to modify it by including
OPTION (OPTIMIZE FOR).
Example of creating a plan guide: