重新編譯預存程序

發行項
05/04/2023

適用於：SQL Server Azure SQL Database Azure SQL 受控執行個體 Analytics Platform System (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)