重新编译存储过程

项目
04/02/2014

本主题介绍如何在 SQL Server 2012 中使用 Transact-SQL 重新编译存储过程。完成此项工作有三种方法：过程定义中的 WITH RECOMPILE 选项，或在调用过程时，各个语句上的 RECOMPILE 查询提示，或者使用 sp_recompile 系统存储过程。本主题介绍在创建过程定义或执行现有过程时使用 WITH RECOMPILE 选项。它还描述如何使用 sp_recompile 系统存储过程重新编译现有过程。

本主题内容

开始之前：

建议

安全性
若要重新编译存储过程，请使用：

Transact-SQL

开始之前

建议

在首次编译或重新编译过程时，该过程的查询计划针对该数据库及其对象的当前状态进行优化。如果数据库对其数据或结构进行了重要更改，则重新编译过程会进行更新并针对这些更改优化过程的查询计划。这样可以提高过程的处理性能。
有时必须强制执行过程重新编译，而其他时间将自动执行。只要重新启动 SQL Server，就会发生自动重新编译操作。当该过程引用的基础表发生物理设计更改时，也会执行此操作。
强制过程重新编译的另一个原因是抵消过程编译的“参数查找”行为。当 SQL Server 执行过程时，该过程在编译时使用的任何参数值都作为生成查询计划的一部分包括在内。如果这些值表示随后调用此过程时使用的典型值，则该过程在每次编译和执行时都会从查询计划中获益。如果过程的参数值频繁异常，则强制执行过程的重新编译和基于其他参数值的新计划可以改善性能。
SQL Server 具有对过程执行语句级重新编译的特点。当 SQL Server 重新编译存储过程时，只编译导致重新编译的语句，而不编译整个过程。
如果过程的中某些查询定期使用非典型值或临时值，则可通过使用这些查询中的 RECOMPILE 查询提示来改善过程性能。由于仅使用此查询提示的查询将进行重新编译，而不是整个过程进行重新编译，因此将模仿 SQL Server 语句级重新编译行为。但除了使用过程的当前参数值外，RECOMPILE 查询提示还在编译该语句时使用存储过程中本地变量的值。有关详细信息，请参阅查询提示 (Transact-SQL)。

安全性

权限

WITH RECOMPILE 选项
如果在创建过程定义时使用此选项，则要求数据库中的 CREATE PROCEDURE 权限，还必须具有对架构（在其下创建过程）的 ALTER 权限。

如果在 EXECUTE 语句中使用此选项，则需要对该过程的 EXECUTE 权限。需要对 EXECUTE 语句本身的权限，而无需对 EXECUTE 语句中引用的过程的执行权限。有关详细信息，请参阅 EXECUTE (Transact-SQL)。
RECOMPILE 查询提示
创建过程时使用该功能，并且此提示包含在该过程中的 Transact-SQL 语句中。因此，它需要在数据库中有 CREATE PROCEDURE 权限，对在其中创建过程的架构有 ALTER 权限。
sp_recompile 系统存储过程
需要具有对指定过程的 ALTER 权限。

用于“返回首页”链接的箭头图标 [返回页首]

使用 Transact-SQL

使用 WITH RECOMPILE 选项重新编译存储过程

连接到数据库引擎。
在标准菜单栏上，单击**“新建查询”**。
将以下示例复制并粘贴到查询窗口中，然后单击**“执行”**。该示例将创建过程定义。

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;
GO

使用 WITH RECOMPILE 选项重新编译存储过程

连接到数据库引擎。
在标准菜单栏上，单击**“新建查询”**。
将以下示例复制并粘贴到查询窗口中，然后单击**“执行”**。该示例将创建一个简单过程，该存储过程将从视图中返回所有雇员（提供姓和名）、职务以及部门名称。

然后，将第二个代码示例复制并粘贴到查询窗口中，然后单击**“执行”**。此操作将执行该过程，并重新编译过程的查询计划。

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

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

使用 sp_recompile 重新编译存储过程

连接到数据库引擎。
在标准菜单栏上，单击**“新建查询”**。
将以下示例复制并粘贴到查询窗口中，然后单击**“执行”**。该示例将创建一个简单过程，该过程将从视图中返回所有雇员（提供姓和名）、职务以及部门名称。

然后，将以下示例复制并粘贴到查询窗口中，然后单击**“执行”**。这将不执行过程，但将该过程标记为重新编译，以便在下次执行该过程时更新其查询计划。

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

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

用于“返回首页”链接的箭头图标 [返回页首]