• Artikel

Zwischenspeichern und Wiederverwenden von Ausführungsplänen

SQL Server 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-Ausführungspläne weisen die folgenden Hauptkomponenten auf:

  • Abfrageplan

    Bei dem größten Teil des Ausführungsplans 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.

Ausführungskontext, gleiche Abfrage, andere Literale

Wenn eine SQL-Anweisung in SQL Server 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 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 einen neuen Ausführungsplan für die Abfrage.

SQL Server 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 Person;

SELECT * FROM Person.Person;

Entfernen von Ausführungsplänen aus dem Prozedurcache

Ausführungspläne verbleiben im Prozedurcache, solange ausreichend Speicherplatz für deren Speicherung zur Verfügung steht. Wenn nicht ausreichend Speicherplatz zur Verfügung steht, ermittelt Database Engine (Datenbankmodul) kostenbasiert, welche Ausführungspläne aus dem Prozedurcache entfernt werden. Für die kostenbasierte Entscheidung erhöht und senkt Database Engine (Datenbankmodul) die aktuelle Kostenvariable für sämtliche Ausführungspläne anhand der im Folgenden aufgeführten Faktoren.

Wenn ein Benutzerprozess einen Ausführungsplan in den Cache einfügt, werden die aktuellen Kosten auf die Kosten der ursprünglichen Abfragekompilierung festgelegt. Für Ad-hoc-Ausführungspläne legt der Benutzerprozess die aktuellen Kosten auf 0 (null) fest. Jedes Mal, wenn danach ein Benutzerprozess auf einen Ausführungsplan verweist, werden die aktuellen Kosten auf die ursprünglich kompilierten Kosten zurückgesetzt. Für Ad-hoc-Ausführungspläne erhöht der Benutzerprozess die aktuellen Kosten. Für alle Pläne entspricht der maximale Wert für die aktuellen Kosten den Kosten der ursprünglichen Kompilierung.

Wenn nicht ausreichend Speicherplatz zur Verfügung steht, werden von Database Engine (Datenbankmodul) Ausführungspläne aus dem Prozedurcache gelöscht. Um zu ermitteln, welche Pläne entfernt werden sollen, überprüft Database Engine (Datenbankmodul) wiederholt den Status sämtlicher Ausführungspläne. Die Pläne, deren aktuelle Kosten 0 (null) betragen, werden entfernt. Ein Ausführungsplan, dessen aktuelle Kosten 0 (null) betragen, wird bei unzureichendem Speicher nicht automatisch entfernt. Der Ausführungsplan wird nur bei einer Überprüfung durch Database Engine (Datenbankmodul) entfernt, wenn die aktuellen Kosten 0 (null) betragen. Wird ein Ausführungsplan derzeit nicht von einer Abfrage verwendet, werden bei der Überprüfung des Plans die aktuellen Kosten von Database Engine (Datenbankmodul) durch Reduzieren dieser Kosten gegen 0 (null) gesenkt.

Database Engine (Datenbankmodul) überprüft die Ausführungspläne wiederholt, bis genügend Ausführungspläne entfernt wurden, um die Speicheranforderungen zu erfüllen. Wenn nicht ausreichend Speicher zur Verfügung steht, können die Kosten eines Ausführungsplans mehrmals erhöht und gesenkt werden. Sobald wieder ausreichend Speicher zur Verfügung steht, werden die aktuellen Kosten nicht verwendeter Ausführungspläne von Database Engine (Datenbankmodul) nicht mehr gesenkt. Alle Ausführungspläne verbleiben im Prozedurcache, auch wenn die Kosten 0 (null) betragen.

