Überwachen Sie die Leistung mithilfe des Abfragespeicher

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

Die Funktion „Abfragespeicher“ bietet Ihnen Einblicke in die Auswahl von Abfrageplänen und deren Leistung für SQL Server, Azure SQL-Datenbank, Fabric 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 Abfragespeicher mithilfe der ALTER DATABASE SET Optionsoption konfigurieren.

Important

Wenn Sie den Abfragespeicher für Echtzeit-Arbeitslastanalysen in SQL Server 2016 (13.x) verwenden, planen Sie, die Leistungsskalierbarkeits-Fixes in KB 4340759 so schnell wie möglich zu installieren.

Aktivieren Sie den Abfragespeicher

  • Der Abfragespeicher ist für neue Azure SQL-Datenbank-Instanzen und für neue Azure SQL Managed Instance-Datenbanken standardmäßig aktiviert.
  • Der Abfragespeicher ist für SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x) standardmäßig nicht aktiviert. Sie ist standardmäßig im READ_WRITE Modus für neue Datenbanken aktiviert, beginnend mit SQL Server 2022 (16.x). 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 standardmäßig nicht 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.

    Note

    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 die Abfragespeicher für eine bestimmte Datenbank zu aktivieren. Beispiel:

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

Die Optionen zur Konfiguration des Abfragespeicher in der Fabric SQL-Datenbank mit ALTER DATABASE sind derzeit eingeschränkt.

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

Note

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

Important

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 bei Speicherdruck auch aus dem Plan-Cache verdrängt. Infolgedessen kann die Behebung von Regressionen der Abfrageleistung, die durch Änderungen am Ausführungsplan verursacht werden, komplex 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 von Plänen im Abfragespeicher wird mithilfe eines Mechanismus verwendet, der dem Abfragehinweis ähnelt, jedoch keine Änderung in Benutzeranwendungen erfordert. Das Erzwingen eines Plans kann eine durch eine Planänderung verursachte Leistungsregression einer Abfrage innerhalb sehr kurzer Zeit beheben.

Note

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 INDEXz. B. usw. Der 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 nativ 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 Szenarien für die Verwendung des Abfragespeicher-Features sind:

  • Schnelles Auffinden und Beheben von Regressionen der Planleistung durch Erzwingung des vorherigen Abfrageplans Beheben Sie Abfragen, deren Leistung sich kürzlich aufgrund von Änderungen an den Ausführungsplänen verschlechtert hat.
  • Ermittelt, wie oft eine Abfrage in einem bestimmten Zeitfenster ausgeführt wurde, und unterstützt einen DBA bei der Fehlerbehebung von Leistungs- und Ressourcenproblemen.
  • 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 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 zum Persistieren der Informationen zu Ausführungsstatistiken
  • einen Speicher für Wartezeitstatistiken zur dauerhaften Speicherung von Informationen zu Wartezeitstatistiken.

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 sind durch Abfrage der Katalogansichten des Abfragespeichers sichtbar.

Die folgende Abfrage gibt Informationen zu Abfragen, ihren Plänen sowie 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 2025 (17.x), Azure SQL-Datenbank

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 dauerhaft auf dem Datenträger in seinem eigenen Abfragespeicher. Im Wesentlichen gibt es einen Abfragespeicher, der zwischen den primären und 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.

Weitere Informationen finden Sie unter Abfragespeicher für sekundäre Replikate.

Verwenden Sie die Funktion „Regressierte Abfragen“

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

Screenshot des Abfragespeicher-Berichtsbaums im SSMS-Objekt-Explorer.

