Query Store Usage Scenarios (Verwendungsszenarien für den Abfragespeicher)

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics

Der Abfragespeicher kann in einer großen Bandbreite von Szenarien eingesetzt werden, wenn das Nachverfolgen und Sicherstellen einer vorhersagbaren Arbeitsleistung entscheidend ist. Diese Beispiele dienen zur Veranschaulichung:

• Bestimmen und Reparieren von Abfragen mit Planauswahlregression

• Ermitteln und Optimieren von Abfragen mit dem höchsten Ressourcenverbrauch

• A/B-Tests

• Aufrechterhalten einer stabilen Leistung während des Upgrades auf neuere SQL Server-Versionen

• Erkennen und Verbessern von Ad-hoc-Workloads

• Weitere Informationen zum Konfigurieren und Verwalten mit dem Abfragespeicher finden Sie unter Überwachen der Leistung mit dem Abfragespeicher.

• Informationen zur Ermittelung handlungsrelevanter Informationen und zur Leistungsoptimierung mit dem Abfragespeicher finden Sie unter Optimieren der Leistung mit dem Abfragespeicher.

• Informationen zum Betrieb des Abfragespeichers in Azure SQL-Datenbank finden Sie unter Betrieb des Abfragespeichers in Azure SQL-Datenbank.

Bestimmen und Reparieren von Abfragen mit Planauswahlregression

Im Rahmen der regulären Abfrageausführung wählt der Abfrageoptimierer möglicherweise einen anderen Plan aus, da sich wichtige Eingangsparameter geändert haben: Z. B. kann sich die Datenkardinalität geändert haben, es wurden Indizes erstellt, geändert oder gelöscht oder Statistikinformationen aktualisiert. In der Regel funktioniert der neue Plan besser oder etwa gleich gut wie der vorherige. Es gibt jedoch Fälle, in denen der neue Plan deutlich schlechter funktioniert – diese Situation wird als Planauswahl-Änderungsregression bezeichnet. Vor der Einführung des Abfragespeichers war das Problem schwer zu erkennen und zu beheben, da SQL Server keinen integrierten Datenspeicher bereitstellte, in dem Benutzer*innen nach Ausführungsplänen suchen konnten, die im Lauf der Zeit verwendet worden waren.

Mit dem Abfragespeicher lassen sich diese Aufgaben schnell lösen:

• Identifizieren aller Abfragen, deren Ausführungsmetrik im interessierenden Zeitraum (letzte Stunde, letzter Tag, letzte Woche usw.) heruntergestuft wurde. Verwenden Sie zur Beschleunigung der Analyse zurückgestellte Abfragen in SQL Server Management Studio.

• Unter den zurückgestellten Abfragen lassen sich leicht diejenigen mit mehreren Plänen identifizieren, deren Leistung sich aufgrund der Wahl eines schlechten Plans verschlechtert hat. Verwenden Sie den Bereich Planzusammenfassung in Zurückgestellte Abfragen , um alle Pläne für eine zurückgestellte Abfrage und ihre Abfrageleistung im zeitlichen Verlauf darzustellen.

• Setzen Sie die Verwendung des im Verlauf vorhergehenden Plans durch, wenn der sich als besser erwiesen hat. Verwenden Sie die Schaltfläche Plan erzwingen in Zurückgestellte Abfragen, um die Verwendung des ausgewählten Plans für die Abfrage durchzusetzen.

Eine detaillierte Beschreibung des Szenarios finden Sie im Blog Query Store: A flight data recorder for your database (Abfragespeicher: Ein Flugdatenschreiber für Ihre Datenbank).

Ermitteln und Optimieren von Abfragen mit dem höchsten Ressourcenverbrauch

Zwar können im Rahmen Ihrer Arbeitsauslastung Tausende Abfragen generiert werden, normalerweise verwendet jedoch nur eine Handvoll den größten Teil der Systemressourcen und erfordert daher Ihre Aufmerksamkeit. Unter den Abfragen mit dem höchsten Ressourcenverbrauch finden sich in der Regel zurückgestellte Abfragen oder solche, die durch weitere Optimierung verbessert werden können.

