HOW TO: Use KEEPFIXED PLAN to Disable Stored Procedure Recompilations

Article translations Article translations
Article ID: 276220 - View products that this article applies to.
This article was previously published under Q276220
Expand all | Collapse all

On This Page

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

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.

Properties

Article ID: 276220 - Last Review: July 19, 2006 - Revision: 5.4
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
Keywords: 
kbhowtomaster kbinfo KB276220

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com