Recompile a Stored Procedure

  • Article
  • 4 minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics yesAnalytics Platform Organization (PDW)

This topic describes how to recompile a stored procedure in SQL Server by using Transact-SQL. There are three ways to do this: WITH RECOMPILE selection in the procedure definition or when the process is called, the RECOMPILE query hint on individual statements, or past using the sp_recompile system stored process. This topic describes using the WITH RECOMPILE option when creating a process definition and executing an existing procedure. It also describes using the sp_recompile system stored procedure to recompile an existing procedure.

In This Topic

  • Earlier you begin:

    Recommendations

    Security

  • To recompile a stored procedure, using:

    Transact-SQL

Before You Begin

Recommendations

  • When a process is compiled for the first time or recompiled, the procedure's query programme is optimized for the electric current land of the database and its objects. If a database undergoes significant changes to its data or construction, recompiling a procedure updates and optimizes the procedure'southward query programme for those changes. This can better the procedure's processing functioning.

  • There are times when procedure recompilation must exist forced and other times when it occurs automatically. Automatic recompiling occurs whenever SQL Server is restarted. It likewise occurs if an underlying table referenced by the procedure has undergone physical design changes.

  • Another reason to strength a procedure to recompile is to annul the "parameter sniffing" behavior of procedure compilation. When SQL Server executes procedures, any parameter values that are used by the process when it compiles are included equally function of generating the query program. If these values represent the typical ones with which the procedure is subsequently called, and then the procedure benefits from the query programme every time that it compiles and executes. 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 functioning.

  • SQL Server features statement-level recompilation of procedures. When SQL Server recompiles stored procedures, simply the argument that caused the recompilation is compiled, instead of the complete procedure.

  • If certain queries in a procedure regularly use atypical or temporary values, procedure operation tin can exist improved by using the RECOMPILE query hint inside those queries. 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. Merely in addition to using the procedure'south current parameter values, the RECOMPILE query hint also uses the values of any local variables inside the stored procedure when you compile the argument. For more information, run into Query Hint (Transact-SQL).

Security

Permissions

WITH RECOMPILE Option
If this option is used when the process definition is created, it requires CREATE PROCEDURE permission in the database and Modify permission on the schema in which the procedure is being created.

If this option is used in an EXECUTE statement, it requires EXECUTE permissions on the procedure. Permissions are not required on the EXECUTE statement itself simply execute permissions are required on the procedure referenced in the EXECUTE statement. For more information, see EXECUTE (Transact-SQL).

RECOMPILE Query Hint
This characteristic is used when the procedure is created and the hint is included in Transact-SQL statements in the process. Therefore, it requires CREATE PROCEDURE permission in the database and Change permission on the schema in which the procedure is being created.

sp_recompile System Stored Procedure
Requires Modify permission on the specified procedure.

Using Transact-SQL

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following instance into the query window and click Execute. This case creates the process definition.

                      Apply AdventureWorks2012;   Go   IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT Nix        DROP Process dbo.uspProductByVendor;   GO   CREATE PROCEDURE dbo.uspProductByVendor @Proper noun varchar(xxx) = '%'   WITH RECOMPILE   Every bit       SET NOCOUNT ON;       SELECT v.Proper noun AS 'Vendor name', p.Name As 'Product name'       FROM Purchasing.Vendor AS v        JOIN Purchasing.ProductVendor AS pv          ON v.BusinessEntityID = pv.BusinessEntityID        Bring together Production.Product AS p          ON pv.ProductID = p.ProductID       WHERE v.Name LIKE @Name;

To recompile a stored procedure by using the WITH RECOMPILE option

Select New Query, then copy and paste the following code case into the query window and click Execute. This executes the procedure and recompiles the process'due south query plan.

              Use AdventureWorks2012;   GO   EXECUTE HumanResources.uspProductByVendor WITH RECOMPILE;   Go

To recompile a stored procedure by using sp_recompile

Select New Query, then copy and paste the following instance into the query window and click Execute. This does not execute the procedure but it does mark the procedure to be recompiled and so that its query plan is updated the next time that the process is executed.

              Employ AdventureWorks2012;   Get   EXEC sp_recompile N'dbo.uspProductByVendor';    Go

See Besides

Create a Stored Procedure
Modify a Stored Procedure
Rename a Stored Procedure
View the Definition of a Stored Procedure
View the Dependencies of a Stored Procedure
Driblet PROCEDURE (Transact-SQL)