Freigeben über

Abfragespeicher für lesbare Secondärdateien

Gilt für: SQL Server 2025 (17.x) Azure SQL-Datenbank

Der Abfragespeicher für lesbare Secondärdateien ermöglicht Abfragespeichereinblicke für Workloads, die auf sekundären Replikaten ausgeführt werden. Wenn diese Option aktiviert ist, werden datenstromausführungsinformationen für sekundäre Replikate (z. B. Laufzeit- und Wartezeitstatistiken) an das primäre Replikat übertragen, in dem die Daten im Abfragespeicher gespeichert und für alle Replikate sichtbar gemacht werden.

Plattformunterstützung

Derzeit ist der Abfragespeicher für lesbare Secondärdateien verfügbar und wird in der Produktion in SQL Server 2025 (17.x) und in Azure SQL-Datenbank unterstützt. Ab SQL Server 2025 (17.x) und in Azure SQL-Datenbank ist der Abfragespeicher für lesbare Secondaries standardmäßig aktiviert.

In SQL Server 2022 (16.x) verbleibt der Abfragespeicher für lesbare Secondaries in der Vorschau und wird daher in der Produktion nicht unterstützt und standardmäßig deaktiviert. Um Query Store nur für lesbare sekundäre Replikate in SQL Server 2022 (16.x) zu aktivieren, muss das Trace-Flag 12606 auf dem primären und allen lesbaren sekundären Replikaten aktiviert werden. Trace-Flag 12606 ist nicht für produktive Bereitstellungen vorgesehen, die auf SQL Server 2022 (16.x) basieren. Weitere Informationen finden Sie in den Versionshinweisen zu SQL Server 2022. Für SQL Server 2025 (17.x) ist das Feature "Abfragespeicher für lesbare Secondaries" standardmäßig aktiviert .

Azure SQL-Datenbank, alle Datenbanken werden automatisch registriert und aktiviert, um den Abfragespeicher für lesbare Secondaries-Feature, für unterstützte Dienstebenen und Hochverfügbarkeitsszenarien zu unterstützen. Derzeit wird dieses Feature in Azure SQL-Datenbank Hyperscale nicht unterstützt.

Derzeit wird dieses Feature in der verwalteten Azure SQL-Instanz oder SQL-Datenbank in Microsoft Fabric nicht unterstützt.

Unterstützte Szenarien für hohe Verfügbarkeit

  • Bevor Sie den Abfragespeicher für lesbare Secondärdateien in einer SQL Server 2025 (17.x)-Instanz verwenden, muss eine AlwaysOn-Verfügbarkeitsgruppe konfiguriert werden.

  • Für die Azure SQL-Datenbank unterstützt der Abfragespeicher für lesbare Secondaries die folgenden Dienstebenen:

    • Allgemeiner Zweck mit aktiver Georeplikation (keine integrierten Replikate mit hoher Verfügbarkeit; erfordert georeplikationskonfiguration für sekundäre Unterstützung)
    • Premium (umfasst integrierte Hochverfügbarkeitsreplikate; aktive Georeplikation wird ebenfalls unterstützt)
    • Geschäftskritisch (umfasst integrierte Hochverfügbarkeitsreplikate; aktive Georeplikation wird ebenfalls unterstützt)

Aktivieren des Abfragespeichers für lesbare Secondärdateien

Wenn der Abfragespeicher noch nicht aktiviert ist und sich im READ_WRITE Modus des primären Replikats befindet, müssen Sie ihn aktivieren, bevor Sie fortfahren. Führen Sie das folgende Skript für jede gewünschte Datenbank im primären Replikat aus:

ALTER DATABASE [Database_Name]
    SET QUERY_STORE = ON(OPERATION_MODE = READ_WRITE);

Um den Abfragespeicher für alle lesbaren Secondärdateien zu aktivieren, stellen Sie eine Verbindung mit dem primären Replikat her, und führen Sie das folgende Skript für jede Datenbank aus, die für die Verwendung des Features aufgelistet werden soll.

