Recompile a Stored Procedure

  • Article
  • four 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 past using Transact-SQL. There are three ways to do this: WITH RECOMPILE selection in the process definition or when the process is called, the RECOMPILE query hint on individual statements, or by using the sp_recompile system stored process. This topic describes using the WITH RECOMPILE option when creating a procedure definition and executing an existing procedure. Information technology likewise describes using the sp_recompile system stored process to recompile an existing process.

In This Topic

  • Earlier you begin:

    Recommendations

    Security

  • To recompile a stored procedure, using:

    Transact-SQL

Before Yous Brainstorm

Recommendations

  • When a procedure is compiled for the get-go time or recompiled, the process's query plan is optimized for the electric current state of the database and its objects. If a database undergoes significant changes to its information or structure, recompiling a process updates and optimizes the procedure'south query plan for those changes. This can amend the procedure'southward processing functioning.

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

  • Another reason to force 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 procedure when it compiles are included as part of generating the query plan. 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. If parameter values on the process are frequently singular, 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, only the argument that caused the recompilation is compiled, instead of the consummate procedure.

  • If certain queries in a procedure regularly utilize singular or temporary values, procedure functioning can be improved by using the RECOMPILE query hint inside those queries. Since only the queries using the query hint will exist recompiled instead of the consummate procedure, SQL Server's argument-level recompilation behavior is mimicked. Merely 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. For more data, see Query Hint (Transact-SQL).

Security

Permissions

WITH RECOMPILE Option
If this option is used when the procedure definition is created, information technology requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is existence created.

If this option is used in an EXECUTE statement, it requires EXECUTE permissions on the process. Permissions are non required on the EXECUTE argument itself simply execute permissions are required on the process referenced in the EXECUTE statement. For more than information, run across 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 procedure. Therefore, information technology requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

sp_recompile System Stored Procedure
Requires Change 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 example into the query window and click Execute. This instance creates the procedure definition.

                      USE AdventureWorks2012;   GO   IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL        Driblet PROCEDURE dbo.uspProductByVendor;   Get   CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'   WITH RECOMPILE   AS       SET NOCOUNT ON;       SELECT v.Name Equally 'Vendor name', p.Proper noun Equally 'Product proper name'       FROM Purchasing.Vendor AS five        JOIN Purchasing.ProductVendor Equally pv          ON v.BusinessEntityID = pv.BusinessEntityID        Join Production.Product Every bit p          ON pv.ProductID = p.ProductID       WHERE v.Name LIKE @Name;

To recompile a stored procedure by using the WITH RECOMPILE choice

Select New Query, and so re-create and paste the following code example into the query window and click Execute. This executes the procedure and recompiles the procedure's query plan.

              Utilise AdventureWorks2012;   GO   EXECUTE HumanResources.uspProductByVendor WITH RECOMPILE;   GO

To recompile a stored process past using sp_recompile

Select New Query, then re-create and paste the following example into the query window and click Execute. This does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the adjacent time that the procedure is executed.

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

Run into As well

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