Die Untersuchung lässt sich am einfachsten durch Öffnen von Abfragen mit dem höchsten Ressourcenverbrauch in Management Studio beginnen. Die Benutzeroberfläche ist in drei Bereiche unterteilt: Ein Histogramm, das die Abfragen mit dem höchsten Ressourcenverbrauch darstellt (links), eine Planzusammenfassung für die ausgewählte Abfrage (rechts) und eine visuellen Abfrageplan für den ausgewählten Plan (unten). Klicken Sie auf Konfigurieren, um die Anzahl der zu analysierenden Abfragen und das relevante Zeitintervall festzulegen. Darüber hinaus können Sie unter verschiedenen Dimensionen des Ressourcenverbrauchs (Dauer, CPU, Arbeitsspeicher, E/A, Anzahl der Ausführungen) und der Baseline (Mittel, Min, Max, Summe, Standardabweichung) wählen.

Betrachten Sie die Planzusammenfassung auf der rechten Seite, um den Ausführungsverlauf zu analysieren und sich über die verschiedenen Pläne und ihre Laufzeitstatistik zu informieren. Verwenden Sie den unteren Bereich, um die verschiedenen Pläne zu untersuchen oder sie nebeneinander visuell zu vergleichen (mithilfe der Schaltfläche „Vergleichen“).

Wenn Sie eine Abfrage mit nicht optimaler Leistung identifiziert haben, richtet sich das weitere Vorgehen nach der Art des Problems:

1. Wurde die Abfrage mit mehreren Plänen ausgeführt und war der letzte Plan signifikant schlechter als der vorherige, können Sie den Mechanismus zur Planerzwingung verwenden, um sicherzustellen, dass SQL Server bei zukünftigen Ausführungen den optimalen Plan verwendet.

2. Überprüfen Sie, ob der Optimierer Hinweise auf fehlende Indizes im XML-Plan gibt. Wenn das der Fall ist, erstellen Sie den fehlenden Index, und verwenden Sie den Abfragespeicher, um die Abfrageleistung nach erfolgter Indexerstellung zu bewerten

3. Vergewissern Sie sich, dass die Statistiken für die zugrunde liegenden Tabellen aktuell sind, die von der Abfrage verwendet werden.

4. Überprüfen Sie, ob die von der Abfrage verwendeten Indizes defragmentiert sind.

5. Ziehen Sie bei aufwändigen Abfragen eine Neuerstellung in Erwägung. Nutzen Sie beispielsweise die Vorteile der Abfrageparametrisierung, und verringern Sie den Einsatz von dynamischem SQL. Implementieren Sie nach dem Lesen der Daten die optimale Logik (führen Sie Datenfilterung auf der Datenbankseite statt auf der Anwendungsseite aus).

A/B-Tests

Verwenden Sie den Abfragespeicher, um die Workloadleistung vor und nach der Änderung der Anwendung zu vergleichen.

Die folgende Liste enthält eine Reihe von Beispielen, für die der Abfragespeicher eingesetzt werden kann, um den Einfluss der Umgebungs- oder Anwendungsänderung auf die Arbeitsleistung zu beurteilen:

• Einführen einer neuen Anwendungsversion.

• Hinzufügen neuer Hardware auf dem Server.

• Erstellen von fehlenden Indizes in Tabellen, auf die von aufwendigen Abfragen verwiesen wird

• Anwenden einer Filterrichtlinie für Sicherheit auf Zeilenebene. Weitere Informationen finden Sie unter Optimieren von Sicherheit auf Zeilenebene mithilfe des Abfragespeichers.

• Hinzufügen von temporaler Verwaltung durch das System zu Tabellen, die häufigen Änderungen durch OLTP-Anwendungen unterliegen.

Wenden Sie für jedes dieser Szenarien den folgenden Arbeitsablauf an:

1. Führen Sie Ihre Arbeitsauslastung mit dem Abfragespeicher vor der geplanten Änderung aus, um die Basislinie für die Leistung zu erstellen.

2. Wenden Sie die Anwendungsänderung zum vorgesehen Zeitpunkt an.

3. Führen Sie die Arbeitsauslastung danach für einen ausreichend langen Zeitraum aus, um ein Leistungsbild des Systems nach der Änderung zu erstellen

4. Vergleichen Sie die Ergebnisse von Nr. 1 und Nr. 3.

   1. Öffnen Sie Datenbank Gesamtverbrauch, um den Einfluss auf die gesamte Datenbank zu bestimmen.

   2. Öffnen Sie Abfragen mit dem höchsten Ressourcenverbrauch (oder führen Sie Ihre eigene Analyse mithilfe von Transact-SQL aus), um die Auswirkungen der Änderung auf die wichtigsten Abfragen zu analysieren.

5. Entscheiden Sie, ob die Änderung beibehalten oder ein Rollback für den Fall ausgeführt werden soll, dass die neue Leistung nicht akzeptabel ist.

In der folgenden Abbildung ist die Abfragespeicheranalyse (Schritt 4) im Fall eines fehlenden, nicht erstellten Index dargestellt. Öffnen Sie den Bereich Abfragen mit dem höchsten Ressourcenverbrauch /Planzusammenfassung, um diese Ansicht für die Abfrage zu erstellen, auf die sich die Indexerstellung auswirken soll:

Darüber hinaus können Sie Pläne vor und nach der Indexerstellung vergleichen, indem Sie sie nebeneinander anzeigen. (Symbolleistenoption „Pläne für die ausgewählte Abfrage in einem separaten Fenster vergleichen“, die auf der Symbolleiste mit einem roten Quadrat gekennzeichnet ist.)

Der Plan (plan_id = 1, oben) enthält vor der Indexerstellung einen Hinweis auf einen fehlenden Index, und Sie können durch die Untersuchung bestätigen, dass „Clustered Index Scan“ der Operator mit dem höchsten Ressourcenverbrauch in der Abfrage war (rotes Rechteck).

Der Plan verwendet nach der Erstellung des fehlenden Index (plan_id = 15, unten) jetzt „Index Seek (Nonclustered)“, wodurch sich der Gesamtaufwand der Abfrage verringert und die Leistung verbessert (grünes Rechteck).

Auf der Grundlage der Analyse ist wohl wahrscheinlich, dass Sie den Index beibehalten möchten, da sich die Abfrageleistung verbessert hat.

Aufrechterhalten einer stabilen Leistung während des Upgrades auf neuere SQL Server-Versionen

Vor SQL Server 2014 (12.x) waren Benutzer*innen dem Risiko einer nachlassenden Leistung während des Upgrades auf die neueste Plattformversion ausgesetzt. Der Grund liegt darin, dass die neueste Version des Abfrageoptimierers sofort aktiviert wurde, sobald neue Teile installiert wurden.

Seit SQL Server 2014 (12.x) sind alle Änderungen des Abfrageoptimierers an den neuesten Datenbank-Kompatibilitätsgrad gebunden, sodass Pläne nicht sofort im Moment des Upgrades geändert werden, sondern erst, wenn ein Benutzer/eine Benutzerin COMPATIBILITY_LEVEL auf die neueste Version aktualisiert. Diese Möglichkeit gibt Ihnen in Kombination mit dem Abfragespeicher ein großes Maß an Kontrolle über die Abfrageleistung im Upgradeprozess. Der empfohlene Upgradeworkflow ist in der folgenden Abbildung dargestellt:

1. Upgrade von SQL Server, ohne den Datenbank-Kompatibilitätsgrad zu ändern: Dadurch erhalten Sie zwar nicht Zugriff auf die neuesten Änderungen des Abfrageoptimierers, doch es werden neuere SQL Server-Features, wie der Abfragespeicher, bereitgestellt.