Wenn nicht ausreichend Speicher zur Verfügung steht, verwendet Database Engine (Datenbankmodul) den Ressourcenmonitor und Benutzerthreads, um Speicherplatz im Prozedurcache freizugeben. Vom Ressourcenmonitor und von den Benutzerthreads können gleichzeitig ausgeführte Pläne überprüft werden, um die Kosten für die nicht verwendeten Ausführungspläne zu senken. Wenn nicht ausreichend allgemeiner Speicher zur Verfügung steht, werden durch den Ressourcenmonitor Ausführungspläne aus dem Prozedurcache gelöscht. Dadurch wird die Einhaltung von Richtlinien für den Systemspeicher, Prozessspeicher, Ressourcenpoolspeicher und die maximale Größe aller Caches erzwungen.

Die maximale Größe für alle Caches ist eine Funktion der Pufferpoolgröße und kann den maximalen Serverarbeitsspeicher nicht überschreiten. Weitere Informationen zum Konfigurieren des maximalen Serverarbeitsspeichers finden Sie in der Einstellung Max. Serverarbeitsspeicher in sp_configure (Transact-SQL).

Wenn nicht ausreichend Einzelcachespeicher zur Verfügung steht, werden durch die Benutzerthreads Ausführungspläne aus dem Prozedurcache gelöscht. Dadurch wird die Einhaltung der Richtlinien für die maximale Einzelcachegröße und die maximale Anzahl von Einzelcacheeinträgen erzwungen.

In den folgenden Beispielen wird erläutert, welche Ausführungspläne aus dem Prozedurcache entfernt werden:

  • Auf einen Ausführungsplan wird regelmäßig verwiesen, sodass seine Kosten nie den Wert 0 (null) erreichen. Der Plan verbleibt im Prozedurcache und wird nur dann entfernt, wenn nicht genügend Arbeitsspeicher vorhanden ist und die aktuellen Kosten 0 (null) sind.

  • Ein Ad-hoc-Ausführungsplan wird eingefügt. Auf diesen wird erst wieder verwiesen, wenn nicht ausreichend Speicherplatz zur Verfügung steht. Ad-hoc-Pläne werden mit einem Wert für die aktuellen Kosten von 0 (null) initialisiert. Daher wird der Plan aus dem Prozedurcache entfernt, wenn der Ausführungsplan vom Datenbankmodul überprüft wird und die aktuellen Kosten 0 (null) betragen. Der Ad-hoc-Ausführungsplan verbleibt im Prozedurcache mit aktuellen Kosten vom Wert 0 (null), wenn genügend Arbeitsspeicher vorhanden ist.

Um einen einzelnen Plan oder alle Pläne manuell aus dem Cache zu entfernen, verwenden Sie DBCC FREEPROCCACHE (Transact-SQL).

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 und höheren Versionen 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 uund späteren Versionen nicht für die Anzahl der Neukompilierungen als Vergleich herangezogen werden. Des Weiteren gibt es in SQL Server 2005 und höheren Versionen aufgrund der erweiterten Funktionsgruppe 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 ProfilerSP:Recompile-Ablaufverfolgungsereignis meldet Neukompilierungen auf Anweisungsebene. Dieses Ablaufverfolgungsereignis meldet nur Neukompilierungen von Batches in SQL Server 2000. Des Weiteren wird 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.

Das SQL:StmtRecompile-Ablaufverfolgungsereignis meldet Neukompilierungen auf Anweisungsebene. 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.

EventSubClass-Wert

Beschreibung

1

Schema geändert.

2

Statistiken geändert.

3

Verzögerte Kompilierung.

4

SET-Option geändert.

5

Temporäre Tabelle geändert.

6

Remote-Rowset geändert.

7

FOR BROWSE-Berechtigung geändert.

8

Abfragebenachrichtigungsumgebung geändert.

9

Partitionierte Sicht geändert.

10

Cursoroptionen geändert.

11

OPTION (RECOMPILE) angefordert.
HinweisHinweis

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 Kardinalitätsänderungen in den DML-Trigger-Tabellen inserted und deleted neu kompiliert werden. Weitere Informationen über die Deaktivierung von AUTO_UPDATE_STATISTICS finden Sie unter Verwenden von Statistiken zum Verbessern der Abfrageleistung.