Überwachen der Leistung mithilfe des Abfragespeichers

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics (nur dedizierter SQL-Pool)

Das Feature „Abfragespeicher“ bietet Einblicke in die Auswahl und Leistung von Abfrageplänen für SQL Server, Azure SQL-Datenbank, Azure SQL Managed Instance und Azure Synapse Analytics. Der Abfragespeicher ermöglicht das schnelle Auffinden von Leistungsabweichungen, die auf Änderungen an Abfrageplänen zurückzuführen sind, und vereinfacht so die Behandlung von Leistungsproblemen. Der Abfragespeicher erfasst automatisch einen Verlauf der Abfragen, Pläne und Laufzeitstatistiken und bewahrt diese zur Überprüfung auf. Es unterteilt die Daten nach Zeitfenstern, sodass Sie Verwendungsmuster für Datenbanken erkennen können und verstehen, wann Abfrageplanänderungen auf dem Server aufgetreten sind. Sie können den Abfragespeicher mit der Option ALTER DATABASE SET konfigurieren.

Wichtig

Wenn Sie den Abfragespeicher für Erkenntnisse zu Just-In-Time-Arbeitsauslastungen in SQL Server 2016 (13.x) verwenden, planen Sie baldmöglichst die Installation der Fixes zur Leistungsskalierbarkeit in KB 4340759 ein.

Aktivieren des Abfragespeichers

  • Der Abfragespeicher ist für neue Azure SQL-Datenbank-Instanzen und für neue Azure SQL Managed Instance-Datenbanken standardmäßig aktiviert.
  • Für SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x) ist der Abfragespeicher standardmäßig nicht aktiviert. Sie ist standardmäßig im READ_WRITE-Modus für neue Datenbanken ab SQL Server 2022 (16.x) aktiviert. Es empfiehlt sich, den Abfragespeicher für alle Datenbanken zu aktivieren, um Features zur besseren Nachverfolgung des Leistungsverlaufs, zur Behandlung von Problemen im Abfrageplan und zur Aktivierung neuer Funktionen in SQL Server 2022 (16.x) zu aktivieren.
  • Der Abfragespeicher ist für neue Azure Synapse Analytics-Datenbanken nicht standardmäßig aktiviert.

Verwenden der Seite „Abfragespeicher“ in SQL Server Management Studio

  1. Klicken Sie im Objekt-Explorer mit der rechten Maustaste auf eine Datenbank und anschließend auf Eigenschaften.

    Hinweis

    Erfordert mindestens Version 16 von Management Studio.

  2. Wählen Sie im Dialogfeld Datenbankeigenschaften die Seite Abfragespeicher aus.

  3. Wählen Sie im Feld Betriebsmodus (angefordert) die Option Lesen und schreiben aus.

Verwenden von Transact-SQL-Anweisungen

Verwenden Sie die ALTER DATABASE-Anweisung, um den Abfragespeicher für eine bestimmte Datenbank zu aktivieren. Zum Beispiel:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Aktivieren Sie in Azure Synapse Analytics beispielsweise den Abfragespeicher ohne zusätzliche Optionen:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

Weitere Syntaxoptionen im Zusammenhang mit dem Abfragespeicher finden Sie unter ALTER DATABASE SET-Optionen (Transact-SQL).

Hinweis

Der Abfragespeicher kann für die Datenbanken master oder tempdb nicht aktiviert werden.

Wichtig

Informationen zum Aktivieren des Abfragespeichers und dazu, wie Sie ihn an Ihre Arbeitsauslastung angepasst halten, finden Sie unter Bewährte Methoden für den Abfragespeicher.

Informationen im Abfragespeicher

Die Ausführungspläne für eine bestimmte Abfrage in SQL Server verändern sich i. Allg. im Laufe der Zeit aufgrund unterschiedlicher Ursachen wie z.B. statischer Änderungen, Schemaänderungen, des Erstellens/Löschens von Indizes usw. Der Prozedurcache (in dem zwischengespeicherte Abfragepläne gespeichert werden) speichert nur den letzten Ausführungsplan. Pläne werden auch bei Speicherplatzknappheit aus dem Plancache entfernt. Aus diesem Grund kann die Problembehandlung bei einer Regression der Abfrageleistung schwierig und zeitaufwendig sein.

