Select the product you need help with

HOW TO: Use KEEPFIXED PLAN to Disable Stored Procedure Recompilations

Article ID: 276220 - View products that this article applies to.

This article was previously published under Q276220

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

(http://support.microsoft.com/kb/243586/EN-US/ )

INF: Troubleshooting Stored Procedure Recompilation

Sample Code that Uses KEEPFIXED PLAN Hints

The sample code in this section is from the following Microsoft Knowledge Base article:

243586

(http://support.microsoft.com/kb/243586/EN-US/ )

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.