Aktualisieren von Datenbanken mithilfe des Abfrageoptimierungs-Assistenten

Artikel
07/10/2024

Gilt für: SQL Server 2016 (13.x) und höhere Versionen Azure SQL-Datenbank Azure Synapse Analytics Analytics Platform System (PDW)

Wenn Sie von einer älteren SQL Server-Version zu SQL Server 2014 (12.x) oder neuer migrieren und ein Upgrade auf den aktuellen Datenbank-Kompatibilitätsgrad durchführen, können bei einer Workload womöglich Leistungseinbußen auftreten. Dies ist in geringerem Ausmaß auch beim Upgrade von SQL Server 2014 (12.x) auf eine neuere Version möglich.

Ab SQL Server 2014 (12.x) und mit jeder neuen Version werden alle Änderungen an der Abfrageoptimierung auf die neueste Datenbankkompatibilitätsebene beschränkt, so dass die Ausführungspläne nicht direkt beim Upgrade geändert werden, sondern erst dann, wenn ein Benutzer die COMPATIBILITY_LEVEL-Datenbankoption auf die neueste verfügbare Version ändert. Weitere Informationen zu den Änderungen am Abfrageoptimierer, die mit SQL Server 2014 (12.x) eingeführt wurden, finden Sie unter Kardinalitätsschätzung. Weitere Informationen zu Kompatibilitätsgraden und deren Auswirkungen auf Upgrades finden Sie unter Kompatibilitätsgrade und Upgrades der Datenbank-Engine.

Diese Gatingfunktion, die durch den Datenbank-Kompatibilitätsgrad bereitgestellt wird, bietet Ihnen in Kombination mit dem Abfragespeicher große Kontrolle über die Abfrageleistung im Upgradeprozess, wenn das Upgrade dem empfohlenen Workflow folgt, der unten gezeigt wird. Weitere Informationen zum empfohlenen Workflow für das Aktualisieren des Kompatibilitätsgrads finden Sie unter Ändern des Datenbank-Kompatibilitätsmodus und Verwenden des Abfragespeichers.

Empfohlener Datenbankupgradeworkflow mit dem Abfragespeicher

Diese Kontrolle über Upgrades wurde mit SQL Server 2017 (14.x) weiter verbessert. In dieser Version wurde Automatische Optimierung eingeführt und die Automatisierung des letzten Schritts im oben empfohlenen Workflow ermöglicht.

Beginnend mit SQL Server Management Studio v18 führt die neue Funktion Abfrageoptimierungs-Assistent (Query Tuning Assistant, QTA) Benutzer durch den empfohlenen Workflow, um die Leistungsstabilität bei Upgrades auf neuere SQL Server-Versionen aufrecht zu erhalten, wie im Abschnitt Aufrechterhalten einer stabilen Leistung während des Upgrades auf eine neuere Version von SQL Server der Szenarien für die Verwendung des Abfragespeichers beschrieben. QTA führt allerdings kein Rollback auf einen bekannten funktionierenden Plan aus, wie im letzten Schritt des empfohlenen Workflows zu sehen. Stattdessen verfolgt QTA alle Regressionen nach, die in der Ansicht Abfragespeicher – Zurückgestellte Abfragen gefunden wurden, und durchläuft mögliche Permutationen von anwendbaren Variationen des Optimierungsmodells, sodass ein neuer, besserer Plan erstellt werden kann.

Wichtig

QTA generiert keine Benutzerworkload. Wenn Sie QTA in einer Umgebung ausführen, die nicht von Ihren Anwendungen verwendet wird, stellen Sie sicher, dass Sie weiterhin repräsentative Testworkloads auf dem SQL Server Datenbank-Engine-Ziel mit anderen Mitteln ausführen können.

Workflow des Abfrageoptimierungs-Assistenten

Der Ausgangspunkt von QTA geht davon aus, dass eine Datenbank aus einer früheren Version von SQL Server (über CREATE DATABASE ... FOR ATTACH oder RESTORE) an eine neuere Version der SQL Server-Datenbank-Engine verschoben wird, und die Kompatibilitätsstufe vor dem Upgrade der Datenbank nicht sofort geändert wird. QTA führt Benutzer durch die folgenden Schritte:

Konfigurieren des Abfragespeichers gemäß den empfohlenen Einstellungen für die Workloaddauer (in Tagen), die vom Benutzer festgelegt wird. Verwenden Sie eine Workloaddauer, die Ihrem normalen Geschäftszyklus entspricht.
Anfordern des Starts der erforderlichen Workload, damit der Abfragespeicher eine Baseline für Workloaddaten erfassen kann (wenn noch keine Baseline verfügbar ist).
Upgrade auf den vom Benutzer ausgewählten Zieldatenbank-Kompatibilitätsgrad.
Anfordern eines zweiten Durchgangs der Workloaddatenerfassung für Vergleichs- und Regressionserkennungszwecke.
Iterieren durch alle Regressionen, die auf Grundlage der Ansicht AbfragespeicherZurückgestellte Abfragen gefunden wurden, Experimentieren durch Erfassen von Laufzeitstatistiken für mögliche Permutationen anwendbarer Optimierungsmodellvariationen und Messen des Ergebnisses.
Melden der gemessenen Optimierungen und optional Zulassen, dass diese Änderungen mit Planhinweislisten persistent gespeichert werden.

Weitere Informationen zum Anfügen einer Datenbank finden Sie unter Anfügen und Trennen einer Datenbank.

Im Folgenden erfahren Sie, wie QTA nur die letzten Schritte des empfohlenen Workflows zur Aktualisierung des Kompatibilitätsgrads mithilfe des Abfragespeichers wie oben beschrieben ändert. QTA bietet nicht nur die Möglichkeit, zwischen dem zurzeit ineffizienten Ausführungsplan und dem letzten bekannten funktionierenden Ausführungsplan zu wählen, sondern auch Optimierungsoptionen speziell für die ausgewählten zurückgestellten Abfragen, um einen neuen verbesserten Zustand mit optimierten Ausführungsplänen zu erstellen.

Empfohlener Datenbankupgradeworkflow mit dem Abfrageoptimierungs-Assistenten

Optimieren des internen Suchbereichs durch QTA

QTA bezieht sich nur auf SELECT-Abfragen, die aus dem Abfragespeicher ausgeführt werden können. Parametrisierte Abfragen sind zulässig, wenn der kompilierte Parameter bekannt ist. Abfragen, die von Laufzeitkonstrukten wie temporären Tabellen oder Tabellenvariablen abhängen, sind zurzeit nicht zulässig.

QTA zielt auf bekannte mögliche Muster von Anfrageregressionen aufgrund von Änderungen in Kardinalitätsschätzungsversionen (Cardinality Estimator, CE). Wenn Sie beispielsweise eine Datenbank aus SQL Server 2012 (11.x) mit dem Datenbank-Kompatibilitätsgrad 110 in SQL Server 2017 (14.x) mit dem Datenbank-Kompatibilitätsgrad 140 aktualisieren, können einige Abfragen Regression zeigen, da sie speziell für die Zusammenarbeit mit der CE-Version entworfen wurden, die in SQL Server 2012 (11.x) vorhanden war (CE 70). Dies bedeutet nicht, dass das Zurücksetzen von CE 140 auf CE 70 die einzige Option ist. Wenn nur eine bestimmte Änderung in der neueren Version zur Regression führt, dann ist es möglich, diese Abfrage mit einem Hinweis zu versehen, dass sie nur den relevanten Teil der vorherigen CE-Version verwendet, der für die spezifische Abfrage besser funktioniert hat, während sie gleichzeitig alle anderen Optimierungen der neueren CE-Versionen nutzt. Und auch andere Abfragen in der Workload, die keine Regression gezeigt haben, können von neueren CE-Optimierungen profitieren.

Die folgenden CE-Muster werden von QTA gesucht:

Unabhängigkeit vs. Korrelation: Wenn die Unabhängigkeitsannahme bessere Schätzungen für die spezifische Abfrage liefert, dann bewirkt der Abfragehinweis USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'), dass SQL Server einen Ausführungsplan generiert, indem minimale Selektivität verwendet wird, wenn AND-Prädikate für Filter geschätzt werden, um die Korrelation zu berücksichtigen. Weitere Informationen finden Sie unter USE HINT-Abfragehinweise und Versionen der Kardinalitätsschätzung.
Einfache Eigenständigkeit vs. Basis-Eigenständigkeit: Wenn ein anderer Joineinschluss bessere Schätzungen für die spezifische Abfrage liefert, dann bewirkt der Abfragehinweis USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'), dass SQL Server einen Ausführungsplan generiert, indem die Annahme eines einfachen Einschlusses anstelle der Standardannahme eines Basiseinschlusses verwendet wird. Weitere Informationen finden Sie unter USE HINT-Abfragehinweise und Versionen der Kardinalitätsschätzung.
Feste Schätzung der Kardinalität für Multi-statement table-valued function (MSTVF) von 100 Zeilen vs. 1 Zeile: Wenn die feste Standardschätzung für die Tabellenwertfunktion von 100 Zeilen nicht zu einem effizienteren Plan führt als die Verwendung der festen Schätzung einer Zeile (entsprechend dem Standard unter dem CE-Abfrageoptimierermodell von SQL Server 2008 R2 (10.50.x) und früheren Versionen), wird der Abfragehinweis QUERYTRACEON 9488 verwendet, um einen Ausführungsplan zu erstellen. Weitere Informationen zu MSTVFs finden Sie unter Erstellen benutzerdefinierter Funktionen (Datenbank-Engine).

Hinweis

Als letztes Mittel (wenn die Hinweise mit engem Bereich nicht ausreichend gute Ergebnisse für die zulässigen Abfragemuster liefern) wird auch die vollständige Verwendung von CE 70 in Betracht gezogen, indem der Abfragehinweis USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') verwendet wird, um einen Ausführungsplan zu erstellen.

Wichtig

Alle Hinweise erzwingen bestimmte Verhaltensweisen, die in zukünftigen Updates von SQL Server ggf. behoben werden. Es wird empfohlen, Hinweise nur dann anzuwenden, wenn keine andere Option besteht, und bei jedem neuen Upgrade den mit Hinweisen versehenen Code erneut zu überprüfen. Durch das Erzwingen von Verhaltensweisen verhindern Sie möglicherweise, dass Ihre Workload von Optimierungen profitieren kann, die in neueren Versionen von SQL Server eingeführt werden.

Starten des Abfrageoptimierungs-Assistenten für Datenbankupgrades

QTA ist eine sitzungsbasierte Funktion, die den Sitzungszustand im msqta-Schema der Benutzerdatenbank speichert, in der zum ersten Mal eine Sitzung erstellt wird. Mehrere Optimierungssitzungen können für eine einzelne Datenbank im Lauf der Zeit erstellt werden, aber nur eine aktive Sitzung kann für eine bestimmte Datenbank vorhanden sein.

Erstellen einer Datenbankupgradesitzung

Öffnen Sie in SQL Server Management Studio den Objekt-Explorer und stellen Sie eine Verbindung zur Datenbank-Engine her.
Klicken Sie für die Datenbank, für die ein Upgrade des Datenbank-Kompatibilitätsgrads vorgesehen ist, mit der rechten Maustaste auf den Datenbanknamen, wählen Sie Aufgaben aus, wählen Sie Datenbankupgrade aus, und wählen Sie dann auf Neue Datenbankupgradesitzung.
Im Fenster des QTA-Assistenten sind zwei Schritte erforderlich, um eine Sitzung zu konfigurieren:
1. Konfigurieren Sie im Fenster Setup den Abfragespeicher so, dass er das Äquivalent eines vollständigen Geschäftszyklus von Workloaddaten erfasst, die analysiert und optimiert werden sollen.
  - Geben Sie die erwartete Workloaddauer in Tagen ein (der Mindestwert ist 1 Tag). Diese Angabe wird verwendet, um empfohlene Abfragespeichereinstellungen vorzuschlagen, um vorläufig die gesamte Baseline zu erfassen. Die Erfassung einer guten Baseline ist wichtig, um sicherzustellen, dass alle zurückgestellten Abfragen, die nach einer Änderung des Kompatibilitätsgrads der Datenbank gefunden wurden, analysiert werden können.
  - Legen Sie den vorgesehenen Zieldatenbank-Kompatibilitätsgrad fest, den die Benutzerdatenbank verwenden soll, nachdem der QTA-Workflow abgeschlossen wurde. Wählen Sie anschließend Weiter aus.
