重新编译存储过程Recompile a Stored Procedure

适用对象:yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

本主题介绍如何在 SQL Server 2017SQL Server 2017 中使用 Transact-SQLTransact-SQL重新编译存储过程。This topic describes how to recompile a stored procedure in SQL Server 2017SQL Server 2017 by using Transact-SQLTransact-SQL. 完成此项工作有三种方法:过程定义中的 WITH RECOMPILE 选项,或在调用过程时,各个语句上的 RECOMPILE 查询提示,或者使用 sp_recompile 系统存储过程。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. 本主题介绍在创建过程定义或执行现有过程时使用 WITH RECOMPILE 选项。This topic describes using the WITH RECOMPILE option when creating a procedure definition and executing an existing procedure. 它还描述如何使用 sp_recompile 系统存储过程重新编译现有过程。It also describes using the sp_recompile system stored procedure to recompile an existing procedure.

本主题内容In This Topic

开始之前Before You Begin

建议Recommendations

  • 在首次编译或重新编译过程时,该过程的查询计划针对该数据库及其对象的当前状态进行优化。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. 如果数据库对其数据或结构进行了重要更改,则重新编译过程会进行更新并针对这些更改优化过程的查询计划。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. 这样可以提高过程的处理性能。This can improve the procedure's processing performance.

  • 有时必须强制执行过程重新编译,而其他时间将自动执行。There are times when procedure recompilation must be forced and other times when it occurs automatically. 只要重新启动 SQL ServerSQL Server ,就会发生自动重新编译操作。Automatic recompiling occurs whenever SQL ServerSQL Server is restarted. 当该过程引用的基础表发生物理设计更改时,也会执行此操作。It also occurs if an underlying table referenced by the procedure has undergone physical design changes.

  • 强制过程重新编译的另一个原因是抵消过程编译的“参数查找”行为。Another reason to force a procedure to recompile is to counteract the "parameter sniffing" behavior of procedure compilation. SQL ServerSQL Server 执行过程时,该过程在编译时使用的任何参数值都作为生成查询计划的一部分包括在内。When SQL ServerSQL 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 procedure are frequently atypical, forcing a recompile of the procedure and a new plan based on different parameter values can improve performance.

  • SQL ServerSQL Server 具有对过程执行语句级重新编译的特点。features statement-level recompilation of procedures. SQL ServerSQL Server 重新编译存储过程时,只编译导致重新编译的语句,而不编译整个过程。When SQL ServerSQL Server recompiles stored procedures, only the statement that caused the recompilation is compiled, instead of the complete procedure.

  • 如果过程的中某些查询定期使用非典型值或临时值,则可通过使用这些查询中的 RECOMPILE 查询提示来改善过程性能。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. 由于仅使用此查询提示的查询将进行重新编译,而不是整个过程进行重新编译,因此将模仿 SQL ServerSQL Server语句级重新编译行为。Since only the queries using the query hint will be recompiled instead of the complete procedure, SQL ServerSQL Server's statement-level recompilation behavior is mimicked. 但除了使用过程的当前参数值外,RECOMPILE 查询提示还在编译该语句时使用存储过程中本地变量的值。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. 有关详细信息,请参阅 查询提示 (Transact-SQL)For more information, see Query Hint (Transact-SQL).

安全性Security

PermissionsPermissions

WITH RECOMPILE 选项WITH RECOMPILE Option
如果在创建过程定义时使用此选项,则要求数据库中的 CREATE PROCEDURE 权限,还必须具有对架构(在其下创建过程)的 ALTER 权限。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.

如果在 EXECUTE 语句中使用此选项,则需要对该过程的 EXECUTE 权限。If this option is used in an EXECUTE statement, it requires EXECUTE permissions on the procedure. 需要对 EXECUTE 语句本身的权限,而无需对 EXECUTE 语句中引用的过程的执行权限。Permissions are not required on the EXECUTE statement itself but execute permissions are required on the procedure referenced in the EXECUTE statement. 有关详细信息,请参阅 EXECUTE (Transact-SQL)For more information, see EXECUTE (Transact-SQL).

RECOMPILE 查询提示RECOMPILE Query Hint
创建过程时使用该功能,并且此提示包含在该过程中的 Transact-SQLTransact-SQL 语句中。This feature is used when the procedure is created and the hint is included in Transact-SQLTransact-SQL statements in the procedure. 因此,它需要在数据库中有 CREATE PROCEDURE 权限,对在其中创建过程的架构有 ALTER 权限。Therefore, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

sp_recompile 系统存储过程sp_recompile System Stored Procedure
需要具有对指定过程的 ALTER 权限。Requires ALTER permission on the specified procedure.

使用 Transact-SQLUsing Transact-SQL

使用 WITH RECOMPILE 选项重新编译存储过程To recompile a stored procedure by using the WITH RECOMPILE option

  1. 连接到数据库引擎Database EngineConnect 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 example creates the procedure definition.

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

使用 WITH RECOMPILE 选项重新编译存储过程To recompile a stored procedure by using the WITH RECOMPILE option

  1. 连接到数据库引擎Database EngineConnect 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 example creates a simple procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view.

    然后,将第二个代码示例复制并粘贴到查询窗口中,然后单击 “执行”And then copy and paste the second code example into the query window and click Execute. 此操作将执行该过程,并重新编译过程的查询计划。This executes the procedure and recompiles the procedure's query plan.

USE AdventureWorks2012;  
GO  
EXECUTE HumanResources.uspGetAllEmployees WITH RECOMPILE;  
GO  
  

使用 sp_recompile 重新编译存储过程To recompile a stored procedure by using sp_recompile

  1. 连接到数据库引擎Database EngineConnect 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 example creates a simple procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view.

    然后,将以下示例复制并粘贴到查询窗口中,然后单击 “执行”Then, copy 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 next time that the procedure is executed.

USE AdventureWorks2012;  
GO  
EXEC sp_recompile N'HumanResources.uspGetAllEmployees';  
GO  
  

另请参阅See Also

创建存储过程 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
DROP PROCEDURE (Transact-SQL)DROP PROCEDURE (Transact-SQL)