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).
Sample Code that Uses KEEPFIXED PLAN Hints
The sample code in this section is from the following Microsoft Knowledge Base
article:
KBLink:243586.KB.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:
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.
The information in this article applies to:
- Microsoft SQL Server 7.0 (Version: 7.0)
- Microsoft SQL Server 2000 (all editions)
© 2003 Microsoft