2. Im Fenster Einstellungen zeigen zwei Spalten den Status Aktuell des Abfragespeichers in der Zieldatenbank sowie die empfohlenen Einstellungen an.
  - Die empfohlenen Einstellungen sind standardmäßig ausgewählt. Wenn Sie jedoch das Optionsfeld über der Spalte „Aktuell“ auswählen, werden die aktuellen Einstellungen akzeptiert, und Sie können auch die aktuelle Konfiguration des Abfragespeichers optimieren.
  - Die vorgeschlagene Einstellung Schwellenwert für veraltete Abfragen ist zwei Mal so groß wie die erwartete Workloaddauer in Tagen. Dies liegt daran, dass der Abfragespeicher Informationen zur Baselineworkload und zur Workload nach dem Datenbankupgrade speichern muss. Wählen Sie anschließend Weiter aus.
  Wichtig
  
  Die vorgeschlagene Maximale Größe ist ein beliebiger Wert, der ggf. für eine Workload mit kurzer Ausführungszeit geeignet ist. Beachten Sie jedoch, dass es unter Umständen nicht ausreicht, Informationen zu den Baseline- und den Workloads nach dem Datenbankupgrade für sehr intensive Workloads zu speichern, insbesondere wenn viele verschiedene Pläne generiert werden können. Wenn Sie davon ausgehen, dass dies der Fall sein wird, geben Sie einen höheren Wert ein, der angemessen ist.
Im Fenster Optimierung wird die Sitzungskonfiguration abgeschlossen, und die nächsten Schritte zum Öffnen und Fortsetzen der Sitzung werden eingeleitet. Wählen Sie nach Abschluss des Vorgangs Fertig stellen aus.

Ausführen des Workflows für das Datenbankupgrade

Klicken Sie für die Datenbank, für die ein Upgrade des Datenbank-Kompatibilitätsgrads vorgesehen ist, mit der rechten Maustaste auf den Datenbanknamen, wählen Sie Aufgaben aus, wählen Sie Datenbankupgrade aus, und wählen Sie dann auf Sitzungen überwachen.
Die Seite Sitzungsverwaltung listet die aktuellen und vergangenen Sitzungen für die Datenbank im Bereich auf. Wählen Sie die gewünschte Sitzung und dann Details aus.

Hinweis

Wenn die aktuelle Sitzung nicht vorhanden ist, wählen Sie die Schaltfläche Aktualisieren aus.

Die Liste enthält die folgenden Informationen:
- Sitzungs-ID
- Sitzungsname: Vom System generierter Name, bestehend aus dem Namen der Datenbank sowie Datum und Uhrzeit der Sitzungserstellung.
- Status: Status der Sitzung („Aktiv“ oder „Geschlossen“).
- Beschreibung: Vom System generierte Beschreibung, bestehend aus dem vom Benutzer ausgewählten Kompatibilitätsgrad der Zieldatenbank und der Anzahl der Tage für die Geschäftszyklus-Workload.
- Startzeit: Das Datum und die Uhrzeit der Erstellung der Sitzung.
Hinweis

