Microsoft KB Archive/276220

= HOW TO: Use KEEPFIXED PLAN to Disable Stored Procedure Recompilations =

Article ID: 276220

Article Last Modified on 7/19/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 64-bit Edition

-



This article was previously published under Q276220



IN THIS TASK

 * SUMMARY
 * Sample Code that Uses KEEPFIXED PLAN Hints
 * REFERENCES



SUMMARY
In some situations where stored procedures are recompiled, the cost of recompilation may outweigh the benefit that is derived from doing so. Note that because SQL Server Service Pack 2 (SP2) and earlier do not support statement level recompilation, the whole stored procedure must be recompiled when a recompile is triggered. Therefore, the number of recompiles that are triggered during the execution of a stored procedure and the length of the stored procedure may sometimes increase the overall duration of the stored procedure execution.

Beginning in SQL Server 7.0 SP3, a new query hint, KEEPFIXED PLAN, has been introduced to help in situations where the cost of recompilation is more than the cost of using the existing plan.

The KEEPFIXED PLAN query hint forces the query optimizer to never recompile a query because of changes in statistics or indexed column changes (for example, update, delete, or insert). For additional information about how to troubleshoot stored procedure recompilation, click the following article number to view the article in the Microsoft Knowledge Base:

243586 INF: Troubleshooting Stored Procedure Recompilation

back to the top

Sample Code that Uses KEEPFIXED PLAN Hints
The sample code in this section is from the following Microsoft Knowledge Base article:

243586 INF: Troubleshooting Stored Procedure Recompilation

drop procedure RowModifications go  create procedure RowModifications as   -- assume SomeTable exists with the same definition as #t, -- and has over 1000 rows create table #t (a int ) select * from #t insert #t select * from retest select count(*) from #t where a = 37 --option (keepfixed plan) go  exec RowModifications go  exec RowModifications go For the second execution of the RowModifications procedure, the following code causes the recompilation: select count(*) from #t where a = 37 If you use this code: Option (keepfixed plan) In this query, the query does not cause the recompilation again: select count(*) from #t where a = 37 option (keepfixed plan) Note This query hint is applied on the statement level and does not affect the scope of the whole stored procedure. If you want this option to affect multiple statements in a stored procedure, each statement must implement the hint that SQL Server is applying to a behavior. This hint is not available for SQL Server 7.0 Service Pack 2 (SP2) or earlier.back to the top

Keywords: kbhowtomaster kbinfo KB276220

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.