Freigeben über

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.

Diagramm des Workflows für empfohlene Datenbankupgrades mithilfe 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 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, die in der "Regressed Queries"-Ansicht des Query Stores gefunden wurden, und geht mögliche Permutationen der anwendbaren Optimierer-Modellvariationen durch, um einen neuen, verbesserten Plan zu erstellen.

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 (durch Anfügen einer Datenbank oder RESTORE-Anweisungen) in eine neuere Version des SQL Server-Datenbankmoduls verschoben wird, und die Datenbankkompatibilitätsstufe vor dem Upgrade wird nicht sofort geändert. 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. Verwenden Sie eine Workloaddauer, die Ihrem normalen Geschäftszyklus entspricht.

  2. Anfordern des Starts der erforderlichen Workload, damit der Abfragespeicher eine Baseline für Workloaddaten erfassen kann (wenn noch keine Baseline verfügbar ist).

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

  4. Anfordern eines zweiten Durchgangs der Workloaddatenerfassung für Vergleichs- und Regressionserkennungszwecke.

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

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.

Diagramm des empfohlenen Datenbankupgradeworkflows mit 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 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. Und auch andere Abfragen in der Workload, die keine Regression gezeigt haben, können von neueren CE-Optimierungen 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 USE HINT-Abfragehinweise und Versionen der Kardinalitätsschätzung.

  • 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 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).

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

  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.

      Screenshot des Setupfensters

    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.

      Screenshot des Fensters

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

    Screenshot des Fensters zum Optimieren des Neuen Datenbankupgrades.

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.

    Screenshot der Seite

    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 mit der sys.plan_guides-Systemtabelle nach Planhinweislisten, und löschen Sie diese mit sp_control_plan_guide manuell.

  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.

      Screenshot des QTA Schritt 2-Unterschritts 1.

    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.

      Screenshot von QTA Schritt 2 Teilschritt 2 – Aktualisieren des Datenbank-Kompatibilitätslevels.

      Die folgende Seite bestätigt, dass der Datenbank-Kompatibilitätsgrad erfolgreich aktualisiert wurde.

      Screenshot des QTA Schritt 2-Unterschritts 2.

    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 diese Arbeitslast abgeschlossen ist, überprüfen Sie die fertig mit der Arbeitslast und wählen Sie „Weiter“ aus.

      Screenshot von QTA Schritt 2 Unterschritt 3.

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

    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.

    Screenshot von QTA Schritt 3.

  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 (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: 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 abhängig davon, ob die vorgeschlagene Abfrageoptimierung als Planhinweisliste bereitgestellt werden kann.

    Screenshot von QTA Schritt 4.

  6. Ü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.

    Screenshot von QTA Schritt 5.

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

    Screenshot von QTA Schritt 5 – Rollback.

    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 mit der sys.plan_guides-Systemtabelle nach Planhinweislisten, und löschen Sie diese mit sp_control_plan_guide manuell.

Berechtigungen

Erfordert Mitgliedschaft in der Rolle db_owner.

Verwandte Inhalte

  • Last updated on