Erneutes Kompilieren einer gespeicherten Prozedur

Artikel
07/15/2024

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Analytics Platform System (PDW)

In diesem Artikel wird beschrieben, wie Sie eine gespeicherte Prozedur in SQL Server mithilfe von Transact-SQL erneut kompilieren. Hierzu stehen drei Möglichkeiten zur Verfügung: die WITH RECOMPILE-Option in der Prozedurdefinition, beim Aufrufen der Prozedur der RECOMPILE-Abfragehinweis in einzelnen Anweisungen oder die gespeicherte Systemprozedur sp_recompile.

Vorbereitungen

Empfehlungen

Wenn eine Prozedur zum ersten Mal kompiliert oder erneut kompiliert wird, wird der Abfrageplan der Prozedur für den aktuellen Status der Datenbank und die darin enthaltenen Objekte optimiert. Werden bedeutende Änderungen an den Daten oder der Struktur einer Datenbank vorgenommen, wird der Abfrageplan der Prozedur bei der erneuten Kompilierung für diese Änderungen aktualisiert und optimiert. Dies kann die Verarbeitungsleistung der Prozedur verbessern.
In manchen Fällen muss die erneute Prozedurkompilierung erzwungen werden, in anderen findet sie automatisch statt. Eine automatische erneute Kompilierung erfolgt bei jedem Neustart von SQL Server. Sie findet auch statt, wenn physische Entwurfsänderungen an einer zugrunde liegenden Tabelle vorgenommen wurden, auf die die Prozedur verweist.
Eine erneute Kompilierung einer Prozedur kann auch erzwungen werden, um dem "Parametersniffing"-Verhalten bei der Kompilierung von Prozeduren entgegenzuwirken. Wenn SQL Server Prozeduren ausführt, werden alle bei der Kompilierung von der Prozedur verwendeten Parameterwerte in die Generierung des Abfrageplans einbezogen. Handelt es sich bei diesen Werten um die normalen Werte, mit denen die Prozedur anschließend aufgerufen wird, profitiert die Prozedur bei jeder Kompilierung und Ausführung vom Abfrageplan. Falls die Parameterwerte der Prozedur häufig untypisch sind, kann die Leistung durch Erzwingen einer erneuten Kompilierung der Prozedur und einen neuen Plan auf Grundlage anderer Parameterwerte verbessert werden.
SQL Server ermöglicht die erneute Kompilierung von Prozeduren auf Anweisungsebene. Wenn SQL Server gespeicherte Prozeduren erneut kompiliert, wird anstelle der vollständigen Prozedur nur die Anweisung kompiliert, die die erneute Kompilierung verursacht hat.
Wenn bei bestimmten Abfragen in einer Prozedur regelmäßig untypische oder temporäre Werte verwendet werden, kann die Prozedurleistung verbessert werden, indem der RECOMPILE-Abfragehinweis in diesen Abfragen verwendet wird. Da anstelle der vollständigen Prozedur nur die Abfragen mit dem Abfragehinweis erneut kompiliert werden, wird das Verhalten von SQL Server bei der erneuten Kompilierung auf Anweisungsebene imitiert. Zusätzlich zu den aktuellen Parameterwerten der Prozedur verwendet der RECOMPILE-Abfragehinweis beim Kompilieren der Anweisung auch die Werte von lokalen Variablen in der gespeicherten Prozedur. Weitere Informationen finden Sie unter Abfragehinweis (Transact-SQL).

Hinweis

In dedizierten und serverlosen Pools in Azure Synapse Analytics sind gespeicherte Prozeduren kein vorkompilierter Code und können daher nicht erneut kompiliert werden. Weitere Informationen finden Sie unter Verwenden gespeicherter Prozeduren für dedizierte SQL-Pools in Azure Synapse Analytics.

Sicherheit

Berechtigungen

WITH RECOMPILE-Option

Wenn diese Option beim Erstellen der Prozedurdefinition verwendet wird, erfordert sie die CREATE PROCEDURE-Berechtigung für die Datenbank und die ALTER-Berechtigung für das Schema, in dem die Prozedur erstellt wird.

Wenn diese Option in einer EXECUTE-Anweisung verwendet wird, erfordert sie EXECUTE-Berechtigungen für die Prozedur. Berechtigungen für die EXECUTE-Anweisung selbst sind nicht erforderlich, es sind jedoch Ausführungsberechtigungen für die Prozedur erforderlich, auf die in der EXECUTE-Anweisung verwiesen sind. Weitere Informationen finden Sie unter EXECUTE (Transact-SQL).

RECOMPILE-Abfragehinweis

Diese Funktion wird beim Erstellen der Prozedur verwendet, und der Hinweis wird in Transact-SQL-Anweisungen in der Prozedur eingeschlossen. Daher erfordert sie die CREATE PROCEDURE-Berechtigung für die Datenbank und die ALTER-Berechtigung für das Schema, in dem die Prozedur erstellt wird.

Gespeicherte Systemprozedur sp_recompile

Erfordert die ALTER-Berechtigung für die angegebene Prozedur.

Verwenden von Transact-SQL

Stellen Sie eine Verbindung mit dem Datenbank-Engineher.
Wählen Sie in der Standardleiste Neue Abfrage aus.

Kopieren Sie das folgende Beispiel in das Abfragefenster, und klicken Sie dann auf Ausführen. In diesem Beispiel wird die Prozedurdefinition erstellt.

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;

So kompilieren Sie eine gespeicherte Prozedur mithilfe der WITH RECOMPILE-Option erneut

Wählen Sie Neue Abfrage aus, kopieren Sie anschließend das folgende Codebeispiel und fügen Sie es in das Abfragefenster ein und wählen Sie dann Ausführen aus. Dadurch wird die Prozedur ausgeführt und der Abfrageplan der Prozedur erneut kompiliert.

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

So kompilieren Sie eine gespeicherte Prozedur mithilfe von sp_recompile erneut

Wählen Sie Neue Abfrage aus, kopieren Sie anschließend das folgende Beispiel und fügen Sie es in das Abfragefenster ei, und wählen Sie dann Ausführen aus. Dadurch wird die Prozedur nicht ausgeführt, aber für die erneute Kompilierung markiert, sodass ihr Abfrageplan bei der nächsten Ausführung der Prozedur aktualisiert wird.

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

Nächste Schritte

Erstellen einer gespeicherten Prozedur
Ändern einer gespeicherten Prozedur
Umbenennen einer gespeicherten Prozedur
Anzeigen der Definition einer gespeicherten Prozedur
Anzeigen der Abhängigkeiten einer gespeicherten Prozedur
DROP PROCEDURE (Transact-SQL)

Teilen über