ALTER DATABASE [Database_Name]
    FOR SECONDARY
    SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE);

Aktivieren Sie die automatische Planungskorrektur für sekundäre Replikas

Gilt für: SQL Server 2022 (16.x) und höhere Versionen, Azure SQL Database.

Nachdem Sie die Query Store-Funktion für sekundäre Replikate aktiviert haben, können Sie optional automatisches Tuning aktivieren, sodass die Plankorrekturfunktion Pläne für sekundäre Replikate festlegen kann. Auf diese Weise kann der Abfrageoptimierer Abfrageleistungsprobleme, die durch Regressionen des Ausführungsplans für sekundäre Replikate verursacht werden, automatisch identifizieren und beheben.

Um die automatische Plankorrektur für sekundäre Replikate zu aktivieren, stellen Sie eine Verbindung mit dem primären Replikat her, und führen Sie für jede gewünschte Datenbank das folgende Skript aus:

ALTER DATABASE [Database_Name]
FOR SECONDARY
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Abfragespeicher für sekundäre Replikate deaktivieren

Um das Feature "Abfragespeicher für sekundäre Replikate" für alle sekundären Replikate zu deaktivieren, stellen Sie eine Verbindung mit der master Datenbank im primary Replikat her, und führen Sie das folgende Skript für jede gewünschte Datenbank aus:

ALTER DATABASE [Database_Name]
    FOR SECONDARY
    SET QUERY_STORE = ON
    (OPERATION_MODE = READ_ONLY);

Validierung, dass der Abfragespeicher auf sekundären Replikaten aktiviert ist

Sie können überprüfen, ob der Abfragespeicher für ein secondary Replikat aktiviert ist, indem Sie eine Verbindung mit der Datenbank im sekundären Replikat herstellen und die folgende T-SQL-Anweisung ausführen:

SELECT desired_state_desc,
       actual_state_desc,
       readonly_reason
FROM sys.database_query_store_options;

Die Ergebnisse aus der Abfrage der sys.database_query_store_options Katalogansicht sollten angeben, dass der Abfragespeicher tatsächlich den Zustand READ_CAPTURE_SECONDARY hat, mit einem readonly_reason von 8.

desired_state_desc actual_state_desc readonly_reason
READ_CAPTURE_SECONDARY READ_CAPTURE_SECONDARY 8

Bemerkungen

Terminologie

Ein Replikatsatz wird als Lese-/Schreibreplikat einer Datenbank (primär) und mindestens ein schreibgeschütztes Replikat (sekundär) definiert, das als logische Einheit behandelt wird. Eine Rolle in diesem Kontext bezieht sich auf die Rolle eines bestimmten Replikats. Wenn ein Replikat in der primären Rolle dient, ist es das Lese-/Schreibreplikat, das sowohl Datenänderungen als auch Leseaktivitäten ausführen kann. Wenn ein Replikat so konfiguriert ist, dass nur schreibgeschützte Aktivitäten ausgeführt werden, wird es in einer sekundären Rolle (sekundär, geo sekundär, geo ha sekundär) bedient. Rollen können sich durch ein geplantes oder ungeplantes Failoverereignis ändern, wenn dies geschieht, kann ein primärer zu einem sekundären oder umgekehrt werden.

Die derzeit unterstützten Rollen sind:

  • Primary
  • Secondary
  • Sekundäre Geo-Region
  • Geo HA sekundär
  • Benanntes Replikat

Funktionsweise

