When SQL Server recompiles stored procedures, only the statement that caused the recompilation is compiled, instead of the complete procedure. SQL Server features statement-level recompilation of procedures. If parameter values on the procedure are frequently atypical, forcing a recompile of the procedure and a new plan based on different parameter values can improve performance.
If these values represent the typical ones with which the procedure is subsequently called, then the procedure benefits from the query plan every time that it compiles and executes. When SQL Server executes procedures, any parameter values that are used by the procedure when it compiles are included as part of generating the query plan. It also occurs if an underlying table referenced by the procedure has undergone physical design changes.Īnother reason to force a procedure to recompile is to counteract the "parameter sniffing" behavior of procedure compilation. Automatic recompiling occurs whenever SQL Server is restarted. There are times when procedure recompilation must be forced and other times when it occurs automatically. This can improve the procedure's processing performance. If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure's query plan for those changes. When a procedure is compiled for the first time or recompiled, the procedure's query plan is optimized for the current state of the database and its objects. There are three ways to do this: WITH RECOMPILE option in the procedure definition or when the procedure is called, the RECOMPILE query hint on individual statements, or by using the sp_recompile system stored procedure.
#Recompile a stored procedure how to#
This article describes how to recompile a stored procedure in SQL Server by using Transact-SQL. Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW)
0 kommentar(er)
