USE AdventureWorks2012 ĮXEC sp_recompile N'dbo.Update 5: The documentation fix has been merged! This does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the next time that the procedure is executed. Select New Query, then copy and paste the following example into the query window and click Execute. To recompile a stored procedure by using sp_recompile USE AdventureWorks2012 ĮXECUTE pProductByVendor WITH RECOMPILE This executes the procedure and recompiles the procedure's query plan.
Option recompile sql server code#
Select New Query, then copy and paste the following code example into the query window and click Execute. WHERE v.Name LIKE recompile a stored procedure by using the WITH RECOMPILE option ON v.BusinessEntityID = pv.BusinessEntityID SELECT v.Name AS 'Vendor name', p.Name AS 'Product name' IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULLĬREATE PROCEDURE dbo.uspProductByVendor varchar(30) = '%' This example creates the procedure definition. Using Transact-SQLĬopy and paste the following example into the query window and click Execute. Requires ALTER permission on the specified procedure. Therefore, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created. This feature is used when the procedure is created and the hint is included in Transact-SQL statements in the procedure. For more information, see EXECUTE (Transact-SQL). Permissions are not required on the EXECUTE statement itself but execute permissions are required on the procedure referenced in the EXECUTE statement. If this option is used in an EXECUTE statement, it requires EXECUTE permissions on the procedure. If this option is used when the procedure definition is created, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created. For more information, see Query Hint (Transact-SQL). But in addition to using the procedure's current parameter values, the RECOMPILE query hint also uses the values of any local variables inside the stored procedure when you compile the statement. Since only the queries using the query hint will be recompiled instead of the complete procedure, SQL Server's statement-level recompilation behavior is mimicked. If certain queries in a procedure regularly use atypical or temporary values, procedure performance can be improved by using the RECOMPILE query hint inside those queries. 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. It also describes using the sp_recompile system stored procedure to recompile an existing procedure. This topic describes using the WITH RECOMPILE option when creating a procedure definition and executing an existing procedure. 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.
Option recompile sql server how to#
This topic describes how to recompile a stored procedure in SQL Server by using Transact-SQL.
0 kommentar(er)
