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%.

Continue reading ...

MAXDOP Query Hint

The MAXDOP query hint is used to implicitly specify the maximum degree of parallelism to use for a specific statement.  The degree of parallelism is roughly defined as how many separate processors to utilize for a single query.  To set this, use the following convention: [cc lang=”sql”] SELECT * FROM Sales.SalesOrderDetail OPTION (MAXDOP 1) [/cc] This will force only one instance of the SPID to be spawned. To allow more instances of a SPID to be spawned replace the 1 with the maximum number. This needs to be specified after each statement. It is not specific to the statement. As with all query hints, it is recommended that you know internally what you are controlling by specifying this hint. While this specific hint is relatively harmless if you specify “1” it is still recommended you understand it’s effects.

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!

Resources

css.php