Erneutes Kompilieren einer gespeicherten Prozedur

  • Artikel

In diesem Thema wird beschrieben, wie Sie eine gespeicherte Prozedur in SQL Server 2014 mithilfe von Transact-SQL neu kompilieren. Hierfür gibt es drei Möglichkeiten: WITH RECOMPILE Option in der Prozedurdefinition oder beim Aufruf der Prozedur, abfragehinweise RECOMPILE für einzelne Anweisungen oder mithilfe der gespeicherten sp_recompile Systemprozedur. In diesem Thema wird die Verwendung der WITH RECOMPILE-Option beim Erstellen einer Prozedurdefinition und Ausführen einer vorhandenen Prozedur beschrieben. Zudem wird erläutert, wie die gespeicherte Systemprozedur sp_recompile zum erneuten Kompilieren einer vorhandenen Prozedur verwendet wird.

In diesem Thema

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 Serverbei 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).

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.

sp_recompile Gespeicherte Systemprozedur
Erfordert die ALTER-Berechtigung für die angegebene Prozedur.

Verwenden von Transact-SQL

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

  1. Stellen Sie eine Verbindung mit dem Datenbank-Engineher.

  2. Klicken Sie in der Standardleiste auf Neue Abfrage.

  3. Kopieren Sie das folgende Beispiel, fügen Sie es in das Abfragefenster ein, und klicken Sie auf Ausführen. In diesem Beispiel wird die Prozedurdefinition erstellt.

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;

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

  1. Stellen Sie eine Verbindung mit dem Datenbank-Engineher.

  2. Klicken Sie in der Standardleiste auf Neue Abfrage.

  3. Kopieren Sie das folgende Beispiel, fügen Sie es in das Abfragefenster ein, und klicken Sie auf Ausführen. In diesem Beispiel wird eine einfache Prozedur erstellt, die alle Mitarbeiter (mit Vor- und Nachnamen), ihre Stellenbezeichnungen und ihre Abteilungsnamen aus einer Sicht zurückgibt.

    Kopieren Sie anschließend das zweite Codebeispiel, fügen Sie es in das Abfragefenster ein, und klicken Sie dann auf Ausführen. Dadurch wird die Prozedur ausgeführt und der Abfrageplan der Prozedur erneut kompiliert.

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

So kompilieren Sie eine gespeicherte Prozedur mithilfe von sp_recompile erneut

  1. Stellen Sie eine Verbindung mit dem Datenbank-Engineher.

  2. Klicken Sie in der Standardleiste auf Neue Abfrage.

  3. Kopieren Sie das folgende Beispiel, fügen Sie es in das Abfragefenster ein, und klicken Sie auf Ausführen. In diesem Beispiel wird eine einfache Prozedur erstellt, die alle Mitarbeiter (mit Vor- und Nachnamen), ihre Stellenbezeichnungen und ihre Abteilungsnamen aus einer Sicht zurückgibt.

    Kopieren Sie anschließend das folgende Beispiel, fügen Sie es in das Abfragefenster ein, und klicken Sie dann auf Ausführen. 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 AdventureWorks2012;  
GO  
EXEC sp_recompile N'HumanResources.uspGetAllEmployees';  
GO

Weitere Informationen

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)