Änderung des Ausführungsplans in SQL Server: Ursachen und Lösungen

Ausführungspläne — komplexe Übersichten, die vom SQL Server-Optimierer gezeichnet werden, bestimmen den effektivsten Datenabrufpfad. Die Pläne sind jedoch nicht statisch. Sie ändern sich und passen sich unter dem Einfluss vieler Faktoren an, von Änderungen der Datenmenge bis hin zu Systemaktualisierungen. Jede Änderung des Ausführungsplans in SQL Server kann die Leistung erheblich beeinträchtigen.

Wenn Sie die Ursachen dieser Änderungen verstehen und, was noch wichtiger ist, verstehen, wie sie effektiv verwaltet werden können, können Sie die optimale Leistung in SQL Server-Umgebungen aufrechterhalten.

Datenänderungen im Vergleich zum Änderungsausführungsplan in SQL Server

Einer der häufigsten Auslöser für Änderungen des Ausführungsplans in SQL Server ist eine große Änderung der Datenmenge in den Datenbanktabellen. Warum? SQL Server verwendet einen bestimmten Mechanismus; er verwaltet Statistiken zu den in der Datenbank gespeicherten Daten. Diese Statistiken werden dann vom Abfrageoptimierer verwendet, um zu ermitteln, wie die Abfrage am effizientesten ausgeführt werden kann. Wenn Daten geändert werden — durch Einfügen, Löschen oder umfangreiche Aktualisierungen — weichen diese Statistiken vom aktuellen Stand der Daten ab.

SQL Server legt Schwellenwerte fest, die bestimmen, wie viele Daten in einer Tabelle geändert werden müssen, um die Tabellenstatistiken automatisch zu aktualisieren. In der Regel liegt dieser Schwellenwert bei etwa 20% der Zeilen in der Tabelle. Wenn dieser Schwellenwert überschritten wird, markiert SQL Server die Statistiken als veraltet. Wenn Sie das nächste Mal eine Abfrage ausführen, die diese Statistiken verwendet, kann der Abfrageoptimierer die Statistiken zuerst aktualisieren. Dann kann er auf der Grundlage dieser neuen Daten einen neuen Ausführungsplan generieren.

Dieses Verhalten dient im Wesentlichen dazu, Ausführungspläne so effizient wie möglich zu gestalten. Wenn die Daten jedoch plötzlich oder kontinuierlich geändert werden, kann dies zu häufigen (und lästigen) Problemen führen Änderungen der Umsetzungspläne.

Veraltete Statistiken und Änderung des Ausführungsplans in SQL Server

Statistiken in SQL Server bieten eine Zusammenfassung der Verteilung von Daten in Tabellen. Dazu gehören Informationen wie Durchschnittswert, Minimal- und Maximalwerte sowie die Verteilung der Werte in jeder Spalte. Anhand dieser Zusammenfassungen kann der Optimierer vorhersagen, wie viele Zeilen von der Abfrage betroffen sein werden. Dies wiederum wirkt sich auf die gewählte Ausführungsstrategie aus.

Wenn sich Änderungen am Ausführungsplan in SQL Server in der Datenbank häufen, verschwindet die Genauigkeit dieser Statistiken. Dieser Rückgang der Relevanz kann den Optimierer irreführen. In einigen Fällen entscheidet sich der Optimierer möglicherweise dafür, den Index zu scannen, wenn die Indexsuche besser geeignet wäre, oder er schätzt die Anzahl der vom Filter zurückgegebenen Zeilen falsch ein, was zu suboptimalen Verbindungsmethoden oder falschen Speicherzuweisungen führt.

Die Folgen dieser Fehlinformationen sind schmerzhaft:

• Ineffiziente Abfrageleistung: Der Optimierer kann einen Plan wählen, der schlecht funktioniert, weil er auf veralteten Statistiken basiert. Dies kann dazu führen, dass Abfragen, die einmal schnell ausgeführt wurden, langsamer ausgeführt werden und mehr Ressourcen wie CPU und I/O verbrauchen, was zu umfassenderen Problemen mit der Systemleistung führt.
• Falsche Ressourcenzuweisung: Falsche Schätzungen können zu einer Über- oder Unterauslastung der SQL Server-Ressourcen führen. Überschätzungen können dazu führen, dass die Datenbank mehr Speicher als nötig reserviert, sodass weniger Speicher für andere Prozesse zur Verfügung steht. Eine Unterschätzung kann zu einer unzureichenden Speicherzuweisung führen, was zu häufigen Festplatten-I/O-Vorgängen führt, die hätten vermieden werden können.

