Erneutes Kompilieren einer gespeicherten Prozedur

Artikel
04/02/2014

In diesem Thema wird beschrieben, wie Sie eine gespeicherte Prozedur in SQL Server 2012 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. 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

Sicherheit
Erneutes Kompilieren einer gespeicherten Prozedur mit:

Transact-SQL

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

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.

Pfeilsymbol, dass mit dem Link "Zurück zum Anfang" verwendet wird [Nach oben]

Transact-SQL

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

Stellen Sie eine Verbindung mit dem Database Engine (Datenbankmodul) her.
Klicken Sie in der Standardleiste auf Neue Abfrage.
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;
GO

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

Stellen Sie eine Verbindung mit dem Database Engine (Datenbankmodul) her.
Klicken Sie in der Standardleiste auf Neue Abfrage.
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
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

So kompilieren Sie eine gespeicherte Prozedur mithilfe von sp_recompile erneut

Stellen Sie eine Verbindung mit dem Database Engine (Datenbankmodul) her.
Klicken Sie in der Standardleiste auf Neue Abfrage.
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
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

Pfeilsymbol, dass mit dem Link "Zurück zum Anfang" verwendet wird [Nach oben]