Aktualisieren von Datenbanken mithilfe des Abfrageoptimierungs-Assistenten

Gilt für: SQL Server 2016 (13.x) und höhere Versionen

Wenn Sie von einer älteren Version von SQL Server zu SQL Server 2014 (12.x) oder höher migrieren und die Datenbankkompatibilitätsstufe auf die neuesten verfügbaren Versionen aktualisieren, kann eine Workload dem Risiko einer Leistungsregression ausgesetzt sein. Dies ist in geringerem Ausmaß auch beim Upgrade von SQL Server 2014 (12.x) auf eine neuere Version möglich.

In SQL Server 2014 (12.x) und höheren Versionen werden alle Abfrageoptimiereränderungen an die neueste Datenbankkompatibilitätsebene gegatet, sodass Ausführungspläne nicht direkt zum Zeitpunkt des Upgrades geändert werden, sondern wenn ein Benutzer die COMPATIBILITY_LEVEL Datenbankoption auf die neueste verfügbare Ändert. Weitere Informationen zu abfrageoptimiereränderungen, die in SQL Server 2014 (12.x) eingeführt wurden, finden Sie unter Kardinalitätsschätzung (SQL Server).For more information on query optimizer changes introduced in SQL Server 2014 (12.x), see Cardinality Estimation (SQL Server) Weitere Informationen zu Kompatibilitätsgraden und deren Auswirkungen auf Upgrades finden Sie unter Kompatibilitätsgrade und Upgrades der Datenbank-Engine.

Diese Gatingfunktion, die von der Datenbankkompatibilitätsebene bereitgestellt wird, bietet Ihnen in Kombination mit dem Abfragespeicher eine hervorragende Kontrolle über die Abfrageleistung im Upgradeprozess, wenn das Upgrade dem empfohlenen Workflow folgt, der im nächsten Diagramm zu sehen ist. Weitere Informationen zum empfohlenen Workflow für das Upgrade der Kompatibilitätsstufe finden Sie unter Ändern der Datenbankkompatibilitätsstufe und Verwenden des Abfragespeichers.

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

Ab SQL Server Management Studio v18 führt das Feature Abfrageoptimierungs-Assistent (Query Tuning Assistant, QTA) Benutzer durch den empfohlenen Workflow, um die Leistungsstabilität während Upgrades auf neuere SQL Server-Versionen beizubehalten, wie im Abschnitt "Beibehalten der Leistungsstabilität während des Upgrades auf neuere SQL Server von Abfragespeicher-Nutzungsszenarien" beschrieben. QTA setzt jedoch nicht auf einen zuvor bekannten funktionsfähigen Plan zurück, wie im letzten Schritt des empfohlenen Workflows dargestellt. Stattdessen verfolgt QTA alle Regressionen, die in der "Regressed Queries"-Ansicht des Abfragespeichers gefunden wurden, und geht mögliche Permutationen der anwendbaren Optimierer-Modellvariationen durch, um einen neuen, verbesserten Plan zu erstellen.

Workflow des Abfrageoptimierungs-Assistenten

Der Ausgangspunkt von QTA geht davon aus, dass eine Datenbank aus einer früheren Version von SQL Server (durch Anfügen einer Datenbank oder RESTORE Anweisungen) in eine neuere Version des SQL Server-Datenbank-Engine verschoben wird und die Datenbankkompatibilitätsebene vor dem Upgrade nicht sofort geändert wird. QTA führt durch die folgenden Schritte:

1. Konfigurieren des Abfragespeichers gemäß den empfohlenen Einstellungen für die Workloaddauer (in Tagen), die vom Benutzer festgelegt wird. Denken Sie an die Dauer der Workload, die Ihrem typischen Geschäftszyklus entspricht.

2. Anforderung zum Starten der erforderlichen Workload, damit Abfragespeicher eine Baseline der Workloaddaten erfassen kann (falls noch keine Baseline verfügbar ist).

3. Upgrade auf den vom Benutzer ausgewählten Zieldatenbank-Kompatibilitätsgrad.

4. Anfordern, dass Workloaddaten ein zweites Mal zum Vergleich und zur Erkennung von Regressionen erfasst werden.

