Zwischenspeichern und Wiederverwenden von Ausführungsplänen

Aktualisiert: 17. Juli 2006

SQL Server 2005 verfügt über einen Arbeitsspeicherpool, der zum Speichern von Ausführungsplänen und auch von Datenpuffern verwendet wird. Der Prozentsatz des Pools, der entweder für Ausführungspläne oder für Datenpuffer zugeordnet wird, verändert sich dynamisch in Abhängigkeit vom Status des Systems. Der Teil des Arbeitsspeicherpools, der zum Speichern von Ausführungsplänen verwendet wird, wird Prozedurcache genannt.

SQL Server 2005-Ausführungspläne weisen die folgenden Hauptkomponenten auf:

• Abfrageplan
  Bei dem größten Teil des Ausführungsplanes handelt es sich um eine eintrittsinvariante, schreibgeschützte Datenstruktur, die von einer beliebigen Anzahl von Benutzern verwendet werden kann. Man bezeichnet diesen Teil als Abfrageplan. Im Abfrageplan wird kein Benutzerkontext gespeichert. Im Arbeitsspeicher befinden sich immer nur eine oder zwei Kopien des Abfrageplans: eine Kopie für alle seriellen Ausführungen und eine weitere für alle parallelen Ausführungen. Die parallele Kopie deckt alle parallelen Ausführungen ab, und zwar unabhängig von ihrem Grad an Parallelität.
• Ausführungskontext
  Jeder Benutzer, der die Abfrage zurzeit ausführt, verfügt über eine Datenstruktur mit den Daten, die für diese Ausführung spezifisch sind, z. B. Parameterwerte. Diese Datenstruktur wird als Ausführungskontext bezeichnet. Die Datenstrukturen des Ausführungskontexts werden wiederverwendet. Wenn ein Benutzer eine Abfrage ausführt und eine der Strukturen nicht verwendet wird, wird diese Struktur erneut initialisiert, und zwar diesmal mit dem Kontext für den neuen Benutzer.

Wenn eine SQL-Anweisung in SQL Server 2005 ausgeführt wird, durchsucht das relationale Modul zunächst den Prozedurcache, um zu überprüfen, ob ein vorhandener Ausführungsplan für dieselbe SQL-Anweisung vorhanden ist. SQL Server 2005 verwendet sämtliche vorhandenen Pläne, die hierbei gefunden werden, wieder und spart somit den Aufwand für das erneute Kompilieren der SQL-Anweisung ein. Wenn kein Ausführungsplan vorhanden ist, generiert SQL Server 2005 einen neuen Ausführungsplan für die Abfrage.

SQL Server 2005 verwendet einen effizienten Algorithmus, um vorhandene Ausführungspläne für bestimmte SQL-Anweisungen zu suchen. In den meisten Systemen können durch das erneute Verwenden vorhandener Pläne anstelle des erneuten Kompilierens jeder SQL-Anweisung mehr Ressourcen eingespart werden, als für den Scan nach vorhandenen Plänen benötigt werden.

Die Algorithmen, die SQL-Anweisungen mit vorhandenen, nicht verwendeten Ausführungsplänen im Cache vergleichen, erfordern, dass alle Objektverweise vollqualifiziert sind. Die erste der folgenden SELECT-Anweisungen wird z. B. nicht mit vorhandenen Plänen verglichen. Für die zweite Anweisung wird jedoch ein Vergleich vorgenommen:

SELECT * FROM Contact

SELECT * FROM Person.Contact

Die Wahrscheinlichkeit, dass einzelne Ausführungspläne wiederverwendet werden, ist in einer Instanz von SQL Server 2000 und SQL Server 2005 höher als in SQL Server Version 6.5 und niedriger.

Entfernen alter Ausführungspläne

