Optimieren von Abfragen nach der Migration

  • 5 Minuten

Nehmen wir an, Sie sind ein Datenbankadministrator für eine Anwaltskanzlei. Am Wochenende haben Sie einige Datenbanken zu SQL Server 2019 migriert, einschließlich der CRM-Datenbank (Customer Relationship Management). Sie sind Montagmorgen früh im Büro und erwarten, dass das Vertriebsteam in einigen Stunden eintrifft und Telefonate mit Kunden führt. Für das Vertriebsteam ist die CRM-Datenbank ausschlaggebend, und die Anwendung muss umgehend reagieren, damit das Team effektiv mit Kund*innen arbeiten kann.

Nach der Migration der Datenbanken haben Sie die vorherige Einstellung für den Kompatibilitätsgrad beibehalten. Die CRM-Anwendungen sind mit dem neuen Server verbunden. Sie möchten die Leistung der Abfragen im neuen System anhand der Workload eines typischen Montags messen, bevor Sie den Kompatibilitätsgrad ändern. Beim Durchführen von Upgrades für Datenbanken ist es wichtig, die Leistung von Abfragen aufrechtzuerhalten.

Anstatt darauf zu warten, dass das Vertriebsteam feststellt, dass die CRM-Anwendung langsam ist, möchten Sie einen Prozess einrichten, der die Arbeitsauslastung und Leistung automatisch misst. Dadurch wird sichergestellt, dass Sie langsame Abfragen nach der Änderung des Kompatibilitätsgrads beheben können.

Abfragespeicher

Das Feature Abfragespeicher wurde mit SQL Server 2016 eingeführt, um kontinuierlich Informationen über Ihre Abfragen zu erfassen. Der Abfragespeicher erfasst Informationen, die Sie für die Problembehandlung für Abfragen mit schlechter Leistung verwenden können. Das Feature funktioniert sowohl bei lokalen Installationen als auch bei Cloudinstallationen. Zwischengespeicherte Abfragepläne werden im Prozedurcache gespeichert, jedoch speichert SQL Server nur den aktuellsten Ausführungsplan.

Wahrscheinlich ändern sich die Ausführungspläne einer Abfrage im Laufe der Zeit aufgrund von Schemaänderungen oder dem Hinzufügen oder Löschen von Indizes. Außerdem können Pläne bei überlastetem Speicher aus dem Plancache entfernt werden.

Der Abfragespeicher ist standardmäßig deaktiviert, wenn Sie eine Datenbank mit SQL Server 2017 oder SQL Server 2019 erstellen oder dahin migrieren. Der Abfragespeicher kann jedoch aktiviert werden, bevor Sie den Datenbank-Kompatibilitätsgrad für Ihre migrierte Datenbank aktualisieren. Aktivieren Sie den Abfragespeicher, indem Sie im Objekt-Explorer mit der rechten Maustaste auf die Datenbank klicken, Eigenschaften auswählen und anschließend die Seite Abfragespeicher auswählen. In der Auswahlliste Vorgangsmodus (Angefordert) können Sie eine der Optionen Deaktiviert, Schreibgeschützt oder Lesen/Schreiben auswählen. Sie können den Abfragespeicher auch mithilfe von Transact-SQL aktivieren. Führen Sie beispielsweise den folgenden Befehl aus, um den Abfragespeicher für eine Datenbank namens CustomerServices zu aktivieren:

ALTER DATABASE CustomerServices SET QUERY_STORE = ON

Wenn eine Abfrage kompiliert wird, werden der Abfragetext und der erste Plan an den Abfragespeicher übermittelt. Wenn eine Abfrage erneut kompiliert wird, wird der Plan im Abfragespeicher aktualisiert. Nach Erstellung eines neuen Plans fügt der Abfragespeicher den Plan hinzu und speichert ihn mit den vorherigen Plänen und den Ausführungsstatistiken.

Wenn die Abfrage ausgeführt wird, speichert der Abfragespeicher die Laufzeitstatistiken. Während der Kompilierung oder erneuten Kompilierung überprüft SQL Server, ob ein Plan im Abfragespeicher enthalten ist, der auf die Abfrage angewendet werden sollte. Wenn ein erzwungener Plan sich von dem im Prozedurcache unterscheidet, wird die Abfrage erneut kompiliert.

Die neusten Änderungen des Abfrageoptimierers werden erst dann für die Datenbank übernommen, wenn Sie den Kompatibilitätsgrad ändern. Sie können eine typische Workload erfassen und eine Baseline für die Messung basierend auf dem vorhandenen Kompatibilitätsgrad erstellen. Nachdem Sie ausreichend Abfrageinformationen für Ihre Leistungsbaseline erfasst haben, ändern Sie den Datenbank-Kompatibilitätsgrad in die Serverversion.

Stellen Sie sicher, dass Sie ausreichend Daten für einen typischen Zeitraum von Geschäftsaktivitäten erfasst haben, um eine angemessene Baseline zu erstellen. Wenn Sie zum aktuellen Serverkompatibilitätsgrad wechseln, wird die Workload dem aktuellen Abfrageoptimierer zur Verfügung gestellt. Die Abfrageleistung verbessert sich wahrscheinlich, da der neue Abfrageoptimierer bessere Pläne erzeugen sollte.