Verringerung des Risikos, dass Statistiken veraltet sind

• Manuelle Aktualisierung der Statistiken: Verwenden Sie UPDATE STATISTICS nach erheblichen Datenänderungen, um sicherzustellen, dass die Statistiken den aktuellen Stand der Daten genau wiedergeben, was zur Wartung beiträgt Optimale Abfrageleistung.
• Passen Sie den Schwellenwert für die Aktualisierung von Statistiken an: Ändern Sie die Standardeinstellungen in SQL Server, um den Schwellenwert für die automatische Aktualisierung von Statistiken zu senken. Dies sollte zu häufigeren Aktualisierungen führen, ohne dass ein manuelles Eingreifen erforderlich ist.
• Implementierung asynchroner Statistikaktualisierungen: Aktivieren Sie AUTO_UPDATE_STATISTICS_ASYNC, um Aktualisierungen im Hintergrund zuzulassen, Verzögerungen bei Abfragen zu vermeiden und sicherzustellen, dass Ausführungspläne auf den neuesten Daten basieren.

Zustand des Indexes

Die Indexintegrität ist einer der wichtigsten Faktoren, die die Fähigkeit von SQL Server beeinflussen, den effizientesten Abfrageausführungsplan auszuwählen. Wenn Indizes gut gepflegt werden, bieten sie schnelle Pfade zu den Daten, die der Optimierer zur Erstellung von Hochleistungsplänen verwendet. Probleme wie die Indexfragmentierung können diese Entscheidungen jedoch potenziell erheblich beeinträchtigen und zu einer weniger optimalen Abfrageleistung führen.

• Fragmentierung: Im Laufe der Zeit können Indizes fragmentiert werden, wenn Daten in der Datenbank geändert — hinzugefügt, aktualisiert oder gelöscht werden. Diese Fragmentierung bedeutet, dass die logische Reihenfolge der Indexdaten verstreut wird und nicht mehr der physikalischen Reihenfolge auf der Festplatte entspricht. Die Folgen sind wie folgt:
• Erhöhte I/O-Betriebszeit: Fragmentierte Indizes führen dazu, dass der gute alte SQL Server mehr I/O-Operationen ausführt, da die Datenseiten nicht kontinuierlich sind. Dies wiederum führt dazu, dass die Engine mehrere Seiten von verschiedenen Speicherorten auf der Festplatte liest.
• Ineffektive Ausführungspläne: Der Optimierer stützt seine Entscheidungen auf das physische Layout und die Statistiken der Indizes. Wenn die Indexfragmentierung zu einer falschen Schätzung der mit der Verwendung des Indexes verbundenen Kosten führt, ist es wahrscheinlicher, dass der Optimierer die Tabelle scannt, anstatt den fragmentierten Index zu verwenden.

Lösungen:

• Pflege des Indexes: Regelmäßige Wartungsaufgaben wie das Neuerstellen oder Reorganisieren von Indizes können die Fragmentierung verringern. Die Wahl zwischen Neuentwicklung und Reorganisation hängt vom Grad der Fragmentierung ab:
  • Indizes neu erstellen: Dieser Prozess erstellt eine neue Version des Indexes, wodurch die Fragmentierung vermieden wird, Festplattenspeicher zurückgewonnen und die Indexzeilen entsprechend ihrer logischen Reihenfolge neu angeordnet werden. Die Neuentwicklung ist im Allgemeinen ressourcenintensiver und wird empfohlen, wenn die Fragmentierung schwerwiegend ist.
  • Reorganisation der Indizes: Die Reorganisation ist weniger aufwändig als die Neuerstellung und ordnet die Seiten auf Blattebene physisch neu an, sodass sie in die logische Reihenfolge passen. Es handelt sich um einen einfacheren Vorgang, der häufiger als Präventionsmaßnahme gegen Fragmentierung durchgeführt werden kann.
• Regelmäßige Überwachung
• Tools und Funktionen: Verwenden Sie Funktionen wie den SQL Server-Agenten, um den Indexverwaltungsprozess zu automatisieren. Tools wie SQL Server Management Studio (SSMS) bieten integrierte Berichte und Dashboards zur Überwachung des Zustands und der Leistung von Indizes.

Änderungen von Abfragen und Schemas

Wenn Sie mit der Struktur der Abfrage spielen oder das Schema der Datenbank anpassen, wirken sich die Wellen, wie ein kleiner Kieselstein, der in einen großen Teich geworfen wird, auf die gesamte Oberfläche aus. Selbst kleine Änderungen können SQL Server dazu zwingen, neue Ausführungspläne zu generieren.