Nachdem ein Ausführungsplan generiert wurde, bleibt er im Prozedurcache. SQL Server 2005 entfernt alte, nicht mehr verwendete Pläne nur dann aus dem Cache, wenn Speicherplatz benötigt wird. Jeder Abfrageplan und Ausführungskontext verfügt über einen zugeordneten Kostenfaktor, der anzeigt, welche Kosten beim Kompilieren der Struktur anfallen. Diese Datenstrukturen verfügen darüber hinaus über ein Altersfeld. Jedes Mal, wenn eine Verbindung auf das Objekt verweist, wird das Altersfeld um den Kompilierungskostenfaktor erhöht. Wenn ein Abfrageplan z. B. den Kostenfaktor 8 hat und zweimal auf ihn verwiesen wird, erhält das Altersfeld den Wert 16. Der Prozess für verzögertes Schreiben scannt regelmäßig die Objektliste im Prozedurcache. Bei jedem Scan reduziert der Prozess für verzögertes Schreiben den Wert im Altersfeld jedes Objekts um 1. Wenn kein anderer Benutzer auf den Plan verweist, erreicht das Alter des Beispielabfrageplanes nach 16 Scans des Prozedurcaches erneut den Wert 0. Der Prozess für verzögertes Schreiben hebt die Zuordnung für ein Objekt auf, wenn folgende Bedingungen erfüllt sind:

• Der Speicher-Manager benötigt freien Arbeitsspeicher, und der gesamte verfügbare Arbeitsspeicher wird derzeit verwendet.
• Der Wert des Altersfeldes für das Objekt ist 0.
• Derzeit verweist keine Verbindung auf das Objekt.

Da das Altersfeld mit jedem Verweis auf das Objekt erhöht wird, werden die Werte in den Altersfeldern von Objekten, auf die häufig verwiesen wird, nicht auf 0 reduziert und deshalb nicht aus dem Cache entfernt. Objekte, auf die nur unregelmäßig verwiesen wird, weisen relativ schnell einen Alterswert auf, der die Aufhebung der Zuordnung möglich macht. Die Zuordnung wird jedoch erst dann tatsächlich aufgehoben, wenn der Arbeitsspeicher für andere Objekte benötigt wird.

Erneutes Kompilieren von Ausführungsplänen

Bestimmte Änderungen in einer Datenbank können dazu führen, dass ein Ausführungsplan vor dem Hintergrund des neuen Status der Datenbank entweder nicht mehr effizient oder ungültig ist. In SQL Server werden die Änderungen erkannt, die dazu führen, dass ein Ausführungsplan nicht mehr gültig ist, und der Plan wird als nicht gültig gekennzeichnet. Für die nächste Verbindung, die die Abfrage ausführt, muss dann ein neuer Plan kompiliert werden. Folgende Bedingungen können dazu führen, dass ein Plan ungültig wird:

• Änderungen, die an einer Tabelle oder einer Sicht vorgenommen werden, auf die in der Abfrage verwiesen wird (ALTER TABLE und ALTER VIEW).
• Änderungen an Indizes, die vom Ausführungsplan verwendet werden.
• Aktualisierungen der vom Ausführungsplan verwendeten Statistiken, die entweder explizit durch eine Anweisung, wie beispielsweise UPDATE STATISTICS, oder automatisch generiert werden.
• Löschen eines Indexes, der von dem Ausführungsplan verwendet wird.
• Ein expliziter Aufruf von sp_recompile.
• Eine große Anzahl von Änderungen an Schlüsseln (generiert durch INSERT- oder DELETE-Anweisungen von anderen Benutzern, die eine Tabelle ändern, auf die in der Abfrage verwiesen wird).
• Bei Tabellen mit Triggern eine deutliche Erhöhung der Zeilenanzahl in der inserted- oder der deleted-Tabelle.
• Ausführen einer gespeicherten Prozedur mithilfe der Option WITH RECOMPILE.

Die meisten Neukompilierungen sind erforderlich, damit die Anweisungen stimmen oder um möglicherweise schnellere Abfrageausführungspläne zu erhalten.

Jedes Mal, wenn in SQL Server 2000 eine in einem Batch vorhandene Anweisung eine Neukompilierung auslöst, wird der gesamte durch eine gespeicherte Prozedur, einen Trigger, einen Ad-hoc-Batch oder eine vorbereitete Anweisung übermittelte Batch neu kompiliert. In SQL Server 2005 wird nur die Anweisung innerhalb des Batches, der die Neukompilierung auslöst, erneut kompiliert. Aufgrund dieses Unterschieds kann die Anzahl der Neukompilierungen in SQL Server 2000 nicht mit der Anzahl an Neukompilierungen in SQL Server 2005 verglichen werden. Des Weiteren gibt es in SQL Server 2005 aufgrund der erweiterten Featuregruppe mehr Neukompilierungstypen.