Die über Abfragen gespeicherten Daten können als Workloads auf Rollenbasis analysiert werden. Der Abfragespeicher für lesbare Secondärdateien ermöglicht es Ihnen, die Leistung einer eindeutigen, schreibgeschützten Workload zu überwachen, die möglicherweise für sekundäre Replikate ausgeführt wird. Die Daten werden auf Rollenebene aggregiert. Eine Konfiguration mit verteilten SQL Server-Verfügbarkeitsgruppen kann z. B. aus:

  • Ein primäres Replikat, Teil der Verfügbarkeitsgruppe 1 (AG1)

  • Zwei lokale sekundäre Replikas, auch Teil von AG1

  • Ein primäres Remote-Replikat an einem anderen Standort, das Teil einer separaten Verfügbarkeitsgruppe (AG2) ist. In SQL Server-Begriffen wird es häufig als globaler Weiterleiter bezeichnet. Die Funktion 'Abfragespeicher für lesbare sekundäre Replikate' erkennt es jedoch als Geo secondary Replikat, vorausgesetzt, es handelt sich um ein geografisch verteiltes sekundäres Replikat.

Wenn AG1 und AG2 so konfiguriert sind, dass schreibgeschützte Verbindungen zugelassen werden, wird bei der Ausführung einer schreibgeschützten Workload auf einem der sekundären Repliken von AG1 die Ausführungsstatistik des Abfragespeichers an das primäre Replikat von AG1 gesendet. Diese Daten werden dann zu aggregierten und gespeicherten Daten zusammengefasst, die aus der secondary Rolle erzeugt wurden, bevor sie an alle sekundären Repliken einschließlich des globalen Forwarders in AG2 zurückgesendet werden. Wenn eine separate Workload gegen die Primärkopie von AG2 ausgeführt wird, wird der globale Forwarder an das primäre Replikat von AG1 gesendet und als Daten gespeichert, die aus der Rolle Geo secondary generiert wurden.

Aus Sicht der Observability wird die sys.query_store_runtime_stats Systemkatalogansicht erweitert, um die Rolle zu identifizieren, aus der die Ausführungsstatistiken stammen. Es gibt eine Beziehung zwischen dieser Ansicht und der sys.query_store_replicas Systemkatalogansicht, die einen aussagekräftigeren Namen der Rolle bereitstellen kann. In SQL Server ist die replica_name-Spalte NULL. Die Spalte wird jedoch für die Hyperscale-Dienstebene aufgefüllt, wenn ein benanntes Replikat vorhanden ist und für schreibgeschützte Workloads genutzt wird replica_name.

Ein Beispiel für eine T-SQL-Abfrage, die verwendet werden könnte, um eine Gesamtanalyse der top 50 Abfragen über die letzten 8 Stunden bereitzustellen, die CPU-Ressourcen aus allen Replikaten verbraucht haben, wäre:

-- Top 50 queries by CPU across all replicas in the last 8 hours
DECLARE @hours AS INT = 8;

SELECT TOP 50 qsq.query_id,
              qsp.plan_id,
              CASE qrs.replica_group_id WHEN 1 THEN 'PRIMARY' WHEN 2 THEN 'SECONDARY' WHEN 3 THEN 'GEO SECONDARY' WHEN 4 THEN 'GEO HA SECONDARY' ELSE CONCAT('NAMED REPLICA_', qrs.replica_group_id) END AS replica_type,
              qsq.query_hash,
              qsp.query_plan_hash,
              SUM(qrs.count_executions) AS sum_executions,
              SUM(qrs.count_executions * qrs.avg_logical_io_reads) AS total_logical_reads,
              SUM(qrs.count_executions * qrs.avg_cpu_time / 1000.0) AS total_cpu_ms,
              AVG(qrs.avg_logical_io_reads) AS avg_logical_io_reads,
              AVG(qrs.avg_cpu_time / 1000.0) AS avg_cpu_ms,
              ROUND(TRY_CAST (SUM(qrs.avg_duration * qrs.count_executions) AS FLOAT) / NULLIF (SUM(qrs.count_executions), 0) * 0.001, 2) AS avg_duration_ms,
              COUNT(DISTINCT qsp.plan_id) AS number_of_distinct_plans,
              qsqt.query_sql_text
