Optimieren von Abfragen mit dem Abfrageoptimierungs-Assistenten (QAT)

  • 5 Minuten

Sie entscheiden sich, den Abfragespeicher zu verwenden, um die Datenbankleistung vor der Migration zu überwachen und diese Daten nach dem Upgrade mit der Leistung zu vergleichen. Sie planen die Verwendung des Abfrageoptimierungs-Assistenten (Query Tuning Assistant, QTA), um zurückgestellte Abfragen zu ermitteln und Verbesserungen vorzuschlagen. In dieser Lerneinheit werden die Schritte zur Verwendung des Abfragespeichers und des QTA beschrieben, um die Abfrageleistung aufrechtzuerhalten.

Da Ihre Datenbankanwendungen wichtige Systeme sind, die die Grundlage für den Betrieb des Unternehmens bilden, müssen Sie über einen Aktionsplan und einen Notfallplan für das Upgrade verfügen. Aktualisieren Sie Kernproduktionsdatenbanken nach und nach. Legen Sie einen Testplan für die aktualisierten Datenbanken fest, um sicherzustellen, dass jede Datenbank ohne Probleme wieder in der Produktion verfügbar ist, bevor Sie weitere Datenbanken aktualisieren.

Sie können den Abfragespeicher verwenden, um die Leistung von Abfragen kontinuierlich zu überwachen. Sie können ihn zudem bei A/B-Tests nutzen, um die Auswirkungen einer Änderung (etwa eines Datenbankupgrades) zu messen. Der QTA bietet automatische Unterstützung nach einem Upgrade, um zurückgestellte Abfragen basierend auf den im Abfragespeicher erfassten Daten zu suchen und zu korrigieren.

Damit der QTA aber ordnungsgemäß funktioniert, müssen Sie die folgenden Schritte der Reihe nach anwenden.

Wichtig

Stellen Sie sicher, dass Sie Ihre Produktionsanwendungsdatenbank oder eine Datenbank mit einer Anwendungsworkload verwenden, die weitgehend der Workload in der Produktionsdatenbank entspricht, damit der Abfragespeicher realistische Metriken für die Abfragen sammeln kann.

  1. Migrieren Sie die Datenbank zu SQL Server 2022.
  2. Verändern Sie den Kompatibilitätsgrad in der vorherigen SQL Server-Version nicht.
  3. Aktivieren Sie den Abfragespeicher für die Datenbank.
  4. Lassen Sie den Abfragespeicher Baselinemetriken für die Abfragen basierend auf ausreichend realistischer Benutzeraktivität erfassen.
  5. Aktualisieren Sie den Kompatibilitätsgrad auf SQL Server 2022 (160).
  6. Lassen Sie den Abfragespeicher erneut Daten für die Abfragen basierend auf ausreichend realistischer Benutzeraktivität erfassen.
  7. Verwenden Sie den QTA, um die Leistung von Abfragen vor und nach der Änderung des Datenbank-Kompatibilitätsgrads zu vergleichen. Wenn zurückgestellte Abfragen gefunden werden, ermitteln Sie Korrekturmaßnahmen.

Migrieren der Datenbank

Wenn Sie zum Wechsel zu SQL Server 2022 bereit sind, migrieren Sie zuerst Ihre Datenbank zu der neuen Instanz. Für die Migration stehen mehrere Vorgehensweisen zur Verfügung. Dies könnte beispielsweise eine einfache Sicherung und Wiederherstellung, eine Datenbankspiegelung oder ein Massenladen sein. Die am besten geeignete Option ist abhängig von der Konfiguration Ihrer aktuellen Umgebung und der SQL Server-Version, von der aus Sie migrieren. Azure Data Migration Service (DMS) ist eine gute Lösung, da sie Datenbanken ab SQL Server 2005 unterstützt.

Hinweis

Der Azure DMS unterstützt nicht die Datenbankmigration zu Azure SQL Managed Instance. Verwenden Sie die Azure SQL-Migrationserweiterung für Azure Data Studio, um zu beginnen.

Kompatibilitätsgrad unverändert lassen

Lassen Sie nach der Migration der Datenbank den Kompatibilitätsgrad unverändert. Dieser Schritt ist sehr wichtig, da die Baseline mithilfe der aktuellen Datenbankkonfiguration gemessen werden soll. Bis Sie den Kompatibilitätsgrad auf SQL Server 2014 (120) oder höher umstellen, verwendet SQL Server die Legacy-Kardinalitätsschätzung. In SQL Server 2014 wurde eine aktualisierte Kardinalitätsschätzung eingeführt, die für die meisten Abfragen von Vorteil ist, aber selten negative Auswirkungen auf die Leistung haben kann.

Aktivieren des Abfragespeichers

Obwohl der Datenbank-Kompatibilitätsgrad bei der vorherigen Version verbleibt, können Sie den Abfragespeicher für die Datenbank aktivieren, da er ein Feature auf Serverebene ist. So aktivieren Sie den Abfragedatenspeicher

  1. Klicken Sie in SQL Server Management Studio (SSMS) mit der rechten Maustaste auf die Datenbank, und wählen Sie Eigenschaften aus.
  2. Wählen Sie im Fenster Datenbankeigenschaften im linken Bereich Abfragespeicher aus.
  3. Legen Sie Betriebsmodus (angefordert) auf Schreibgeschützt oder Lesen und schreiben fest.
  4. Klickan Sie auf OK.

Alternativ können Sie die folgende Anweisung ausführen, um den Abfragespeicher im Standardmodus READ WRITE zu aktivieren:

ALTER DATABASE <database-name> SET QUERY_STORE = ON

