Erkunden des Abfragespeichers
Der SQL Server-Abfragespeicher ist ein datenbankbezogenes Feature, das automatisch einen Verlauf von Abfragen, Plänen und Laufzeitstatistiken erfasst, um die Problembehandlung hinsichtlich der Leistung und die Optimierung von Abfragen zu vereinfachen. Außerdem bietet er Einblicke hinsichtlich der Verwendungsmuster der Datenbank und des Ressourcenverbrauchs.
Der Abfragespeicher besteht aus drei Speichern:
- Planspeicher: Speichert geschätzte Informationen zum Ausführungsplan.
- Laufzeitstatistikspeicher: Speichert Informationen zur Ausführungsstatistik.
- Speicher mit Wartezeitstatistiken: Bewahrt Informationen zu Wartezeitstatistiken auf.
Aktivieren des Abfragespeichers
Der Abfragespeicher ist in Azure SQL-Datenbanken standardmäßig aktiviert. Wenn Sie ihn mit SQL Server und Azure Synapse Analytics verwenden möchten, müssen Sie ihn zunächst aktivieren. Um das Feature „Abfragespeicher“ zu aktivieren, verwenden Sie die folgende, für Ihre Umgebung gültige Abfrage:
-- SQL Server
ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
-- Azure Synapse Analytics
ALTER DATABASE <database_name> SET QUERY_STORE = ON;
Erfassen von Daten im Abfragespeicher
Der Abfragespeicher ist in mehreren Phasen in die Abfrageverarbeitungspipeline integriert. Bei jedem Integrationspunkt werden die Daten im Speicher gesammelt und asynchron auf den Datenträger geschrieben, um den E/A-Aufwand zu minimieren. Die Integrationspunkte sind wie folgt:
Wenn eine Abfrage zum ersten Mal ausgeführt wird, werden der Abfragetext und der erste geschätzte Ausführungsplan an den Abfragespeicher gesendet und dort gespeichert.
Der Plan wird im Abfragespeicher aktualisiert, wenn eine Abfrage erneut kompiliert wird. Wenn die erneute Kompilierung zu einem neu generierten Ausführungsplan führt, bleibt dieser auch im Abfragespeicher erhalten, um die vorherigen Pläne zu ergänzen. Darüber hinaus verfolgt der Abfragespeicher zu Vergleichszwecken die Ausführungsstatistiken für jeden Abfrageplan nach.
Während der Kompilierung und der Prüfung auf erneute Kompilierung stellt der Abfragespeicher fest, ob es einen erzwungenen Plan für die auszuführende Abfrage gibt. Die Abfrage wird erneut kompiliert, wenn der Abfragespeicher einen erzwungenen Plan liefert, der sich von dem Plan im Prozedurcache unterscheidet.
Wenn eine Abfrage ausgeführt wird, bleiben ihre Laufzeitstatistiken im Abfragespeicher erhalten. Der Abfragespeicher fasst diese Daten zusammen, um eine genaue Darstellung jedes Abfrageplans sicherzustellen.
Weitere Informationen dazu, wie der Abfragespeicher Daten sammelt, finden Sie unter "Sammeln von Daten durch den Abfragespeicher".
Gängige Szenarios
Der SQL Server-Abfragespeicher bietet wertvolle Erkenntnisse hinsichtlich der Leistung der Datenbank-Vorgänge. Zu den häufigen Szenarios gehören:
- Identifizieren und beheben Sie Leistungseinbrüche aufgrund einer minderwertigen Auswahl von Abfrageausführungsplänen.
- Identifizieren und optimieren Sie Abfragen mit dem höchsten Ressourcenverbrauch.
- Führen Sie A/B-Tests zur Bewertung der Auswirkungen von Änderungen an Datenbanken und Anwendungen durch.
- Stellen Sie die Leistungsstabilität nach SQL Server-Upgrades sicher.
- Ermitteln Sie die am häufigsten verwendeten Abfragen.
- Überwachen Sie den Verlauf von Abfrageplänen für eine Abfrage.
- Identifizieren und verbessern Sie ungeplante Workloads.
- Verstehen Sie die vorherrschenden Wartekategorien einer Datenbank und der beitragenden Abfragen und Pläne, die die Wartezeiten beeinflussen.
- Analysieren Sie die Verwendungsmuster der Datenbank im Laufe der Zeit in Bezug auf den Ressourcenverbrauch (CPU, E/A, Speicher).
Entdecken der Abfragespeichersichten
Sobald der Abfragespeicher für eine Datenbank aktiviert ist, ist der Abfragespeicherordner für die Datenbank im Objekt-Explorer sichtbar. Für Azure Synapse Analytics werden die Sichten des Abfragespeichers unter den Systemansichten angezeigt. Die Abfragespeichersichten bieten aggregierte, schnelle Einblicke in die Leistungsaspekte der SQL Server-Datenbank.
Zurückgestellte Abfragen
Die Leistung einer zurückgestellten Abfrage verschlechtert sich im Laufe der Zeit aufgrund von Änderungen des Ausführungsplans. Die geschätzten Ausführungspläne können sich aufgrund verschiedener Faktoren ändern, einschließlich Schemaänderungen, Statistikänderungen und Indexänderungen. Die Untersuchung des Prozedurcaches ist möglicherweise die erste Maßnahme, die Ihnen einfällt, aber dieser speichert nur den letzten Ausführungsplan für eine Abfrage, und Pläne können je nach Speicherbedarf des Systems gelöscht werden. Der Abfragespeicher behält jedoch mehrere Ausführungspläne für jede Abfrage bei, was die Flexibilität bietet, durch sogenannte Planerzwingung einen bestimmten Plan auszuwählen, um Leistungsregressionen von Abfragen, die durch Planänderungen verursacht werden, zu beheben.
Die Ansicht "Regressed Queries" kann Abfragen identifizieren, deren Ausführungsmetriken sich aufgrund von Änderungen im Ausführungsplan über einen bestimmten Zeitraum verschlechtert haben. Diese Ansicht ermöglicht die Filterung anhand der Auswahl einer Metrik (z. B. Dauer, CPU-Zeit, Zeilenzahl und mehr) und einer Statistik (Summe, Durchschnitt, Minimum, Maximum oder Standardabweichung). Anschließend listet sie die 25 wichtigsten zurückgestellten Abfragen basierend auf dem bereitgestellten Filter auf. Standardmäßig wird eine grafische Balkendiagrammansicht der Abfragen angezeigt, aber Sie können die Abfragen optional auch in einem Rasterformat anzeigen.
Der Planzusammenfassungsbereich zeigt die persistierten Abfragepläne an, die der Abfrage im Verlauf der Zeit zugeordnet sind, nachdem Sie eine Abfrage aus dem Abfragebereich oben links ausgewählt haben. Sie sehen einen grafischen Abfrageplan im unteren Bereich, wenn Sie einen Abfrageplan im Bereich „Planzusammenfassung“ auswählen. Es stehen sowohl im Bereich der Planzusammenfassung als auch im Bereich des grafischen Abfrageplans Schaltflächen auf der Symbolleiste zur Verfügung, mit denen Sie den ausgewählten Plan für die ausgewählte Abfrage erzwingen können. Die Struktur und das Verhalten dieses Bereichs werden in allen SQL-Abfragesichten einheitlich verwendet.
Alternativ können Sie auch die gespeicherte Prozedur sp_query_store_force_plan verwenden, um die Planerzwingung zu nutzen.
EXEC sp_query_store_force_plan @query_id=73, @plan_id=79
Ressourcenverbrauch gesamt
Die Ansicht " Gesamtressourcenverbrauch " ermöglicht die Analyse des Gesamtressourcenverbrauchs für mehrere Ausführungsmetriken (z. B. Ausführungsanzahl, Dauer, Wartezeit und mehr) für einen angegebenen Zeitrahmen. Die gerenderten Diagramme sind interaktiv. Wenn Sie ein Measure aus einem der Diagramme auswählen, wird eine Drillthrough-Ansicht mit den Abfragen, die mit dem gewählten Measure verbunden sind, auf einer neuen Registerkarte angezeigt.
In der Detailansicht finden Sie die 25 wichtigsten Abfragen von Ressourcenconsumern, die zu der ausgewählten Metrik beigetragen haben. Diese Detailansicht verwendet die konsistente Schnittstelle, die es ermöglicht, die zugehörigen Abfragen und ihre Details zu überprüfen, gespeicherte geschätzte Abfragepläne zu bewerten und optional die Planerzwingung zur Leistungsverbesserung zu verwenden. Diese Ansicht ist nützlich, wenn es zu Konflikten bei den Systemressourcen kommt, z. B. wenn die CPU-Auslastung die Kapazitätsgrenze erreicht.
Abfragen mit dem höchsten Ressourcenverbrauch
Die Ansicht " Top Resource Consuming Queries " ähnelt dem Detail-Drilldown der Ansicht "Gesamtressourcenverbrauch". Sie können auch eine Metrik und eine Statistik als Filter auswählen. Die angezeigten Abfragen sind jedoch die 25 wichtigsten Abfragen auf der Grundlage des gewählten Filters und Zeitrahmens.
Die Ansicht " Top Resource Consuming Queries " bietet den ersten Hinweis auf die ungeplante Art der Arbeitsauslastung, wenn ungeplante Workloads identifiziert und verbessert werden. In der folgenden Abbildung werden beispielsweise die Metrik "Ausführungsanzahl " und die Gesamtstatistik ausgewählt, um zu ermitteln, dass etwa 90% der wichtigsten ressourcenaufwendigen Abfragen nur einmal ausgeführt werden.
Abfragen mit erzwungenen Plänen
Die Ansicht "Abfragen mit erzwungenen Plänen " bietet einen schnellen Blick auf die Abfragen mit erzwungenen Abfrageplänen. Diese Ansicht wird relevant, wenn ein erzwungener Plan nicht mehr die erwartete Leistung erbringt und neu ausgewertet werden muss. Diese Ansicht bietet die Möglichkeit, alle gespeicherten geschätzten Ausführungspläne für eine ausgewählte Abfrage zu überprüfen und auf einfache Weise festzustellen, ob ein anderer Plan jetzt besser für die Leistung geeignet ist. Wenn dies der Fall ist, stehen auf der Symbolleiste Schaltflächen zur Verfügung, mit denen Sie die Erzwingung eines Plans bei Bedarf aufheben können.
Abfragen mit hoher Abweichung
Die Leistung der Abfrage kann je nach Ausführung variieren. Die Abfrage mit der Ansicht "Hohe Variation " enthält eine Analyse von Abfragen, die die höchste Variation oder Standardabweichung für eine ausgewählte Metrik aufweisen. Die Benutzeroberfläche entspricht den meisten Ansichten des Abfragespeichers und ermöglicht die Prüfung von Abfragedetails, die Auswertung des Ausführungsplans und optional das Erzwingen eines bestimmten Plans. Verwenden Sie diese Ansicht, um unvorhersehbare Abfragen auf ein konsistentes Leistungsmuster abzustimmen.
Statistik der Abfragewartezeit
In der Abfragewartestatistikansicht werden die aktivsten Wartekategorien für die Datenbank analysiert und ein Diagramm gerendert. Dieses Diagramm ist interaktiv. Wenn Sie eine Wartekategorie auswählen, erhalten Sie Details zu den Abfragen, die zur Wartezeitstatistik beitragen.
Die Benutzeroberfläche der Detailansicht entspricht ebenfalls den meisten Abfragespeicheransichten und ermöglicht die Prüfung von Abfragedetails, die Auswertung des Ausführungsplans und optional das Erzwingen eines bestimmten Plans. Diese Ansicht hilft bei der Identifizierung von Abfragen, die die Benutzererfahrung in allen Anwendungen betreffen.
Nachverfolgungsabfrage
Die Nachverfolgungsabfrageansicht ermöglicht die Analyse einer bestimmten Abfrage basierend auf einem eingegebenen Abfrage-ID-Wert. Nach der Ausführung stellt die Ansicht den vollständigen Ausführungsverlauf der Abfrage bereit. Ein Häkchen bei einer Ausführung bedeutet, dass ein erzwungener Plan verwendet wurde. Diese Ansicht kann Erkenntnisse hinsichtlich der Abfragen ergeben, z. B. solche mit erzwungenen Plänen, um zu überprüfen, ob die Abfrageleistung stabil bleibt.
Verwenden des Abfragespeichers zum Ermitteln von Abfragewartezeiten
Wenn sich die Leistung eines Systems verschlechtert, ist es sinnvoll, die Abfragewartestatistiken heranzuziehen, um möglicherweise eine Ursache zu finden. Neben der Identifizierung von Abfragen, die optimiert werden müssen, kann es auch Aufschluss über mögliche Upgrades der Infrastruktur geben, die von Vorteil wären.
Der SQL-Abfragespeicher stellt die Abfragewartestatistikansicht bereit, um Einblicke in die wichtigsten Wartekategorien für die Datenbank bereitzustellen. Derzeit gibt es 23 Wartekategorien.
Ein Balkendiagramm zeigt die wichtigsten Wartekategorien für die Datenbank an, wenn Sie die Ansicht „Statistik der Abfragewartezeit“ öffnen. Darüber hinaus können Sie über einen Filter auf der Symbolleiste des Bereichs „Wartekategorien“ die Wartezeitstatistiken auf der Grundlage der Gesamtwartezeit (Standard), der durchschnittlichen Wartezeit, der minimalen Wartezeit, der maximalen Wartezeit oder der Standardabweichung der Wartezeit berechnen lassen.
Wenn Sie eine Wartekategorie auswählen, erfolgt ein Drillthrough durch die Details der Abfragen, die zu dieser Wartekategorie beitragen. In dieser Ansicht können Sie einzelne Abfragen untersuchen, die die größte Wirkung haben. Sie können auf die gespeicherten geschätzten Ausführungspläne zugreifen, die im Bereich „Planzusammenfassung“ angezeigt werden, indem Sie eine Abfrage im Abfragebereich auswählen. Wenn Sie einen Abfrageplan aus dem Bereich „Planzusammenfassung“ auswählen, wird der grafische Abfrageplan im unteren Bereich angezeigt. In dieser Ansicht haben Sie die Möglichkeit, einen Abfrageplan für die Abfrage zu erzwingen oder aufzuheben, um die Leistung zu verbessern.
Automatische Plankorrektur
SQL Server 2017 und Azure SQL-Datenbank haben das Konzept der automatischen Plankorrektur eingeführt, bei dem die Daten im Abfragespeicher analysiert werden. Wenn Sie den Abfragespeicher mit einer Datenbank in SQL Server 2017 (oder höher) und in Azure SQL-Datenbank aktivieren, sucht die SQL Server-Engine nach Abfrageplanregressionen und bietet Empfehlungen. Diese Empfehlungen werden in der sys.dm_db_tuning_recommendations dynamischen Verwaltungsansicht (Dynamic Management View, DMV) angezeigt. Diese Empfehlungen enthalten T-SQL-Anweisungen, um einen Abfrageplan manuell zu erzwingen, wenn die Leistung zufriedenstellend war.
Wenn Sie mit diesen Empfehlungen vertraut sind, können Sie SQL Server so einrichten, dass Pläne automatisch erzwungen werden, wenn Regressionen auftreten. Aktivieren Sie die automatische Plankorrektur mithilfe von ALTER DATABASE und dem AUTOMATIC_TUNING-Argument.
Für Azure SQL-Datenbank können Sie die automatische Plankorrektur auch mithilfe der Optionen für die automatische Optimierung im Azure-Portal oder mithilfe der REST-APIs aktivieren. Empfehlungen zur automatischen Plankorrektur sind immer für jede Datenbank aktiviert, in der der Abfragespeicher aktiviert ist (Standardeinstellung für Azure SQL-Datenbank und Azure SQL Managed Instance). Die automatische Plankorrektur (FORCE_PLAN) ist für Azure SQL-Datenbank für neue Datenbanken standardmäßig aktiviert.