Da der Abfragespeicher mehrere Ausführungspläne pro Abfrage beibehält, kann er über Richtlinien den Abfrageprozessor anweisen, für eine Abfrage einen bestimmten Ausführungsplan zu verwenden. Dies wird als Planerzwingung bezeichnet. Das Erzwingen eines Plans im Abfragespeicher erfolgt ähnlich wie beim Abfragehinweis USE PLAN , es erfordert jedoch keine Änderung an Benutzeranwendungen. Durch das Erzwingen eines Plans können Sie eine Regression der Abfrageleistung aufgrund einer Änderung des Plans in sehr kurzer Zeit beheben.

Hinweis

Der Abfragespeicher sammelt Pläne für DML-Anweisungen wie SELECT, INSERT, UPDATE, DELETE, MERGE und BULK INSERT.

Standardmäßig sammelt der Abfragespeicher keine Pläne für DDL-Anweisungen wie CREATE INDEX usw. Abfragespeicher erfasst den kumulierten Ressourcenverbrauch, indem Pläne für die zugrunde liegenden DML-Anweisungen erfasst werden. Beispielsweise können Abfragespeicher die SELECT- und INSERT-Anweisungen anzeigen, die intern ausgeführt werden, um einen neuen Index aufzufüllen.

Der Abfragespeicher sammelt standardmäßig keine Daten für systemintern kompilierte gespeicherte Prozeduren. Verwenden Sie sys.sp_xtp_control_query_exec_stats, um die Datensammlung für systemintern kompilierte gespeicherte Prozeduren zu aktivieren.

Durch Wartestatistiken erhalten Sie weitere Informationen, die Ihnen bei der Problembehandlung der Leistung in der Datenbank-Engine helfen können. Lange Zeit waren Wartestatistiken nur auf Instanzebene verfügbar, wodurch es schwierig war, sie einer bestimmten Abfrage zuzuordnen. Ab SQL Server 2017 (14.x) und Azure SQL-Datenbank enthält der Abfragespeicher eine Dimension, die Wartezeitstatistiken nachverfolgt. Im folgenden Beispiel wird der Abfragespeicher zum Sammeln von Wartezeitstatistiken aktiviert.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Häufige Szenarios für die Verwendung des Abfragespeichers:

  • Schnelles Auffinden und Beheben von Regressionen der Planleistung durch Erzwingung des vorherigen Abfrageplans Korrigieren von Abfragen, die in der Vergangenheit aufgrund von Änderungen am Ausführungsplan die Leistung vermindert haben
  • Bestimmen der Ausführungshäufigkeit einer Abfrage in einem festgelegten Zeitraum mit Unterstützung eines DBAs bei der Behandlung von Leistungsproblemen mit Ressourcen
  • Identifizieren der häufigsten n Abfragen (nach Ausführungszeit, Speicherverbrauch usw.) in den letzten x Stunden.
  • Überwachen des Verlaufs von Abfrageplänen für eine bestimmte Abfrage
  • Analysieren der Verwendungsmuster einer Ressource (CPU, E/A und Arbeitsspeicher) für eine bestimmte Datenbank
  • Identifizieren Sie Top-N-Abfragen, die auf den Ressourcen warten.
  • Erhalten Sie Einblick in die Wartedetails einer bestimmten Abfrage oder eines bestimmten Plans.

Der Abfragespeicher enthält drei Speicher:

  • einen Planspeicher, der die Informationen zum Ausführungsplan speichert
  • einen Speicher für Laufzeitstatistiken, der die Informationen zum Ausführungsstatistiken speichert
  • einen Speicher für Wartestatistiken, der die Informationen zum Wartestatistiken speichert

Die Anzahl der eindeutigen Pläne, die für eine Abfrage gespeichert werden können, wird durch die Konfigurationsoption max_plans_per_query begrenzt. Zum Verbessern der Leistung werden diese Informationen asynchron in die Speicher geschrieben. Um die Speicherverwendung zu minimieren, werden die statistischen Daten zur Laufzeitausführung im Speicher für Laufzeitstatistiken über ein festes Zeitintervall aggregiert. Die Informationen in diesen Speichern können durch Abfragen der Katalogsichten für den Abfragespeicher angezeigt werden.

