Artikel
03/30/2012

Erneutes Kompilieren gespeicherter Prozeduren

Wenn eine Datenbank durch Aktionen wie das Hinzufügen von Indizes oder das Ändern von Daten in indizierten Spalten geändert wird, sollten die ursprünglichen Abfragepläne, die für den Zugriff auf die Tabellen verwendet werden, neu optimiert werden, indem sie erneut kompiliert werden. Die Optimierung wird automatisch ausgeführt, wenn eine gespeicherte Prozedur nach dem Neustart von SQL Server das erste Mal ausgeführt wird. Sie erfolgt auch, wenn eine von der gespeicherten Prozedur verwendete zugrunde liegende Tabelle geändert wird. Wird jedoch ein neuer Index hinzugefügt, der eventuell von der gespeicherten Prozedur genutzt werden kann, wird die Optimierung erst vorgenommen, wenn die gespeicherte Prozedur nach dem nächsten Neustart von SQL Server erneut ausgeführt wird. Unter diesen Umständen kann es sinnvoll sein, das erneute Kompilieren der gespeicherten Prozedur bei deren nächster Ausführung zu erzwingen.

Ein weiterer Grund für das Erzwingen der erneuten Kompilierung einer gespeicherten Prozedur kann ggf. darin bestehen, dem "Parametersniffing"-Verhalten der Kompilierung gespeicherter Prozeduren entgegenzuwirken. Wenn SQL Server gespeicherte Prozeduren ausführt, werden alle von der Prozedur bei der Kompilierung verwendeten Parameterwerte als Bestandteil bei der Generierung des Abfrageplans eingeschlossen. Wenn es sich bei diesen Werten um die normalen Werte handelt, mit denen die Prozedur anschließend aufgerufen wird, profitiert die gespeicherte Prozedur bei jeder Kompilierung und Ausführung vom Abfrageplan. Andernfalls kann die Leistung beeinträchtigt werden.

SQL Server 2008 R2 bietet die erneute Kompilierung gespeicherter Prozeduren auf Anweisungsebene. Wenn SQL Server 2008 R2 gespeicherte Prozeduren erneut kompiliert, wird statt der gesamten Prozedur nur die Anweisung kompiliert, die die erneute Kompilierung verursacht hat. Daher verwendet SQL Server die Parameterwerte so, wie sie in der erneut kompilierten Anweisung vorliegen, wenn der Abfrageplan erneut generiert wird. Diese Werte können sich von den Werten unterscheiden, die ursprünglich an die Prozedur übergeben wurden.

Erzwingen der erneuten Kompilierung einer gespeicherten Prozedur

SQL Server bietet drei Verfahren, um die Neukompilierung einer gespeicherten Prozedur zu erzwingen:

Die gespeicherte Systemprozedur sp_recompile erzwingt eine erneute Kompilierung einer gespeicherten Prozedur, wenn diese das nächste Mal ausgeführt wird. Dazu wird der vorhandene Plan aus dem Prozedurcache gelöscht, sodass beim nächsten Ausführen der Prozedur das Erstellen eines neuen Plans erzwungen wird.
Durch das Erstellen einer gespeicherten Prozedur, die die Option WITH RECOMPILE in ihrer Definition angibt, wird angegeben, dass von SQL Server kein Plan für diese gespeicherte Prozedur zwischengespeichert wird; stattdessen wird die gespeicherte Prozedur bei jeder Ausführung erneut kompiliert. Verwenden Sie die Option WITH RECOMPILE, wenn eine gespeicherte Prozedur über Parameter verfügt, deren Werte sich von Ausführung zu Ausführung der gespeicherten Prozedur erheblich unterscheiden, sodass jedes Mal ein neuer Ausführungsplan erstellt wird. Die Verwendung dieser Option ist nicht üblich und führt dazu, dass die Ausführung der gespeicherten Prozedur mehr Zeit beansprucht, da sie bei jeder Ausführung neu kompiliert werden muss.

Falls statt der vollständigen gespeicherten Prozedur nur einzelne Abfragen in der gespeicherten Prozedur erneut kompiliert werden sollen, geben Sie den RECOMPILE-Abfragehinweis in jeder Abfrage an, die erneut kompiliert werden soll. Dieses Verhalten imitiert das weiter oben in diesem Abschnitt genannte Verhalten von SQL Server hinsichtlich der erneuten Kompilierung auf Anweisungsebene. Es werden jedoch nicht nur die aktuellen Parameterwerte der gespeicherten Prozedur verwendet, sondern der RECOMPILE-Abfragehinweis verwendet außerdem alle lokalen Variablen in der gespeicherten Prozedur, wenn die Anweisung kompiliert wird. Verwenden Sie diese Option, wenn atypische oder temporäre Werte nur in einer Untergruppe der Abfragen verwendet werden, die zu der gespeicherten Prozedur gehören. Weitere Informationen finden Sie unter Abfragehinweis (Transact-SQL).

Sie können das erneute Kompilieren der gespeicherten Prozedur erzwingen, indem Sie die Option WITH RECOMPILE angeben, wenn Sie die gespeicherte Prozedur ausführen. Verwenden Sie diese Option nur dann, wenn der von Ihnen angegebene Parameter atypisch ist oder sich die Daten seit dem Erstellen der gespeicherten Prozedur erheblich geändert haben.

Hinweis
Wird ein Objekt, auf das von einer gespeicherten Prozedur verwiesen wird, gelöscht oder umbenannt, wird ein Fehler zurückgegeben, sobald die Prozedur ausgeführt wird. Wird jedoch ein Objekt, auf das in einer gespeicherten Prozedur verwiesen wird, durch ein Objekt mit demselben Namen ersetzt, wird die gespeicherte Prozedur ausgeführt, ohne dass sie neu erstellt werden muss.