Stored Procedure Recompilation
As you already know, SQL Server creates an optimized execution plan, which is stored in memory, the first time a stored procedure is executed. In general, you want SQL Server to reuse this execution plan for subsequent executions of stored procedures. However, for diverse reasons, sometimes you might want to force SQL Server to modify an execution plan. The reason might fall among one of these: The value of parameters changed significantly, the objects referenced by the stored procedure changed in some way, the data changed significantly or, last but not less important, indexes changed.
There are three ways to explicitly force SQL Server to generate another execution plan:
Use the WITH RECOMPILE option when creating ...
Get Microsoft® SQL Server™ 2000 Programming by Example now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