Manchmal verwendet SQL Server einen weniger optimalen Abfrageplan, was die Ausführung plötzlich und ohne eindeutigen Grund verlangsamt. Dieses Problem kann durch Erzwingen eines vorherigen Plans im Abfragespeicher behoben werden. Die Verwendung des Abfragespeichers ähnelt der Verwendung des Abfragehinweises USE PLAN, erfordert jedoch keine Änderungen an Benutzeranwendungen.

Nach der Änderung des Kompatibilitätsgrad stellt der Abfragespeicher weiterhin Abfrageunterstützung bereit, um die beste Leistung zu gewährleisten. Er misst und überprüft Folgendes: Zurückgestellte Abfragen, Ressourcenverbrauch gesamt, Abfragen mit dem höchsten Ressourcenverbrauch, Abfragen mit erzwungenen Plänen, Abfragen mit hoher Abweichung und Nachverfolgte Abfragen.

Automatische Abfrageoptimierung

Die Aufgabe der Kardinalitätsschätzung in SQL Server besteht darin, die Anzahl der zurückgegebenen Zeilen in einer Abfrage zu schätzen. Der Abfrageoptimierer verwendet das Ergebnis der Kardinalitätsschätzung, um einen Ausführungsplan zu generieren. Die Kardinalitätsschätzung blieb bis SQL Server 2014 nahezu unverändert. Mit dem Release von SQL Server 2014 wurden die Kardinalitätsschätzungsalgorithmen überarbeitet, um Schätzungen zu verbessern und die schnellsten Abfrageergebnisse zu liefern. Allerdings bestimmt der Datenbank-Kompatibilitätsgrad, welche Kardinalitätsschätzung verwendet wird.

Ab SQL Server 2014 sollte die Abfrageleistung zwar wesentlich besser sein, jedoch kann es bei migrierten Datenbanken mit aktualisiertem Kompatibilitätsgrad zu Leistungsbeeinträchtigungen kommen. Die automatische Abfrageoptimierung wurde bei SQL Server 2017 eingeführt. Verwenden Sie das Feature zusammen mit dem Abfragespeicher, um zurückgestellte Abfragen automatisch zu ermitteln und zu beheben. Nachdem Ihre Datenbank migriert und die Arbeitsauslastung der Datenbank beim vorherigen Kompatibilitätsgrad erfasst wurde, upgraden Sie den Kompatibilitätsgrad und führen den Abfragespeicher weiter aus.

Die automatische Optimierung verwendet die Daten im Abfragespeicher, um Abfragen zu ermitteln, die aufgrund von Änderungen in der Kardinalitätsschätzung nach dem Wechsel des Kompatibilitätsgrads zurückgestellt werden. Die automatische Optimierung experimentiert mit den zurückgestellten Abfragen, um die Leistung zu verbessern, wodurch Sie bei Verbesserungen Planhinweislisten erstellen können.

Optimieren der Abfrageleistung in SQL Server 2019

Im Beispiel der Anwaltskanzlei können Sie sowohl den Abfragespeicher als auch die automatische Optimierung verwenden, um die Leistung zu optimieren und sicherzustellen, dass die Änderung am Kompatibilitätsgrad keine Regressionen verursacht, da Sie für die Datenbankserver ein Upgrade auf SQL Server 2019 durchgeführt haben. Wir empfehlen den folgenden Prozess:

  1. Führen Sie für die Datenbankserver ein Upgrade auf SQL Server 2019 durch, aber ändern Sie nicht den Kompatibilitätsgrad.
  2. Aktivieren Sie den Abfragedatenspeicher.
  3. Erlauben Sie dem Abfragespeicher, Leistungsdaten während den normalen Geschäftszeiten zu erfassen.
  4. Führen Sie ein Upgrade für den Datenbank-Kompatibilitätsgrad durch.
  5. Überprüfen Sie, ob der Abfragespeicher Regressionen ermittelt hat. Wenn dies der Fall ist, können Sie den letzten als funktionierend bekannten Abfrageplan erzwingen, um zu einer höheren Leistungsstufe zurückzukehren.

Der Abfragespeicher automatisiert die Überwachung der Leistung Ihrer Abfragen. Es ist ein nützliches Tool, das im Hintergrund ausgeführt werden kann, um sicherzustellen, dass Ihre Datenbank kontinuierlich Ergebnisse auf die schnellste Weise liefert. In Kombination mit dem Abfragespeicher nutzt der Abfrageoptimierungs-Assistent (Query Tuning Assistant, QTA) die Daten im Abfragespeicher, um zurückgestellte Abfragen in Ihrer aktualisierten Datenbank zu ermitteln.

Nachdem eine Datenbank migriert wurde und für den Kompatibilitätsgrad ein Upgrade auf SQL Server 2014 oder höher durchgeführt wurde, wird die neue Kardinalitätsschätzung verwendet, um die Anzahl der zurückgegebenen Zeilen einer Abfrage zu schätzen. Verwenden Sie den Abfrageoptimierungs-Assistenten, um zurückgestellte Abfragen bei der neuen Kardinalitätsschätzung zu ermitteln und zu beheben.