Die folgende Abfrage gibt Informationen zu Abfragen, ihren Plänen, zu Kompilierzeit- und Laufzeitstatistiken aus dem Abfragespeicher zurück.

SELECT Txt.query_text_id, Txt.query_sql_text, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
    ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;

Abfragespeicher für sekundäre Replikate

Gilt für: SQL Server (ab SQL Server 2022 (16.x))

Mit dem Abfragespeicher für sekundäre Replikate können die Abfragespeicherfunktionen, die für primäre Replikate zur Verfügung stehen, auch für Workloads sekundärer Replikate verwendet werden. Wenn der Abfragespeicher für sekundäre Replikate aktiviert ist, senden Replikate die Abfrageausführungsinformationen, die normalerweise im Abfragespeicher gespeichert werden, an das primäre Replikat zurück. Das primäre Replikat speichert die Daten dann auf dem Datenträger in seinem eigenen Abfragespeicher. Im Wesentlichen gibt es also einen einzelnen Abfragespeicher, die vom primären Replikat und von allen sekundären Replikaten gemeinsam genutzt wird. Die Abfragespeicher befindet sich im primären Replikat und speichert Daten für alle Replikate an einem Ort.

Vollständige Informationen zu Abfragespeicher für sekundäre Replikate finden Sie unter Abfragespeicher für sekundäre Replikate der Always On-Verfügbarkeitsgruppe.

Verwenden der Funktion Rückläufige Abfragen

Aktualisieren Sie nach der Aktivierung des Abfragespeichers den Datenbankbereich im Objekt-Explorer-Bereich, um den Abschnitt Abfragespeicher hinzuzufügen.

Screenshot der Abfragespeicher-Berichtsbaumstruktur in SSMS-Objekt-Explorer.

Hinweis

Für Azure Synapse Analytics sind Sichten des Abfragedatenspeichers unter Systemsichten im Datenbankbereich des Objekt-Explorers verfügbar.

Wählen Sie Zurückgestellte Abfragen aus, um den Bereich Zurückgestellte Abfragen in SQL Server Management Studio zu öffnen. Im Bereich „Regressed Queries“ werden die Abfragen und Pläne im Abfragespeicher angezeigt. Verwenden Sie die Dropdownfelder im oberen Bereich, um Abfragen anhand verschiedener Kriterien zu filtern: Dauer (ms) (Standard), CPU-Zeit (ms), Logische Lesevorgänge (KB), Logische Schreibvorgänge (KB), Physische Lesevorgänge (KB), CLR-Zeit (ms), DOP, Arbeitsspeicherverbrauch (KB), Zeilenanzahl, Verwendeter Protokollspeicher (KB), Verwendeter temporärer DB-Speicher (KB) und Wartezeit (ms).

Wählen Sie einen Plan aus, um die grafische Darstellung des Abfrageplans anzuzeigen. Schaltflächen stehen zur Verfügung, um die Quellabfrage anzuzeigen, einen Abfrageplan zu erzwingen bzw. seine Erzwingung aufzuheben, zwischen Raster- und Diagrammformaten umzuschalten, ausgewählte Pläne zu vergleichen (wenn mehrere Pläne ausgewählt sind) und die Anzeige zu aktualisieren.

Screenshot des SQL Server Bericht über Zurückgestellte Abfragen im Objekt-Explorer in SSMS.

Um einen Plan zu erzwingen, wählen Sie eine Abfrage und einen Plan aus, und klicken Sie anschließend auf Plan erzwingen. Sie können nur Pläne erzwingen, die mit dem Abfrageplanfeature gespeichert wurden und sich noch im Abfrageplancache befinden.

Suchen nach wartenden Abfragen

Ab SQL Server 2017 (14.x) und in der Azure SQL-Datenbank stehen Wartestatistiken pro Abfrage über einen bestimmten Zeitraum im Abfragespeicher zur Verfügung.