2. Aktivieren Sie den Abfragedatenspeicher. Weitere Informationen finden Sie unter Dauerhafte Anpassung des Abfragespeichers an Ihre Workload.

3. Erlauben Sie es dem Abfragespeicher, Abfragen und Pläne abzufangen, und legen Sie eine Baseline der Leistung mit dem Kompatibilitätsgrad der Quelle oder der vorherigen Datenbank fest. Bleiben Sie ausreichend lang in diesem Schritt, um alle Pläne zu erfassen und eine stabile Baseline zu erstellen. Dabei kann es sich um die Dauer eines üblichen Geschäftszyklus für eine Produktionsworkload handeln.

4. Umstieg auf den neuesten Datenbank-Kompatibilitätsgrad: Machen Sie Ihre Workload mit der neuesten Version des Abfrageoptimierers verfügbar, um neue Pläne zu erstellen.

5. Verwenden des Abfragespeichers für die Analyse und Reparatur zurückgestellter Abfragen: In der Regel führen die neuen Änderungen des Abfrageoptimierers zu besseren Plänen. Jedoch verfügen Sie in Form des Abfragespeichers über eine einfache Möglichkeit, Planauswahlregressionen durchzuführen und falsche Entscheidungen mithilfe des Mechanismus zum Durchsetzen von Plänen zu korrigieren. Ab SQL Server 2017 (14.x) wird bei Verwendung des Features Automatische Plankorrektur dieser Schritt automatisch durchgeführt.

   a. Erzwingen Sie für alle Fälle, in denen Regressionen auftreten, den zuvor bekannten geeigneten Plan im Abfragespeicher.

   b. Falls Abfragepläne nicht erzwungen werden können oder die Leistung weiterhin unzureichend ist, ziehen Sie in Betracht, die vorherige Einstellung des Datenbank-Kompatibilitätsgrads wiederherzustellen und sich anschließend an den Microsoft-Kundensupport zu wenden.

Erkennen und Verbessern von Ad-hoc-Workloads

Einige Workloads weisen keine besonders häufig ausgeführten Abfragen auf, die Sie zur Verbesserung der Gesamtleistung einer Anwendung optimieren können. Diese Workloads zeichnen sich normalerweise durch eine relativ große Anzahl verschiedener Abfragen aus, von denen jede einen Teil der Systemressourcen beansprucht. Aufgrund ihrer Einzigartigkeit werden solche Abfragen nur sehr selten ausgeführt (normalerweise nur einmal, daher die Bezeichnung „ad-hoc“), daher ist ihr Ressourcenverbrauch zur Laufzeit nicht kritisch. Da andererseits die Anwendung unterm Strich ständig neue Abfragen generiert, wird ein erheblicher Teil der Systemressourcen für die Kompilierung von Abfragen aufgewendet, was nicht optimal ist. Das ist auch für den Abfragespeicher nicht ideal, da die große Anzahl von Abfragen und Plänen den vorgesehenen Speicherplatz schnell erschöpft und der Abfragespeicher auf diese Weise sehr bald in den schreibgeschützten Modus versetzt werden muss. Wenn Sie die Richtlinie zur größenbasierten Bereinigung (dringend empfohlen , um den Abfragespeicher stets betriebsbereit zu halten) aktiviert haben, bereinigen Hintergrundprozesse während des größten Teils der Zeit die Strukturen des Abfragespeichers, was ebenfalls in erheblichem Maß Systemressourcen verbraucht.

Die Ansicht Abfragen mit dem höchstem Ressourcenverbrauch gibt Ihnen einen ersten Hinweis auf die Ad-hoc-Natur Ihrer Workload:

Verwenden Sie die Metrik Ausführungsanzahl , um zu analysieren, ob Ihre Abfragen mit dem höchsten Ressourcenverbrauch Ad-hoc-Abfragen sind (dafür müssen Sie den Abfragespeicher mit QUERY_CAPTURE_MODE = ALLausführen). Im Diagramm oben können Sie sehen, dass 90% der Abfragen mit dem höchstem Ressourcenverbrauch nur einmal ausgeführt werden.

