DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 276220 - Last Review: July 19, 2006 - Revision: 5.4

This article was previously published under Q276220

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  (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.

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
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support