This question was
closed
by
nidheesh.r.pillai for the following reason: The question is answered, right answer was accepted
asked
Side-effects of OPTION(KEEPFIXED PLAN)
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.
Another thing: OPTION(KEEP PLAN) will reduce the number of recompiles on statistics changes to temporary tables. I read somewhere that changing six rows in a temporary table causes a statistics update and therefore possibly a recompile, whereas on a normal table, there will have to be more changes before statistics is auto-updated. Using OPTION(KEEP PLAN) will tell SQL Server to treat temporary tables the same way as normal tables. OPTION(KEEPFIXED PLAN) will tell SQL Server to totally disregard statistics changes and keep the same plan whatever happens to data distribution. Schema changes will still cause a recompile, regardless of options used.
All excellent stuff. One minor nit. My understanding of auto stats updates are that, with the defaults in place, we're talking 1 row where there are zero, 500 rows where there are less than 500 and then 500 + 20% of rows when greater than 500. Traceflag 2371 changes that 20% value to a sliding scale. I think that 6 row thing is older and not applicable any more. At least I can't find it in documentation newer than 2005.
Thanks for the detailed response @Magnus Ahlkvist. I too did some searching over the web to find more useful information and according to this link (
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/907a6118-550f-4239-ab6c-d1ff4c787c45/plan-stability-keep-plan-vs-keepfixed-plan?forum=transactsql), you are right about the 6 row threshold for the temporary tables to initiate a recompile for statistics update. In such cases, as you rightly mentioned, it is indeed advisable to use the OPTION (KEEP PLAN) for temporary tables. This I can try instead of the existing KEEPFIXED PLAN as there are many temporary tables in the SP. Regarding your advice of questioning of its usage for each and every query, I don't know how I will go about it considering an estimate of approximately 500+ such individual queries containing the statement inside SP's present across the server. Any tips on how I should start on this would be of great help.
Another help - the link that I referred above mentions that "SET options can cause recompiles, so using KEEPFIXED PLAN may not solve the problem if SET options are causing recompiles". Can you shed some light on what that means? Are they in referrence to variable assignments like SET @variable? - coz they like the temporary tables can be found strewn around plenty inside the SP's in question.
Hi, I think they with SET options means for example SET TRANSACTION_ISOLATION_LEVEL statements. Regarding questioning each and every occurance, I would look over the code, and as a default decision remove the option, but keep it whereever it is indeed needed. From the top of my head, I can't think of a scenario where I would keep it though :)
15 People are following this question.