Alternativ können Sie ein Transact-SQL-Skript ausführen, um die Gesamtzahl der Abfragetexte, Abfragen und Pläne im System abzurufen und deren Unterschiede durch den Vergleich von query_hash und query_plan_hash zu bestimmen:

--Do cardinality analysis when suspect on ad hoc workloads
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;  
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query;  
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;  

Dies ist ein potenzielles Ergebnis, das Sie bei Arbeitsauslastungen mit Ad-hoc-Abfragen erhalten können:

Das Abfrageergebnis zeigt, dass sich trotz der großen Anzahl von Abfragen und Plänen im Abfragespeicher query_hash und query_plan_hash nicht unterscheiden. Ein Verhältnis zwischen eindeutigen Abfragetexten und eindeutigen Abfragehashes, das erheblich größer als 1 ist, ist ein Hinweis, dass die Arbeitsauslastung einen geeigneten Kandidaten für Parametrisierung darstellt, da der einzige Unterschied zwischen den Abfragen eine literale Konstante (Parameter) ist, die als Teil des Abfragetexts übergeben wird.

Normalerweise tritt diese Situation ein, wenn Ihre Anwendung Abfragen erstellt (statt gespeicherte Prozeduren oder parametrisierte Abfragen aufzurufen) oder auf objektrelationalen Zuordnungsframeworks basiert, die standardmäßig Abfragen generieren.

Wenn der Anwendungscode in Ihre Zuständigkeit fällt, können Sie eine Neuerstellung der Datenzugriffsschicht in Erwägung ziehen, um gespeicherte Prozeduren oder parametrisierte Abfragen zu verwenden. Diese Situation lässt sich aber auch ohne Änderung der Anwendung erheblich verbessern, indem die Abfrageparametrisierung für die gesamte Datenbank (alle Abfragen) oder für die einzelnen Abfragevorlagen mit dem gleichen query_hash erzwungen wird.

Der Ansatz mit einzelnen Abfragevorlagen erfordert die Erstellung von Planhinweislisten:

--Apply plan guide for the selected query template 
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'<your query text goes here>',  
    @stmt OUTPUT,   
    @params OUTPUT;  
  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION (PARAMETERIZATION FORCED)';  

Die Lösung mit Planhinweislisten ist genauer, erfordert aber mehr Arbeit.

Wenn alle Ihre Abfragen (oder ein Großteil davon) für die automatische Parametrisierung geeignet sind, sollten Sie die Konfiguration von PARAMETERIZATION = FORCED für die gesamte Datenbank in Betracht ziehen. Weitere Informationen finden Sie unter Richtlinien für die Verwendung der erzwungenen Parametrisierung.

--Apply forced parameterization for entire database  
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;  

Nach dem Ausführen eines dieser Schritte zeichnet Abfragen mit höchstem Ressourcenverbrauch ein anderes Bild Ihrer Arbeitsauslastung.

In manchen Fällen generiert Ihre Anwendung möglicherweise viele verschiedene Abfragen, die keine geeigneten Kandidaten für automatische Parametrisierung darstellen. In diesem Fall findet sich eine große Anzahl Abfragen im System, das Verhältnis zwischen eindeutigen Abfragen und eindeutigem Abfragehash (query_hash) liegt aber wahrscheinlich nahe bei 1.

In diesem Fall kann es sinnvoll sein, die Serveroption Für Ad-hoc-Arbeitsauslastungen optimieren zu aktivieren, um die Verschwendung von Cachespeicher für Abfragen zu vermeiden, die wahrscheinlich nicht erneut ausgeführt werden. Um die Erfassung solcher Abfragen im Abfragespeicher zu verhindern, legen Sie QUERY_CAPTURE_MODE auf AUTOfest.

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO 
  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON   
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);  

Nächste Schritte

• Überwachen der Leistung mit dem Abfragespeicher
• Bewährte Methoden für den Abfragespeicher
• Upgraden von Datenbanken mit dem Abfrageoptimierungs-Assistenten