KEEPFIXED PLAN Query Hint
-
Posted on November 5, 2010 by Derek Dieter
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%.
Continue reading ...