Zulassen, dass der Abfragespeicher Daten sammelt

Versetzen Sie Ihre Datenbank wieder in die Produktion, und schalten Sie alle Datenbankverbindungen mit Anwendungen oder Berichten um. Die Datenbank beginnt mit dem Empfangen von Abfragen aus Produktionsanwendungen. Lassen Sie dem Abfragespeicher ausreichend Zeit, um eine realistische Workload für die Datenbank zu erfassen.

Der Abfragespeicher sollte einen typischen Zyklus von Geschäftsaktivitäten erfassen, einschließlich Geschäftszeiten, nächtlicher Verarbeitung, Wartungsfenster und anderer Aktivitäten. Für viele Unternehmen reichen die Aktivitäten einer Woche aus, aber für einige Unternehmen kann dieser Zeitraum kürzer oder länger sein.

Viele Unternehmen haben umfassende Geschäftszyklen und somit einzigartige Aktivitäten für die zweiwöchentliche Gehaltsabrechnung oder die Verarbeitung am Monatsende. Sie sollten den zeitlichen Ablauf von Geschäftszyklen in Ihrer Datenbankumgebung kennen. Bei einem Lebensmittelgeschäft decken die wöchentlichen Zyklen für den Wareneingang und die Wiederauffüllung die meisten Datenbankaktivitäten ab.

Sie können die gesammelten Daten auf den Registerkarten des Abfragespeichers anzeigen. Erweitern Sie zum Anzeigen der Registerkarten in SSMS im Objekt-Explorer die Datenbankstruktur, um den Abfragespeicher anzuzeigen. Wenn Ihrer Einschätzung nach genügend Daten erfasst wurden, können Sie das Upgrade planen.

Aktualisieren des Kompatibilitätsgrads

Bevor Sie eine Änderung an einer Datenbank vornehmen, sollten Sie sie sichern. Tun Sie dies möglichst außerhalb der Arbeitszeit. Aktualisieren Sie nach der Sicherung den Kompatibilitätsgrad wie folgt:

  1. Klicken Sie in SSMS im Objekt-Explorer mit der rechten Maustaste auf die Datenbank, und wählen Sie Eigenschaften aus.
  2. Wählen Sie im Fenster Datenbankeigenschaften die Registerkarte Optionen aus.
  3. Aktualisieren Sie den Kompatibilitätsgrad auf SQL Server 2022 (160), und wählen Sie OK aus.

Alternativ können Sie die folgende Anweisung ausführen:

ALTER DATABASE <database-name> SET COMPATIBILITY_LEVEL = 160

Zulassen, dass der Abfragespeicher weiterhin Daten sammelt

Nachdem Ihre Datenbank aktualisiert wurde und Anwendungen fortgesetzt werden, wird der Abfragespeicher weiterhin im Hintergrund ausgeführt, um Metriken für die Abfragen zu sammeln. Die Abfragen sind jetzt aufgrund der neuen Kardinalitätsschätzung, die vom Abfrageoptimierer verwendet wird, potenziellen Problemen ausgesetzt.

Führen Sie den Abfragespeicher weiter aus, und legen Sie fest, dass er genauso lange Daten sammelt wie vor dem Upgrade. Die Abfrageregression wird jedoch u. U. sofort angezeigt, sodass Sie sofort Maßnahmen ergreifen können, um Leistungsprobleme zu beheben.

Ausführen des Abfrageoptimierungs-Assistenten

Führen Sie den QTA aus, um alle zurückgestellten Abfragen zu behandeln. So konfigurieren Sie den QTA

  1. Klicken Sie in SSMS im Objekt-Explorer mit der rechten Maustaste auf die Datenbank, und wählen Sie Aufgaben>Datenbankupgrade>Neue Datenbankupgradesitzung aus.
  2. Geben Sie auf dem Bildschirm Setup des Abfrageoptimierungs-Assistenten einen Wert für Zu erfassender Workloadzeitraum (in Tagen) und Kompatibilitätsgrad der Zieldatenbank ein.
  3. Wählen Sie Weiter aus, um die Bildschirme Einstellungen und Optimierung zu konfigurieren.
  4. Wählen Sie Fertig stellenaus.

Um den QTA zu überwachen, klicken Sie mit der rechten Maustaste auf den Datenbanknamen, und wählen Sie Aufgaben>Datenbankupgrade>Sitzungen überwachen aus. Der QTA liefert Ihnen einen Zusammenfassungsbericht zu den wichtigsten zurückgestellten Abfragen, wobei die beobachteten Daten mit den Basisdaten verglichen werden. Anschließend können Sie die vom QTA empfohlenen Änderungen anzeigen, um die Abfrage dort zu optimieren, wo die Leistung beeinträchtigt ist.

Zusammenfassung

Verwenden Sie den QTA nach Ihrem Datenbankupgrade zum Suchen und Korrigieren von Abfragen, die aufgrund des Upgrades zurückgestellt werden. Damit der QTA zurückgestellte Abfragen finden kann, müssen Sie zuerst eine Baseline erstellen, indem Sie mithilfe des Abfragespeichers Abfragen mit dem alten Kompatibilitätsgrad messen.

Nach dem Upgrade sammelt der Abfragespeicher Metriken, die Sie beim QTA zum Vergleichen der neuen Leistung mit der Baseline verwenden können. Es ist sehr wichtig für den Einsatz des QTA, dass der Abfragespeicher vor und nach dem Upgrade Daten sammelt.

Wenn der QTA zurückgestellte Abfragen findet, ermittelt er im Experiment die besten Aktionen zur Leistungsverbesserung. Diese Aktionen können anschließend angewendet werden.