Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Gilt für:
SQL Server 2016 (13.x) und höhere Versionen von Azure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse Analytics (nur dedizierter SQL-Pool)SQL-Datenbank in Microsoft Fabric
Der Abfragespeicher kann in einer Vielzahl von Szenarien eingesetzt werden, wenn die Nachverfolgung und Sicherstellung einer vorhersehbaren Workloadleistung entscheidend ist. Diese Beispiele dienen zur Veranschaulichung:
Identifizieren und Beheben von Abfragen mit Regressionen bei der Planauswahl
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.
Identifizieren und Beheben von Abfragen mit Regressionen bei der Planauswahl
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 können Sie schnell:
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.
Erzwingen Sie den vorherigen Plan aus dem Verlauf, wenn er sich als besser erwiesen hat. Verwenden Sie in Regressierte Abfragen die Schaltfläche Plan erzwingen, um den ausgewählten Plan für die Abfrage zu erzwingen.
Eine detaillierte Beschreibung des Szenarios finden Sie im Blog Abfragespeicher: 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 Abfragen, deren Leistung sich verschlechtert hat, oder solche, die sich durch zusätzliches Tuning verbessern lassen.
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:
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.
Ü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
Vergewissern Sie sich, dass die Statistiken für die zugrunde liegenden Tabellen aktuell sind, die von der Abfrage verwendet werden.
Überprüfen Sie, ob die von der Abfrage verwendeten Indizes defragmentiert sind.
Erwägen Sie, die kostenintensive Abfrage umzuschreiben. 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.
Das Hinzufügen einer systemseitigen temporalen Versionierung zu Tabellen, die häufig von Ihren OLTP-Anwendungen geändert werden.
Wenden Sie für jedes dieser Szenarien den folgenden Arbeitsablauf an:
Führen Sie Ihre Workload vor der geplanten Änderung mit dem Abfragespeicher aus, um eine Leistungsbasis zu erstellen.
Wenden Sie die Anwendungsänderung zum vorgesehen Zeitpunkt an.
Führen Sie die Arbeitsauslastung danach für einen ausreichend langen Zeitraum aus, um ein Leistungsbild des Systems nach der Änderung zu erstellen
Vergleichen Sie die Ergebnisse von Nr. 1 und Nr. 3.
Öffnen Sie Datenbank Gesamtverbrauch, um den Einfluss auf die gesamte Datenbank zu bestimmen.
Ö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.
Entscheiden Sie, ob die Änderung beibehalten oder ein Rollback für den Fall ausgeführt werden soll, dass die neue Leistung nicht akzeptabel ist.
Die folgende Abbildung zeigt die Abfragespeicher-Analyse (Schritt 4) im Fall eines fehlenden Indexes. Öffnen Sie den Bereich Abfragen mit dem höchsten Ressourcenverbrauch / „Planzusammenfassung“, um diese Ansicht für die Abfrage anzuzeigen, die von der Erstellung des Index betroffen sein sollte:
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.
Beibehalten der Leistungsstabilität während des Upgrades
Abfrageoptimiereränderungen sind an die neueste Datenbankkompatibilitätsstufe gebunden, sodass Pläne nicht direkt zum Zeitpunkt des Upgrades geändert werden, sondern wenn ein Benutzer die COMPATIBILITY_LEVEL neueste Datenbank ändert. 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:
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.
Aktivieren Sie den Abfragespeicher. Weitere Informationen finden Sie unter Dauerhafte Anpassung des Abfragespeichers an Ihre Workload.
Erlauben Sie Abfragespeicher, Abfragen und Pläne zu erfassen, und erstellen Sie eine Leistungsbaseline mit der Datenbankkompatibilitätsstufe der Quell- bzw. vorherigen Datenbank. 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.
Wechseln Sie zur neuesten Datenbank-Kompatibilitätsstufe: Setzen Sie Ihre Workload dem neuesten Abfrageoptimierer aus, um potenziell neue Ausführungspläne zu erzeugen.
Verwenden Sie den Abfragespeicher für Analysen und die Behebung von Regressionen: In der Regel sollten die neuen Verbesserungen im Abfrageoptimierer zu besseren Plänen führen. Der Abfragespeicher bietet jedoch eine einfache Möglichkeit, Regressionen bei der Planauswahl zu erkennen und sie mithilfe eines Mechanismus zur Planerzwingung zu beheben. Ab SQL Server 2017 (14.x) wird bei Verwendung des Features Automatische Plankorrektur dieser Schritt automatisch durchgeführt.
a. Erzwingen Sie in Fällen, in denen Regressionen auftreten, den zuvor als gut bekannten Plan in Abfragespeicher.
b. Falls es Abfragepläne gibt, die sich nicht erzwingen lassen, oder die Leistung weiterhin unzureichend ist, ziehen Sie in Betracht, den Datenbank-Kompatibilitätsgrad auf die vorherige Einstellung zurückzusetzen und sich anschließend an den Microsoft-Kundensupport zu wenden.
Tip
Verwenden Sie den Task Datenbankupgrade ausführen in SQL Server Management Studio, um ein Upgrade für den Datenbank-Kompatibilitätsgrad der Datenbank auszuführen. Ausführliche Informationen finden Sie unter Upgraden von Datenbanken mit dem Abfrageoptimierungs-Assistenten.
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. Dies ist auch für den Abfragespeicher keine ideale Situation, da die große Anzahl von Abfragen und Plänen den dafür reservierten Speicherplatz schnell füllt, was bedeutet, dass der Abfragespeicher wahrscheinlich sehr schnell in den schreibgeschützten Modus wechselt. 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 query_hash und query_plan_hash sich trotz der großen Anzahl von Abfragen und Plänen im Abfragespeicher eigentlich 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 Planleitfäden:
--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;
Nachdem Sie einen dieser Schritte angewendet haben, zeigt Abfragen mit höchstem Ressourcenverbrauch ein anderes Bild Ihrer Workload.
In manchen Fällen generiert Ihre Anwendung möglicherweise viele verschiedene Abfragen, die keine geeigneten Kandidaten für automatische Parametrisierung darstellen. In diesem Fall gibt es eine große Anzahl von Abfragen im System, aber das Verhältnis zwischen einzigartigen Abfragen und einzigartigen query_hash liegt 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);