Im Abfragespeicher werden Wartetypen in Wartekategorien zusammengefasst. Die Zuordnung von Wartekategorien zu Wartetypen finden Sie unter sys.query_store_wait_stats (Transact-SQL).

Wählen Sie Abfragewartestatistiken aus, um den Bereich Abfragewartestatistiken in SQL Server Management Studio 18.0 oder neueren Versionen zu öffnen. Der Bereich „Abfragewartestatistiken“ zeigt ein Balkendiagramm mit den wichtigsten Wartekategorien im Abfragespeicher an. Verwenden Sie die Dropdownliste im oberen Bereich, um ein Aggregatkriterium für die Wartezeit auszuwählen: avg, max, min, std dev oder total (Standard).

Screenshot des SQL Server Bericht der Statistik der Abfragewartezeit im Objekt-Explorer in SSMS.

Wählen Sie eine Wartekategorie aus, indem Sie auf die Leiste klicken. Eine Detailansicht der ausgewählten Wartekategorie wird angezeigt. Dieses neue Balkendiagramm enthält die Abfragen, die zu dieser Wartekategorie beigetragen haben.

Screenshot des SQL Server Detailansicht der Statistik der Abfragewartezeit im Objekt-Explorer in SSMS.

Verwenden Sie die Dropdownfelder im oberen Bereich, um Abfragen nach verschiedenen Wartezeitkriterien für die ausgewählte Wartekategorie zu filtern: avg, max, min, std dev oder total (Standard). Wählen Sie einen Plan aus, um die grafische Darstellung des Abfrageplans anzuzeigen. Über verschiedene Schaltflächen können Sie die Quellabfrage anzeigen, einen Abfrageplan erzwingen und die Erzwingung wieder aufheben und die Ansicht aktualisieren.

Wartekategorien fassen mehrere Wartetypen in Buckets zusammen, die sich in ihrer Art ähneln. Verschiedene Wartekategorien erfordern verschiedene Analysen zur Problembehebung. Wartetypen aus der gleichen Kategorien führen jedoch zu sehr ähnlichen Problembehebungsvorgängen. Wenn nun die betroffenen Abfrage in den Wartezuständen bereitgestellt wird, können die meisten Überprüfungen erfolgreich abgeschlossen werden.

Im folgenden finden Sie einige Beispiele, wie Sie ausführlicheren Einblick in Ihre Workload erhalten, bevor oder nachdem Wartekategorien im Abfragespeicher eingefügt wurden:

Frühere Erfahrung Neue Erfahrung Aktion
Lange Wartezustände von RESOURCE_SEMAPHORE pro Datenbank Lange Speicherwartezustände im Abfragespeicher für bestimmte Abfragen Suchen Sie die im Abfragespeicher die speicherintensivsten Abfragen. Diese Abfragen verzögern wahrscheinlich zusätzlich den Fortschritt der betroffen Abfragen. Ziehen Sie in Betracht, den Abfragehinweis „MAX_GRANT_PERCENT“ für diese Abfragen oder für die betroffene Abfrage zu verwenden.
Lange Wartezustände von LCK_M_X pro Datenbank Lange Sperrwartezustände im Abfragespeicher für bestimmte Abfragen Überprüfen Sie die Abfragetexte der betroffenen Abfragen, und identifizieren Sie die Zielentitäten. Suchen Sie im Abfragespeicher nach anderen Abfragen, die die gleiche Entität modifizieren und die häufig ausgeführt werden bzw. oder eine lange Dauer haben. Nachdem Sie diese Abfragen ermittelt haben, ändern Sie ggf. die Anwendungslogik, um die Parallelität zu verbessern, oder verwenden Sie eine weniger restriktive Isolationsstufe.
Lange Wartezustände von PAGEIOLATCH_SH pro Datenbank Lange Wartezustände der Puffer-E/A im Abfragespeicher für bestimmte Abfragen Suchen Sie die Abfragen mit einer hohen Anzahl an physischen Lesevorgängen im Abfragespeicher. Wenn Sie mit den Abfragen mit langen E/A-Wartezuständen übereinstimmen, denken Sie darüber nach, einen Index auf der zugrunde liegenden Entität einzufügen, damit Suchvorgänge statt Scanvorgängen durchgeführt werden und damit der E/A-Aufwand der Abfragen gesenkt wird.
Lange Wartezustände von SOS_SCHEDULER_YIELD pro Datenbank Lange CPU-Wartezustände im Abfragespeicher für bestimmte Abfragen Machen Sie die Abfragen im Abfragespeicher ausfindig, die am meisten CPU nutzen. Bestimmen Sie dann, welche dieser Abfragen sowohl eine hohe CPU-Auslastung als auch lange CPU-Wartezustände für die betroffenen Abfragen aufweisen. Konzentrieren Sie sich darauf, diese Abfragen zu optimieren: möglicherweise gibt es eine Planregression, oder es fehlt ein Index.