FROM sys.query_store_runtime_stats_interval AS qsrsi
     INNER JOIN sys.query_store_runtime_stats AS qrs
         ON qrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id
     INNER JOIN sys.query_store_plan AS qsp
         ON qsp.plan_id = qrs.plan_id
     INNER JOIN sys.query_store_query AS qsq
         ON qsq.query_id = qsp.query_id
     INNER JOIN sys.query_store_query_text AS qsqt
         ON qsq.query_text_id = qsqt.query_text_id
WHERE qsrsi.start_time >= DATEADD(HOUR, -@hours, GETUTCDATE())
GROUP BY qsq.query_id, qsq.query_hash, qsp.query_plan_hash, qsp.plan_id, qrs.replica_group_id, qsqt.query_sql_text
ORDER BY SUM(qrs.count_executions * qrs.avg_cpu_time / 1000.0) DESC, AVG(qrs.avg_cpu_time / 1000.0) DESC;

Die Abfragespeicherberichte in SQL Server Management Studio (SSMS) 21 und höheren Versionen bieten eine Dropdownliste für Replikate , die eine Möglichkeit zum Anzeigen von Abfragespeicherdaten in verschiedenen Replikatgruppen/Rollen bietet. Darüber hinaus spiegelt der Knoten des Abfragespeichers innerhalb der Objekt-Explorer-Ansicht den aktuellen Status des Abfragespeichers wider (d. h. READ_CAPTURE_SECONDARY), wenn eine Verbindung mit einem lesbaren sekundären Replikat besteht.

Abfragespeicher für lesbare Secondaries-Telemetrie in Azure SQL-Datenbank

gilt für: Azure SQL-Datenbank

Beim Streamen von Abfragespeicher-Laufzeitstatistiken über Azure-Diagnoseeinstellungen sind zwei Spalten enthalten, um die Replikatquelle der Telemetriedaten zu identifizieren:

  • is_primary_b: Ein boolescher Wert, der angibt, ob die Daten aus dem primären Replikat (true) oder einem sekundären Replikat (false) stammen.
  • replica_group_id: Eine ganze Zahl, die der Replikatrolle entspricht

Diese Spalten sind für die Eindeutigkeit von Metriken und Performance-Daten bei der Analyse von Workloads über Replikatsätze hinweg unerlässlich. Stellen Sie beim Konfigurieren von Diagnoseeinstellungen zum Streamen von Abfragespeicher-Laufzeitstatistiken in Log Analytics, Event Hubs oder Azure Storage sicher, dass Ihre Abfragen und Dashboards diese Spalten verwenden, um die Daten ordnungsgemäß nach Replikatrolle zu segmentieren. Weitere Informationen zum Konfigurieren von Diagnoseeinstellungen und verfügbaren Metriken finden Sie unter Diagnoseeinstellungen in Azure Monitor.

Von Bedeutung

Die Abfrageleistungserblicke für Azure SQL-Datenbank (QPI)does not unterstützen derzeit das replica_group_id Konzept. Die im Dashboard angezeigten Daten aggregieren sämtliche Laufzeit- und Wartezeitstatistiken aus allen Replikaten.

Überlegungen zur Leistung für den Abfragespeicher für lesbare Secondärdateien

Der Kanal, der von sekundären Replikaten zum Senden von Abfrageinformationen an das primäre Replikat verwendet wird, ist derselbe Kanal, der verwendet wird, um sekundäre Replikate auf dem neuesten Stand zu halten. Was bedeutet hier channel ?

In einer HADR-Konfiguration (Availability Group) werden Replikate mit einer dedizierten Transportebene synchronisiert, die Protokollblöcke, Bestätigungen und Statusmeldungen zwischen den primären und sekundären Replikaten enthält. Dadurch wird die Datenkonsistenz und die Failoverbereitschaft sichergestellt.

