Aktualisieren von Datenbanken mithilfe des Abfrageoptimierungs-Assistenten

Artikel
08/23/2023

Gilt für: SQL Server 2016 (13.x) und höhere VersionenAzure SQL-DatenbankAzure 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 auch bei einem Upgrade zwischen SQL Server 2014 (12.x) und jeder neueren Version zu einem geringeren Grad möglich.

Beginnend mit SQL Server 2014 (12.x) und mit jeder neuen Version werden alle Abfrageoptimiereränderungen an die neueste Datenbankkompatibilitätsebene angepasst, sodass Ausführungspläne nicht direkt am Zeitpunkt des Upgrades geändert werden, sondern wenn ein Benutzer die COMPATIBILITY_LEVEL Datenbankoption auf die neueste verfügbare Ändert. Weitere Informationen zu in SQL Server 2014 (12.x) eingeführten Abfrageoptimiereränderungen finden Sie unter Cardinality Estimator. 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.

Recommended database upgrade workflow using Query Store

Diese Kontrolle über Upgrades wurde mit SQL Server 2017 (14.x) weiter verbessert, wo die automatische Optimierung eingeführt wurde, und ermöglicht die Automatisierung des letzten Schritts im obigen empfohlenen Workflow.

Ab SQL Server Management Studio v18 führt das neue QTA-Feature (Query Tuning Assistant) Benutzer durch den empfohlenen Workflow, um während Upgrades auf neuere SQL Server-Versionen die Leistungsstabilität beizubehalten, wie im Abschnitt "Beibehalten der Leistungsstabilität während des Upgrades auf neuere SQL Server von Abfragespeicher Nutzungsszenarien" 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 zielbezogenen SQL Server-Datenbank-Engine auf andere Weise 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 verschoben wird (über CREATE DATABASE ... FOR ATTACH oder RESTORE) an eine neuere Version der SQL Server-Datenbank-Engine, und die Kompatibilitätsstufe vor dem Upgrade der Datenbank wird nicht sofort geändert. 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.

Recommended database upgrade workflow using QTA

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 von SQL Server 2012 (11.x) und Datenbankkompatibilitätsebene 110 auf SQL Server 2017 (14.x) und Datenbankkompatibilitätsebene 140 aktualisieren, können einige Abfragen zurücktreten, da sie speziell für die Verwendung der CE-Version entwickelt wurden, die in SQL Server 2012 (11.x) (CE 70) vorhanden war. 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 die Regression einführt, ist es möglich, dass diese Abfrage nur den relevanten Teil der vorherigen CE-Version verwendet, die für die spezifische Abfrage besser funktionierte, während weiterhin alle anderen Verbesserungen neuerer CE-Versionen verwendet werden. Und lassen Sie auch andere Abfragen in der Workload zu, die nicht zurückgeschritten wurden, um von neueren CE-Verbesserungen zu 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, bewirkt der Abfragehinweis USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') , dass SQL Server einen Ausführungsplan generiert, indem die Minimale Selektorivität verwendet wird, wenn AND Prädikate für Filter geschätzt werden, die für Korrelation zu berücksichtigen sind. Weitere Informationen finden Sie unter USE HINT-Abfragehinweise und Versionen der Kardinalitätsschätzung.
Simple Containment vs. Base Containment: Wenn eine andere Join-Eindämmung bessere Schätzungen für die spezifische Abfrage bereitstellt, bewirkt der Abfragehinweis USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') , dass SQL Server einen Ausführungsplan generiert, indem die Annahme für einfache Eindämmungen anstelle der Standardannahme der Basiseindämmung verwendet wird. Weitere Informationen finden Sie unter USE HINT-Abfragehinweise und Versionen der Kardinalitätsschätzung.
Multi-statement table-valued function (MSTVF) fixed Karte inality guess of 100 rows vs. 1 row: If the default fixed estimation for TVFs of 100 rows doesn't result in a effizienter plan than using the fixed estimation for TVFs of 1 row (corresponding to the default under the query optimizer CE model of SQL Server 2008 R2 (10.50.x) and früheren Versionen), anschließend wird der Abfragehinweis QUERYTRACEON 9488 verwendet, um einen Ausführungsplan zu generieren. 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

Jeder Hinweis erzwingt bestimmte Verhaltensweisen, die in zukünftigen SQL Server-Updates behoben werden können. 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 können Sie Ihre Arbeitsauslastung davon abgrenzen, dass sie von Verbesserungen profitieren kann, die in neueren Versionen von SQL Server eingeführt wurden.

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 die Objekt-Explorer und stellen Sie eine Verbindung mit Datenbank-Engine her.
Klicken Sie für die Datenbank, die für das Upgrade der Datenbankkompatibilitätsstufe vorgesehen ist, mit der rechten Maustaste auf den Datenbanknamen, wählen Sie "Aufgaben" aus, wählen Sie "Datenbankupgrade" aus, und wählen Sie "Neue Datenbankupgradesitzung" aus.
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 zweimal die Anzahl der erwarteten 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 Datenbankupgradeworkflows

Klicken Sie für die Datenbank, die für das Upgrade der Datenbankkompatibilitätsstufe vorgesehen ist, mit der rechten Maustaste auf den Datenbanknamen, wählen Sie "Aufgaben" aus, wählen Sie "Datenbankupgrade" aus, und wählen Sie "Sitzungen überwachen" aus.
Die Seite Sitzungsverwaltung listet die aktuellen und vergangenen Sitzungen für die Datenbank im Bereich auf. Wählen Sie die gewünschte Sitzung aus, und wählen Sie "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 besteht aus datenbankname, Datum und Uhrzeit der Sitzungserstellung.
- Status: Status der Sitzung („Aktiv“ oder „Geschlossen“).
- Beschreibung: Vom System generierte Daten bestehen aus der vom Benutzer ausgewählten Zieldatenbankkompatibilitätsstufe und der Anzahl der Tage für die Arbeitsauslastung des Geschäftszyklus.
- 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: Transact-SQL-Anweisung, die durch Auswählen der Schaltfläche ... erweitert werden kann.
  - Runs: Displays the number of executions of that query for the entire workload collection.
  - 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 Status 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: 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 Vor- und nach dem Experimentstatus, die die Menge der gemessenen Verbesserung für die Abfrage mit der vorgeschlagenen Optimierung darstellt.
- 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.