Note

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 „Regressierte Abfragen“ werden Ihnen 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 Berichts zu regressierten Abfragen von SQL Server im SSMS-Objekt-Explorer.

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 mithilfe der Abfrageplanfunktion 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 Berichts zu den Abfragewartestatistiken von SQL Server im SSMS-Objekt-Explorer.

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 der Detailansicht der SQL Server-Abfragewartestatistik im SSMS-Objekt-Explorer.

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 verschiedene Wartetypen zu Gruppen ähnlicher Art zusammen. Verschiedene Wartekategorien erfordern unterschiedliche Folgeanalysen, um das Problem zu beheben, doch Wartetypen aus derselben Kategorie führen zu sehr ähnlichen Troubleshooting-Abläufen, und die Bereitstellung der betroffenen Abfrage zusätzlich zu den Warteinformationen wäre das fehlende Puzzlestück, um die meisten solchen Untersuchungen erfolgreich abzuschließen.

Im Folgenden finden Sie einige Beispiele, wie Sie vor und nach der Einführung von Wartekategorien im Abfragespeicher mehr Einblicke in Ihre Workload gewinnen können:

Vorherige Erfahrung Neues Erlebnis Action
Hohe RESOURCE_SEMAPHORE-Wartezeiten pro Datenbank Hohe Speicherwartezeiten im Abfragespeicher für spezifische Abfragen Suchen Sie im Abfragespeicher nach den arbeitsspeicherintensivsten 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.
Hohe LCK_M_X-Wartezeiten je Datenbank Hohe Sperrwartezeiten 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.
Hohe PAGEIOLATCH_SH-Wartezeiten pro Datenbank Hoher Puffer IO wartet im Abfragespeicher auf bestimmte Abfragen Suchen Sie die Abfragen mit einer hohen Anzahl an physischen Lesevorgängen im Abfragespeicher. Wenn sie den Abfragen mit hohen IO Wartezeiten entsprechen, sollten Sie einen Index für die zugrunde liegende Entität einführen, um Suchvorgänge anstelle von Scans durchzuführen und so den IO Aufwand der Abfragen zu minimieren.
Hohe SOS_SCHEDULER_YIELD-Wartezeiten pro Datenbank Hohe CPU-Wartezeiten in Abfragespeicher für bestimmte Abfragen Ermitteln Sie im Abfragespeicher die Abfragen mit der höchsten CPU-Auslastung. Identifizieren Sie darunter die Abfragen, bei denen ein hoher CPU-Trend mit hohen CPU-Wartezeiten für die betroffenen Abfragen korreliert. Konzentrieren Sie sich darauf, diese Abfragen zu optimieren: möglicherweise gibt es eine Planregression, oder es fehlt ein Index.

Konfigurationsoptionen

Die verfügbaren Optionen zum Konfigurieren von Abfragespeicher Parametern finden Sie unter ALTER DATABASE SET "Optionen(Transact-SQL)".

Fragen Sie die Ansicht sys.database_query_store_options ab, um die aktuellen Optionen des Abfragespeicher 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.

Note

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

Zeigen Sie Abfragespeicher in Management Studio an, und verwalten Sie es über die folgenden Ansichten und Prozeduren.

Funktionen des Abfragespeichers

Funktionen helfen bei Vorgängen mit dem Abfragespeicher.

sys.fn_stmt_sql_handle_from_sql_stmt

Katalogansichten von Abfragespeicher

Katalogsichten enthalten Informationen zum Abfragespeicher.

sys.database_query_store_options

sys.query_context_settings

sys.query_store_plan

sys.query_store_query

sys.query_store_query_text

sys.query_store_runtime_stats

sys.query_store_wait_stats

sys.query_store_runtime_stats_interval

sys.database_query_store_internal_state

Gespeicherte Prozeduren im Abfragespeicher

Gespeicherte Prozeduren ermöglichen das Konfigurieren des Abfragespeichers.

sp_query_store_flush_db

sp_query_store_reset_exec_stats

sp_query_store_force_plan

sp_query_store_unforce_plan

sp_query_store_remove_plan

sp_query_store_remove_query

sp_query_store_clear_message_queues

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:

Verwandte Inhalte

  • Last updated on