5. 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.

6. Die gemessenen Verbesserungen melden und optional zulassen, dass diese Änderungen mithilfe von Planhinweisen beibehalten werden.

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

Das folgende Diagramm zeigt, wie QTA nur die letzten Schritte des empfohlenen Workflows ändert, um die Kompatibilitätsstufe mithilfe des zuvor verwendeten Abfragespeichers zu aktualisieren. Anstatt zwischen dem derzeit ineffizienten Ausführungsplan und dem letzten bekannten guten Ausführungsplan zu wählen, stellt QTA Optimierungsoptionen bereit, die für die ausgewählten regressierten Abfragen spezifisch sind, um einen neuen verbesserten Zustand mit abgestimmten Ausführungsplänen zu erstellen.

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 Abfrageregressionen aufgrund von Änderungen in Sql Server-Versionen (Kardinalitätsschätzung) ab. 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. Außerdem können auch andere Abfragen innerhalb der Workload, bei denen keine Regression aufgetreten ist, von neueren CE-Verbesserungen profitieren.

Die von QTA durchsuchten CE-Muster sind:

• Unabhängigkeit versus 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 bei der Einschätzung von Prädikaten für Filter minimale Selektivität genutzt wird, um die Korrelation zu berücksichtigen. Weitere Informationen finden Sie unter Abfragehinweise zu USE HINT und Versionen der CE.

• Einfache Eindämmung im Vergleich zur Basiseindämmung: Wenn eine andere Verknüpfungseindä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ämmung anstelle der standardannahme für basisbezogene Eindämmung verwendet wird. Weitere Informationen finden Sie unter USE HINT-Abfragehinweise und Versionen der Kardinalitätsschätzung.

• Feste Kardinalitätsschätzung für Tabellenwertfunktionen mit mehreren Anweisungen (MSTVF) von 100 Zeilen gegenüber 1 Zeile: Wenn die standardmäßige feste Schätzung für Tabellenwertfunktionen von 100 Zeilen nicht zu einem effizienteren Plan führt als die Verwendung der festen Schätzung von 1 Zeile (was dem Standardwert im CE-Modell des Abfrageoptimierers von SQL Server 2008 R2 (10.50.x) und früheren Versionen entspricht), wird der Abfragehinweis QUERYTRACEON 9488 verwendet, um einen Ausführungsplan zu erzeugen. Weitere Informationen zu MSTVFs finden Sie unter Erstellen benutzerdefinierter Funktionen (Datenbank-Engine).

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.

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

1. Öffnen Sie in SQL Server Management Studio den Objekt-Explorer, und stellen Sie eine Verbindung mit dem Datenbankmodul her.

2. 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.

3. 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). Dies wird verwendet, um empfohlene Abfragespeichereinstellungen vorzuschlagen, damit die gesamte Baseline vorläufig erfasst werden kann. 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 aktuellen Status des Abfragespeichers in der Zieldatenbank und 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.

      • Der vorgeschlagene Schwellenwert für veraltete Abfragen ist das Doppelte der erwarteten Arbeitsbelastungsdauer in Tagen. Dies liegt daran, dass der Abfragespeicher Informationen über die geplante Arbeitsauslastung und die Arbeitsauslastung nach dem Upgrade der Datenbank enthalten muss.

      Wählen Sie anschließend Weiter aus.

      

      Wichtig

      Die vorgeschlagene maximale Größe ist ein beliebiger Wert, der für einen kurzen zeitgerechten Workload geeignet sein kann. Möglicherweise reicht es jedoch nicht aus, Informationen zu den Grundlasten und zu den Lasten nach einem Datenbank-Upgrade für Intensivlasten zu speichern, insbesondere wenn viele verschiedene Pläne generiert werden können. Wenn Sie davon ausgehen, dass dies der Fall ist, geben Sie einen höheren Wert ein, der geeignet ist.

4. Das Fenster Tuning schließt die Sitzungskonfiguration ab und enthält Anweisungen zu den nächsten Schritten, um die Sitzung zu öffnen und fortzusetzen. Wählen Sie nach Abschluss des Vorgangs Fertig stellen aus.

   

Ausführen des Workflows für das Datenbankupgrade

