重新编译存储过程

项目
07/15/2024

适用于： SQL Server Azure SQL 数据库 Azure SQL 托管实例分析平台系统 (PDW)

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

开始之前

建议

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

注意

在 Azure Synapse Analytics 专用池和无服务器池中，存储过程不是预编译代码，因此无法重新编译。有关详细信息，请参阅对 Azure Synapse Analytics 中的专用 SQL 池使用存储过程。

安全性

权限

WITH RECOMPILE 选项

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

如果在 EXECUTE 语句中使用此选项，则需要对该过程的 EXECUTE 权限。需要对 EXECUTE 语句本身的权限，而无需对 EXECUTE 语句中引用的过程的执行权限。有关更多信息，请参阅 EXECUTE (Transact-SQL)。

RECOMPILE 查询提示

创建过程时使用该功能，并且此提示包含在该过程中的 Transact-SQL 语句中。因此，它需要在数据库中有 CREATE PROCEDURE 权限，对在其中创建过程的架构有 ALTER 权限。

sp_recompile 系统存储过程

需要具有对指定过程的 ALTER 权限。

“使用 Transact-SQL”

连接到数据库引擎。
在“标准”栏上，选择“新建查询”。

将以下示例复制并粘贴到查询窗口中，然后选择“执行”。该示例将创建过程定义。

USE AdventureWorks2022;  
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 选项重新编译存储过程

选择“新建查询”，然后将以下代码示例复制粘贴到查询窗口，并选择“执行”。此操作将执行该过程，并重新编译过程的查询计划。

USE AdventureWorks2022;  
GO  
EXECUTE HumanResources.uspProductByVendor WITH RECOMPILE;  
GO

使用 sp_recompile 重新编译存储过程

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

USE AdventureWorks2022;  
GO  
EXEC sp_recompile N'dbo.uspProductByVendor';   
GO

后续步骤

创建存储过程
 修改存储过程
 重命名存储过程
 查看存储过程的定义
 查看存储过程的依赖关系
 DROP PROCEDURE (Transact-SQL)

通过