Die Neukompilierung auf Anweisungsebene wirkt sich positiv auf die Leistung aus, da in den meisten Fällen wenige Anweisungen Neukompilierungen und die damit verbundenen Sanktionen in Bezug auf die CPU-Zeit und die Sperren verursachen. Diese Sanktionen werden daher für die anderen Anweisungen innerhalb des Batches vermieden, für die keine Neukompilierung erforderlich ist.

Das SQL Server Profiler SP:Recompile-Ablaufverfolgungsereignis meldet Neukompilierungen auf Anweisungsebene in SQL Server 2005. Dieses Ablaufverfolgungsereignis meldet nur Neukompilierungen von Batches in SQL Server 2000. Des Weiteren wird in SQL Server 2005 die TextData-Spalte dieses Ereignisses aufgefüllt. Aus diesem Grund ist die in SQL Server 2000 übliche Methode, bei der für SP:StmtStarting oder SP:StmtCompleted eine Ablaufverfolgung durchgeführt werden muss, um den Transact-SQL-Text abzurufen, durch den die Neukompilierung verursacht wurde, nicht mehr erforderlich.

SQL Server 2005 umfasst zudem ein neues Ablaufverfolgungsereignis mit der Bezeichnung SQL:StmtRecompile, das Neukompilierungen auf Anweisungsebene meldet. Diese Ablaufverfolgung kann verwendet werden, um Neukompilierungen zu verfolgen und zu debuggen. Während SP:Recompile nur für gespeicherte Prozeduren und Trigger generiert wird, wird SQL:StmtRecompile für gespeicherte Prozeduren, Trigger, Ad-hoc-Batches, Batches, die mithilfe von sp_executesql ausgeführt werden, vorbereitete Abfragen sowie für die dynamische SQL generiert.

Die EventSubClass-Spalte von SP:Recompile und SQL:StmtRecompile enthält einen ganzzahligen Code, der den Grund für die Neukompilierung angibt. Die folgende Tabelle führt die Bedeutungen der einzelnen Codenummern auf.

Hinweis:

Wenn die Datenbankoption AUTO_UPDATE_STATISTICS auf ON festgelegt ist, werden Abfragen neu kompiliert, wenn sie Tabellen oder indizierte Sichten betreffen, deren Statistiken aktualisiert wurden oder deren Kardinalitäten sich seit der letzten Ausführung signifikant geändert haben. Dieses Verhalten gilt für standardmäßige benutzerdefinierte Tabellen, temporäre Tabellen und die durch DML-Trigger erstellten inserted- und deleted-Tabellen. Wenn sich sehr viele Neukompilierungen auf die Abfrageleistung auswirken, können Sie diese Einstellung in OFF ändern. Wenn die AUTO_UPDATE_STATISTICS-Datenbankoption auf OFF festgelegt ist, werden auf der Grundlage von Statistiken oder wegen Änderungen der Kardinalität keine Neukompilierungen durchgeführt, mit Ausnahme der durch DML INSTEAD OF-Trigger erstellten inserted- und deleted-Tabellen. Da diese Tabellen in tempdb erstellt wurden, hängt die Neukompilierung von Abfragen, die auf diese Tabellen zugreifen, von der AUTO_UPDATE_STATISTICS-Einstellung in tempdb ab. Beachten Sie, dass, auch wenn diese Einstellung auf OFF festgelegt ist, Abfragen in SQL Server 2000 weiterhin auf der Grundlage der DML-Trigger-Tabellen inserted und deleted neu kompiliert werden. Weitere Informationen über die Deaktivierung von AUTO_UPDATE_STATISTICS finden Sie unter Indexstatistiken.

Siehe auch

Verweis

SQL Server, SQL-Statistik-Objekt

Konzepte

Pufferverwaltung

Andere Ressourcen

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 (in Englisch)

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf