KEEPFIXED PLAN Query Hint

The KEEPFIXED PLAN query hint is used in order to retain the original execution plan used to initially compile a statement. It is typically used to disallow a recompile, especially in regards to a statement that utilizes temp table or a table that changes it’s percentage of records frequently resulting in triggering a recompile for a statement.

To use, specify OPTION (KEEPFIXED PLAN) immediately after the statement that you do not want to recompile.

[cc lang=”sql”]
SELECT *
FROM #Temp
OPTION (KEEPFIXED PLAN)
[/cc]

Temp tables frequently recompile due to the change in statistics. When using temp tables within stored procedures, this can be a disadvantage. To get around the recompile, either use table variables (indexed with constraints) or use the KEEPFIXED PLAN query hint.

Recompiles typically happen when the percentage of a tables (or temp tables) rows change by 500 and the cardinality (or uniqueness) changes by 20%.