Sitzung löschen löscht alle Daten, die für die ausgewählte Sitzung gespeichert wurden. Durch das Löschen einer geschlossenen Sitzung werden zuvor bereitgestellte Planhinweislisten jedoch nicht gelöscht. Wenn Sie eine Sitzung löschen, für die Planhinweislisten bereitgestellt wurden, können Sie QTA nicht für ein Rollback verwenden. Suchen Sie stattdessen mit der sys.plan_guides-Systemtabelle nach Planhinweislisten, und löschen Sie diese mit sp_control_plan_guide manuell.
Der Einstiegspunkt für eine neue Sitzung ist der Schritt Datenerfassung.

Hinweis

Über die Schaltfläche Sitzungen kehren Sie zur Seite Sitzungsverwaltung zurück und behalten die aktive Sitzung in ihrem aktuellen Zustand bei.

Dieser Schritt hat drei Teilschritte:
1. Baselinedatenerfassung fordert den Benutzer auf, den repräsentativen Workloadzyklus auszuführen, damit der Abfragespeicher eine Baseline erfassen kann. Nachdem die Workload abgeschlossen wurde, überprüfen Sie Workloadausführung abgeschlossen, und wählen Sie dann Weiter aus.
  
  Hinweis
  
  Das QTA Fenster kann geschlossen werden, während die Workload ausgeführt wird. Die Rückkehr zur Sitzung (die im aktiven Zustand verbleibt) zu einem späteren Zeitpunkt wird mit dem gleichen Schritt fortgesetzt, bei dem sie unterbrochen wurde.
2. Upgrade der Datenbank fordert zur Erteilung der Berechtigung auf, den Kompatibilitätsgrad der Datenbank in den gewünschten Zielwert zu aktualisieren. Um mit dem nächsten Teilschritt fortzufahren, wählen Sie Ja aus.
  
  Die folgende Seite bestätigt, dass der Datenbank-Kompatibilitätsgrad erfolgreich aktualisiert wurde.
3. Beobachtete Datenerfassung fordert den Benutzer auf, den repräsentativen Workloadzyklus erneut auszuführen, sodass der Abfragespeicher eine vergleichende Baseline erfassen kann, die für die Suche nach Optimierungsmöglichkeiten verwendet wird. Verwenden Sie während der Ausführung der Workload die Schaltfläche Aktualisieren, um die Liste der zurückgestellten Abfragen zu aktualisieren, falls solche gefunden wurden. Ändern Sie den Wert für Anzuzeigende Abfragen, um die Anzahl der angezeigten Abfragen zu beschränken. Die Reihenfolge der Liste wird durch die Metrik (Dauer oder CpuTime) und die Aggregation („Mittelwert“ ist Standard) beeinflusst. Wählen Sie auch aus, wie viele Abfragen angezeigt werden sollen. Nachdem die Workload abgeschlossen wurde, überprüfen Sie Workloadausführung abgeschlossen, und wählen Sie dann Weiter aus.
  
  Die Liste enthält die folgenden Informationen:
  - Abfrage-ID
  - Abfragetext: Eine Transact-SQL-Anweisung, die durch Auswählen der Schaltfläche ... erweitert werden kann.
  - Ausführungen: Zeigt die Anzahl der Ausführungen dieser Abfrage für die gesamte Workloadsammlung an.
  - Baselinemetrik: Die ausgewählte Metrik (Dauer oder CpuTime) in Millisekunden für die Baselinedatensammlung vor dem Upgrade des Datenbank-Kompatibilitätsgrads.
  - Beobachtete Metrik: Die ausgewählte Metrik (Dauer oder CpuTime) in Millisekunden für die Datensammlung nach dem Upgrade des Datenbank-Kompatibilitätsgrads.
  - % Änderung: Prozentuale Änderung für die ausgewählte Metrik zwischen dem Zustand vor und nach dem Upgrade der Datenbankkompatibilität. Ein negativer Wert stellt die Menge der gemessenen Regression für die Abfrage dar.
  - Optimierbar: TRUE oder FALSE abhängig davon, ob die Abfrage für Experimente geeignet ist.