• Änderung der Links: Eine Änderung der Art und Weise, wie Tabellen verknüpft werden, kann den Datenabrufpfad dramatischer verändern als eine Änderung der Route der Hauptautobahn.
• Anpassung des Filters: Durch das Ändern von WHERE-Klauseln werden die Daten nicht nur auf andere Weise gefiltert, sondern auch die Umgebung für den Datenabruf vollständig verändert.
• Neue Operationen eingeben: Das Hinzufügen von Elementen wie GROUP BY oder ORDER BY ändert die Abfrageanforderungen, indem Daten auf neue Weise angefordert werden, was das System auf unterschiedliche Weise belasten kann.

Änderungen der Regelung: Eine Änderung des Datenbankschemas kann ebenfalls weitreichende Folgen haben:

• Indizes anpassen: Das Hinzufügen oder Entfernen von Indizes kann die Navigation erleichtern oder zu Verwirrung führen, was sich auf die Geschwindigkeit und Effizienz des Datenabrufs auswirken kann.
• Änderung der Spalten: Das Ändern von Datentypen, das Hinzufügen von NULL-Einschränkungen oder das Ändern der Spaltengrößen können sich darauf auswirken, wie Daten gespeichert und abgerufen werden, sodass neue Ausführungspläne erforderlich sind, um diese Änderungen effektiv zu handhaben.
• Einschränkungen und Beziehungen anpassen: Änderungen an Primärschlüsseln, Fremdschlüsseln oder Kontrolleinschränkungen können die Datenintegritätsregeln und die Beziehungen zwischen Tabellen ändern, was sich auf das Verhalten von Verknüpfungen und die Gültigkeit vorhandener Ausführungspläne auswirkt.

Lösungen:

• Testen: Die Implementierung von Änderungen in einer kontrollierten Umgebung ermöglicht es Ihnen, die Auswirkungen auf die Leistung abzuschätzen und Einblicke in potenzielle reale Probleme zu erhalten.
• Aktualisierung der Statistiken: Die Aktualisierung der Statistiken nach der Änderung stellt sicher, dass der Optimierer über die aktuellsten Daten verfügt.
• Laufende Überwachung: Die kontinuierliche Überwachung der Leistung von Abfragen und Ausführungsplänen nach Änderungen hilft dabei, ineffiziente Ausführungen schnell zu erkennen und zu korrigieren.

Aktualisierungen und Zeitplanänderungen in SQL Server

Auf SQL Server-Updates folgen in der Regel Änderungen am Abfrageoptimierer, der dafür verantwortlich ist, zu entscheiden, wie Abfragen am effizientesten ausgeführt werden. Die Weiterentwicklung des Optimierers kann zu Unterschieden in der Art und Weise führen, wie Ausführungspläne festgelegt werden.

Eine der wichtigsten Änderungen, die mit dem Update auftreten können, ist Änderung des Kardinalitätsschätzers. Ihre Aufgabe besteht darin, die Anzahl der Zeilen vorherzusagen, die von verschiedenen Abfrageoperationen verarbeitet werden, was einen großen Einfluss auf die Wahl des Ausführungsplans hat. Aktualisierungen können diese Vorhersagen verfeinern, was wiederum unterschiedliche Planoptionen zur Folge hat, die möglicherweise nicht mit früheren Optimierungen übereinstimmen.

Lösungen:

• Präzise Tests: Die Tests sollten Leistungsbenchmarks beinhalten, um mögliche Regressionen zu identifizieren.
• Planverwaltung: SQL Server bietet Tools wie Query Store, um gute Ausführungspläne zu erfassen und aufzubewahren. Dies kann verwendet werden, um die Verwendung dieser Pläne auch nach dem Upgrade zu erzwingen, bis eine dauerhaftere Lösung möglich ist.
• Schrittweise Implementierung: Die schrittweise Bereitstellung von Updates in allen Umgebungen ermöglicht es den Teams, deren Auswirkungen zu überwachen und Richtlinien nach Bedarf anzupassen.

Änderungen in der Serverkonfiguration

Es ist offensichtlich, dass die Art und Weise, wie Ausführungspläne erstellt und ausgeführt werden, den Konfigurationseinstellungen des Servers nicht gleichgültig ist.

