I recently came across a whole lot of crucial stored procedures, wherein every piece of code written into the procedure (be it a DDL or a DML) had the OPTION (KEEPFIXED PLAN) written. I have never worked with this option before so when I enquired around, I got the similar response as Books Online gives - that it is forced so that the overhead of creating an execution plan again and again is reduced when changes/calculations occur. I am baffled a bit here. My belief has been that SQL Server query optimizer usually selects the best execution plan for a query given to it, and yes query options are used as last resort for performance, but then my question is that isn't it overdone when you have EACH & EVERY query inside a stored procedure to be forced with a OPTION (KEEPFIXED PLAN)? Is it okay to implement it or Does this option have any side-effects if overused to such an extent?
It will have side effects. If the code itself causes new plans and recompiles to be created _unnecessarily_ one _might_ consider this option. But to use it everywhere is not a good idea. As your data changes, the optimal plan changes. When working with temporary tables in procecures or scripts, one may want to use this option, given that the contents of the temporary table does not change very much between executions. In SQL Server 2000 and earlier, a recompile caused a whole stored procedure to be recompiled. But from SQL Server 2005, statement level recompile was introduced, thus recompiles aren't effecting performance that much anymore. If this option is used all over the system, the DBA or developer will have to monitor if optimal plans are used (aka if data distribution have changed much since the plan was created). I'm guessing that this is not happening in your system, and therefore my recommendation would be to question each and very use of the option and only keep it when there are _really_ good reasons to do so.