Konfigurationsoptionen

Weitere Informationen zu den verfügbaren Konfigurationsoptionen für Abfragespeicherparameter finden Sie unter ALTER DATABASE SET-Optionen (Transact-SQL).

Fragen Sie die Sicht sys.database_query_store_options ab, um die aktuellen Optionen des Abfragespeichers zu ermitteln. Weitere Informationen zu den Werten finden Sie unter sys.database_query_store_options.

Beispiele für das Festlegen der Konfigurationsoptionen mit Transact-SQL-Anweisungen finden Sie unter Optionsverwaltung.

Hinweis

Für Azure Synapse Analytics kann der Abfragespeicher wie auf anderen Plattformen auch aktiviert werden, aber zusätzliche Konfigurationsoptionen werden nicht unterstützt.

Überprüfen und verwalten Sie den Abfragespeicher mit Management Studio oder mithilfe der folgenden Sichten und Prozeduren.

Funktionen des Abfragespeichers

Funktionen unterstützen Sie bei den Vorgängen des Abfragespeichers.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

Katalogsichten des Abfragespeichers

Katalogsichten stellen Informationen über den Abfragespeicher bereit.

sys.database_query_store_options (Transact-SQL)

sys.query_context_settings (Transact-SQL)

sys.query_store_plan (Transact-SQL)

sys.query_store_query (Transact-SQL)

sys.query_store_query_text (Transact-SQL)

sys.query_store_runtime_stats (Transact-SQL)

sys.query_store_wait_stats (Transact-SQL)

sys.query_store_runtime_stats_interval (Transact-SQL)

sys.database_query_store_internal_state (Transact-SQL)

Gespeicherte Prozeduren für den Abfragespeicher

Gespeicherte Prozeduren ermöglichen das Konfigurieren des Abfragespeichers.

sp_query_store_flush_db (Transact-SQL)

sp_query_store_reset_exec_stats (Transact-SQL)

sp_query_store_force_plan (Transact-SQL)

sp_query_store_unforce_plan (Transact-SQL)

sp_query_store_remove_plan (Transact-SQL)

sp_query_store_remove_query (Transact-SQL)

sp_query_store_clear_message_queues (Transact-SQL)

sp_query_store_consistency_check (Transact-SQL)1

1 In extremen Szenarien kann der Abfragespeicher aufgrund interner Fehler in den Zustand FEHLER geraten. Falls dies eintritt, kann der Abfragespeicher ab SQL Server 2017 (14.x) wiederhergestellt werden, indem in der betroffenen Datenbank die gespeicherte Prozedur sp_query_store_consistency_check ausgeführt wird. Weitere Einzelheiten finden Sie unter sys.database_query_store_options in der Beschreibung der Spalte actual_state_desc.

Abfragespeicher-Wartung

Bewährte Methoden und Empfehlungen zur Wartung und Verwaltung des Abfragespeichers wurden in diesem Artikel erweitert: Bewährte Methoden für die Verwaltung der Abfragespeicher.

Leistungsüberwachung und Problembehandlung

Weitere Informationen zur Leistungsoptimierung mit dem Abfragespeicher finden Sie unter Optimieren der Leistung mit dem Abfragespeicher.

Weitere leistungsbezogene Themen:

Zugehöriger Inhalt