1. 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.

2. 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, die Planhandbücher bereitgestellt hat, können Sie QTA nicht zum Zurücksetzen verwenden. Suchen Sie stattdessen in der sys.plan_guides-Systemtabelle nach Planhinweisen, und löschen Sie diese manuell mit sp_control_plan_guide.

3. 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 dieser Workload abgeschlossen ist, kontrollieren Sie Abgeschlossen mit Workload-Lauf und wählen Sie Weiter aus.

      Hinweis

      Das QTA Fenster kann geschlossen werden, während die Workload ausgeführt wird. Wenn Sie zu einer Sitzung zurückkehren, die noch aktiv ist, setzen Sie genau an dem Schritt fort, an dem Sie zuvor aufgehört haben.

      

   2. Datenbankupgrade fragt nach der Erlaubnis, das Kompatibilitätslevel der Datenbank auf das gewünschte Ziel 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. Die beobachtete Datensammlung fordert den Benutzer auf, den repräsentativen Arbeitsauslastungszyklus erneut auszuführen, sodass der Abfragespeicher einen vergleichenden Basisplan sammeln kann, der 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 dieser Workload abgeschlossen ist, kontrollieren Sie Abgeschlossen mit Workload-Lauf und wählen Sie 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.

4. Analyse anzeigen ermöglicht die Auswahl, mit welchen Abfragen experimentiert werden soll, um Optimierungsmöglichkeiten zu ermitteln. Der Wert Anzuzeigende Abfragen bestimmt den Umfang der Abfragen, die für Experimente infrage kommen. Nachdem die gewünschten Abfragen aktiviert wurden, wählen Sie Weiter aus, um die Experimente zu starten.

   Abfragen, für die "Tunable" auf "False " festgelegt ist, können nicht für Experimente ausgewählt werden.

   Wichtig

   Eine Eingabeaufforderung weist darauf hin, dass die Rückkehr zur Ansichtsanalyseseite nicht möglich ist, sobald QTA zur Experimentierphase wechselt. 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.

   

5. 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 (Duration oder CpuTime) in ms für die Abfrage, wie sie in Schritt 2, Teilschritt 3 ausgeführt wurde; sie stellt die Regression aufweisende Abfrage nach dem Upgrade der Datenbankkompatibilität 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: Gibt die Prozentuale Änderung für die ausgewählte Metrik zwischen dem Vor- und nach dem Experimentstatus an, 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, je nachdem, ob die vorgeschlagene Abfrageoptimierung als Planguide bereitgestellt werden kann.

   

6. Überprüfung zeigt den Bereitstellungsstatus zuvor ausgewählter Abfragen für diese Sitzung an. Die Liste auf dieser Seite unterscheidet sich von der auf der vorherigen Seite dadurch, dass die Spalte Bereitstellung möglich in Rollback möglich geändert wurde. Diese Spalte kann True oder False sein, je nachdem, ob die bereitgestellte Abfrageoptimierung rückgängig gemacht und der zugehörige Planleitfaden 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 "Rollback" aus. Dieser Abfrageplanleitfaden wird entfernt, und die Liste wird aktualisiert, um die zurückgesetzte Abfrage zu entfernen. 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, die Planhandbücher bereitgestellt hat, können Sie QTA nicht zum Zurücksetzen verwenden. Suchen Sie stattdessen in der sys.plan_guides-Systemtabelle nach Planhinweisen, und löschen Sie diese manuell mit sp_control_plan_guide.

Berechtigungen

Erfordert Mitgliedschaft in der Rolle db_owner.

Verwandte Inhalte

• Kompatibilitätsgrade und Upgrades der Datenbank-Engine
• Leistungsüberwachungs- und Optimierungstools
• Überwachen der Leistung mithilfe des Abfragespeichers
• Ändern des Datenbank-Kompatibilitätsgrads und Verwenden des Abfragespeichers
• Festlegen von Traceflags mit DBCC TRACEON (Transact-SQL)
• USE HINT-Abfragehinweise
• Kardinalitätsschätzung (SQL Server)
• Automatische Optimierung
• Verwenden des SQL Server-Abfrageoptimierungs-Assistenten