Wenn der Abfragespeicher für lesbare Secondärdateien aktiviert ist, wird kein separater Netzwerkendpunkt erstellt. Stattdessen wird über die vorhandene Transportschicht ein neuer logischer Kommunikationspfad eingerichtet:

  • Für Azure SQL-Datenbank (nicht Hyperscale), azure SQL Managed Instance und SQL Server verwendet dies die Hohe Verfügbarkeit und Notfallwiederherstellung (HADR) Always On-Transportschicht.

  • Für Azure SQL-Datenbank Hyperscale wird eine andere Transportschicht namens Remote Blob I/O-Transportschicht verwendet. Die Remote-BLOB-E/A-Transportschicht ist der Kommunikationskanal zwischen den Computeknoten und den Protokolldienst-/Seitenservern. Die Remote-BLOB-E/A-Transportebene bietet einen zuverlässigen, verschlüsselten Kanal für die Übertragung von Protokolldatensätzen und Datenseiten.

Dieser Pfad multiplexiert Abfragespeicher-Ausführungsdaten (Abfragetext, Pläne, Laufzeit-/Wartezeitstatistiken) in den normalen Protokolldatenverkehr in derselben verschlüsselten Sitzung. Das Feature verfügt über eine eigene Erfassungs- und Empfangswarteschlange, die durch Abfragen der sys.database_query_store_internal_state Ansicht aus der Perspektive eines replikats angezeigt werden kann:

SELECT pending_message_count,
       messaging_memory_used_mb
FROM sys.database_query_store_internal_state;

Daten aus Secondärdateien werden in den gleichen Abfragespeichertabellen auf der primären Seite beibehalten, wodurch die Speicheranforderungen erhöht werden können. Unter hoher Last können Sie Latenz oder Rückdruck im Transportkanal beobachten. Die gleichen Einschränkungen für die Ad-hoc-Abfrageerfassung, die für den Abfragespeicher der primären Anwendung gelten, gelten auch für Secondaries. Weitere Informationen und Anleitungen zum Verwalten von Abfragespeichergrößen- und Erfassungsrichtlinien finden Sie unter "Beibehalten der relevantesten Daten im Abfragespeicher".

Negative Sichtbarkeit der Abfrage-ID/Plan-ID

Negative IDs geben temporäre In-Memory-Platzhalter für Abfragen und Ausführungspläne auf sekundären Systemen an, bevor sie zum primären System übertragen werden.

Bevor Abfragespeicherdaten aus lesbaren sekundären Replikaten im primären Speicher gesichert werden, könnten Abfragen und Pläne temporäre Bezeichner innerhalb des lokalen Speicherabbilds des Abfragespeichers zugewiesen bekommen – dem MEMORYCLERK_QUERYDISKSTORE_HASHMAP. Die Abfrage- und Plan-IDs können als negative Zahlen erscheinen und dienen als Platzhalter, bis das primäre Replikat einen autorisierten Bezeichner zuweist. Dies geschieht, nachdem der Abfragespeicher festgestellt hat, dass eine Abfrage die Anforderungen des konfigurierten Aufnahmemodus erfüllt. Wenn eine benutzerdefinierte Erfassungsrichtlinie vorhanden ist, können Sie die Anforderungen überprüfen, die erfüllt werden müssen, indem Sie die sys.database_query_store_options Systemkatalogansicht abfragen.

SELECT query_capture_mode_desc,
       capture_policy_execution_count,
       capture_policy_total_compile_cpu_time_ms,
       capture_policy_total_execution_cpu_time_ms
FROM sys.database_query_store_options;

Sobald eine Abfrage als erfasst festgelegt wurde, können die Laufzeit-/Wartezeitstatistiken und der Plan beibehalten werden, und die lokalen temporären IDs werden durch positive IDs ersetzt. Auf diese Weise können Sie auch planweise Erzwingungs- oder Hinweisfunktionen verwenden.

Verwandte Inhalte

  • Last updated on