Eine der wichtigsten Einstellungen, die sich häufig auf die Wahl des Ausführungsplans auswirken, ist der maximale Grad an Parallelität (MAXDOP). Es gibt die Anzahl der Prozessoren an, die SQL Server verwenden kann, um Abfragen parallel auszuführen. Durch die Änderung von MAXDOP bestimmen Sie im Wesentlichen, wie viele CPU-Kerne SQL Server für die parallele Abfrageverarbeitung verwenden kann. Ein niedrigerer MAXDOP begrenzt den Server auf weniger Kerne, wodurch er zu einer sequenzielleren Abfrageverarbeitung gezwungen wird. Ein höherer MAXDOP veranlasst den Server, mehr Kerne zu verwenden, um komplexe Abfragen schneller auszuführen. Dies kann jedoch zu einem erhöhten Wettbewerb zwischen Prozessen führen, wenn nicht sorgfältig vorgegangen wird.

Diese Veränderungen treten jedoch nicht im luftleeren Raum auf. Sie kalibrieren das grundlegende Verhalten des SQL Server Query Optimizers neu. Diese Neukalibrierung kann manchmal dazu führen, dass zuvor zwischengespeicherte Pläne in den neuen Einstellungen nicht optimal werden, sodass SQL Server veranlasst wird, Abfragen in der neuen Systemkonfiguration neu zu kompilieren. Diese Neukompilierung ist zwar notwendig, kann aber zu vorübergehenden Leistungseinbußen führen, da sich der Server an die neuen Einstellungen anpasst.

Umgang mit Änderungen des Ausführungsplans in SQL Server

• Allmähliche Änderungen: Nehmen Sie schrittweise Änderungen an Einstellungen wie MAXDOP vor, anstatt große Änderungen vorzunehmen, um deren Auswirkungen besser einschätzen zu können.
• Verwenden von Basislinien: Leistungsbasiswerte, die Sie konfigurieren, bevor Sie Änderungen vornehmen, können hilfreich sein, um die Effektivität der Konfigurationsänderung zu messen.
• Schauen Sie sich die Best Practices an: Folgen Sie den Best Practices von Microsoft für Konfigurationseinstellungen. Die SQL Server-Dokumentation und die Empfehlungen der Community können als Hilfestellung zu diesem Problem dienen.

Sniffing-Parameter

Sniffing-Parameter bezieht sich auf das SQL Server-Optimierungsverhalten, bei dem der Abfrageoptimierer die bei der ersten Ausführung einer gespeicherten Prozedur übergebenen Parameterwerte untersucht und auf der Grundlage dieser Werte einen Plan erstellt. Dieser Prozess ist im Allgemeinen positiv — er passt den Ausführungsplan an die spezifische Belastung der Abfrage an. Es gibt jedoch Zeiten und Situationen, in denen Parameter-Sniffing zu Leistungsproblemen führen kann, wenn derselbe Ausführungsplan für sehr unterschiedliche Parameterwerte wiederverwendet wird.

Wenn die anfänglichen Parameterwerte nicht repräsentativ für die typische Verwendung sind, ist der optimierte Plan möglicherweise nicht ideal für nachfolgende Durchläufe mit unterschiedlichen Parametern. Dies kann zu einer ineffizienten Abfrageleistung, längeren Ausführungszeiten und einer erhöhten Ressourcenauslastung führen.

Schauen wir uns ein Beispiel an. Wenn eine gespeicherte Prozedur zuerst mit einem Parameter ausgeführt wird, der eine kleine Teilmenge von Zeilen abruft, kann der Abfrageoptimierer einen Plan auswählen, der die Indexsuche verwendet — sehr effizient für kleine Datensätze. Wenn derselbe Plan für einen Parameter verwendet wird, der eine größere Menge von Zeilen abrufen muss, lässt sich der Plan möglicherweise nicht gut skalieren, was zu einer geringeren Leistung und einem höheren Ressourcenbedarf führt.

Umgang mit Problemen beim Parameter-Sniffing

• Warehouse abfragen und Durchsetzen von Plänen: Mit der Abfragespeicherfunktion von SQL Server können Sie detaillierte Abfrageleistungsdaten zusammen mit Abfrageplänen erfassen und speichern. Administratoren können die Verwendung bestimmter Pläne, von denen bekannt ist, dass sie für bestimmte Abfragen gut funktionieren, manuell erzwingen und so die schädlichen Auswirkungen des Abhörens von Parametern vermeiden.
• Leitfaden zu den Plänen: Planhinweislisten ermöglichen es SQL Server-Benutzern, Abfrageaufforderungen oder feste Pläne an bestimmte Abfragen anzuhängen. Dies hilft dabei, die Wahl des Ausführungsplans zu kontrollieren — eine direkte Änderung des Anwendungscodes ist nicht erforderlich.