Analyse anzeigen ermöglicht die Auswahl, mit welchen Abfragen experimentiert werden soll, um Optimierungsmöglichkeiten zu ermitteln. Der Wert Anzuzeigende Abfragen wird zum Bereich der geeigneten Abfragen für Experimente. Nachdem die gewünschten Abfragen aktiviert wurden, wählen Sie Weiter aus, um die Experimente zu starten.

Hinweis

Abfragen mit „Optimierbar = FALSE“ können nicht für Experimente ausgewählt werden.

Wichtig

Eine Eingabeaufforderung weist darauf hin, dass die Rückkehr zur Seite „Analyse anzeigen“ nicht möglich ist, sobald QTA in die Experimentierphase übergeht.
Wenn Sie nicht alle geeigneten Abfragen auswählen, bevor Sie zur Experimentierphase übergehen, müssen Sie zu einem späteren Zeitpunkt eine neue Sitzung erstellen und den Workflow wiederholen. Dies erfordert das Zurücksetzen des Datenbank-Kompatibilitätsgrads auf den vorherigen Wert.
Ergebnisse anzeigen ermöglicht die Auswahl, für welche Abfragen die vorgeschlagene Optimierung als Planhinweisliste bereitgestellt werden soll.

Die Liste enthält die folgenden Informationen:
- Abfrage-ID
- Abfragetext: Eine Transact-SQL-Anweisung, die durch Auswählen der Schaltfläche ... erweitert werden kann.
- Status: Zeigt den aktuellen Experimentierstatus für die Abfrage an.
- Baselinemetrik: Die ausgewählte Metrik (Dauer oder CpuTime) in Millisekunden für die Abfrage, die in Schritt 2, Teilschritt 3 ausgeführt wurde. Diese Metrik stellt die zurückgestellte Abfrage nach dem Upgrade des Datenbank-Kompatibilitätsgrads dar.
- Beobachtete Metrik: Die ausgewählte Metrik (Dauer oder CpuTime) in Millisekunden für die Abfrage nach dem Experimentieren, für die eine ausreichend gute Optimierung vorgeschlagen wurde.
- % Änderung: Prozentuale Änderung für die ausgewählte Metrik zwischen dem Zustand vor und nach dem Experimentieren. Sie stellt die Menge der gemessenen Optimierung für die Abfrage mit der vorgeschlagenen Optimierung dar.
- Abfrageoption: Link zum vorgeschlagenen Hinweis, der die Abfrageausführungsmetrik verbessert.
- Kann bereitgestellt werden: TRUE oder FALSE abhängig davon, ob die vorgeschlagene Abfrageoptimierung als Planhinweisliste bereitgestellt werden kann.
Überprüfung zeigt den Bereitstellungsstatus zuvor ausgewählter Abfragen für diese Sitzung an. Die Liste auf dieser Seite unterscheidet sich von der vorherigen Seite durch Ändern der Spalte Kann bereitgestellt werden in Rollback kann ausgeführt werden. Diese Spalte kann TRUE oder FALSE abhängig davon sein, ob für die bereitgestellte Abfrageoptimierung ein Rollback ausgeführt und ihre Planhinweisliste entfernt werden kann.

Wenn zu einem späteren Zeitpunkt ein Rollback für eine vorgeschlagene Optimierung erforderlich ist, wählen Sie die entsprechende Abfrage aus, und wählen Sie dann Rollback aus. Diese Abfrageplan-Hinweisliste wird entfernt und die Liste aktualisiert, um die Abfrage zu entfernen, für die ein Rollback ausgeführt wurde. Beachten Sie in der folgenden Abbildung, dass Abfrage 8 entfernt wurde.

Hinweis

Durch das Löschen einer geschlossenen Sitzung werden zuvor bereitgestellte Planhinweislisten nicht gelöscht. Wenn Sie eine Sitzung löschen, für die Planhinweislisten bereitgestellt wurden, können Sie QTA nicht für ein Rollback verwenden. Suchen Sie stattdessen mit der sys.plan_guides-Systemtabelle nach Planhinweislisten, und löschen Sie diese mit sp_control_plan_guide manuell.