Query Store voor leesbare secundaire bestanden

Van toepassing op: SQL Server 2025 (17.x) Preview Azure SQL DatabaseAzure SQL Managed Instance

Query Store voor leesbare secundaire databases maakt Query Store-inzichten mogelijk voor workloads die worden uitgevoerd op secundaire replica's. Wanneer deze functie is ingeschakeld, streamen secundaire replica's informatie over de uitvoering van query's (zoals runtime- en wachtstatistieken) naar de primaire replica, waar de gegevens worden bewaard in Query Store en zichtbaar worden gemaakt voor alle replica's.

De functie is oorspronkelijk geïntroduceerd in SQL Server 2022 (16.x), maar deze is standaard uitgeschakeld en vereist een traceringsvlag om in te schakelen. Dit is deels te wijten omdat de functie een preview-status heeft voor SQL Server 2022 (16.x).

Vanaf SQL Server 2025 (17.x) Preview en Azure SQL Database is Query Store standaard ingeschakeld voor leesbare secundaire bestanden.

Query Store inschakelen voor leesbare secundaire bestanden

Voordat u Query Store gebruikt voor leesbare secundaire databases op een preview-exemplaar van SQL Server 2025 (17.x), moet een AlwaysOn-beschikbaarheidsgroep worden geconfigureerd.

Voor Azure SQL Database ondersteunt Query Store voor leesbare secundaire bestanden de volgende servicelagen:

• Algemeen gebruik met actieve geo-replicatie (geen ingebouwde replica's voor hoge beschikbaarheid; vereist configuratie van geo-replicatie voor secundaire ondersteuning)
• Premium (inclusief ingebouwde replica's voor hoge beschikbaarheid; actieve geo-replicatie wordt ook ondersteund)
• Bedrijfskritiek (inclusief ingebouwde replica's voor hoge beschikbaarheid; actieve geo-replicatie wordt ook ondersteund)
• Azure SQL Managed Instance met het beleid Always-up-to-date
  • Algemeen gebruik met een failovergroep
  • Bedrijfskritiek (inclusief ingebouwde replica's voor hoge beschikbaarheid)

Van toepassing op: SQL Server 2022 (16.x) en latere versies.

Als Query Store nog niet is ingeschakeld en in READ_WRITE de modus op de primaire replica staat, moet u deze inschakelen voordat u doorgaat. Voer het volgende script uit voor elke gewenste database op de primaire replica:

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

Als u Query Store wilt inschakelen voor alle leesbare secundaire bestanden, maakt u verbinding met de primaire replica en voert u het volgende script uit voor elke database die moet worden ingeschreven om de functie te gebruiken.

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

Automatische correctie van plannen inschakelen voor secundaire replica's

Van toepassing op: SQL Server 2022 (16.x) en latere versies, Azure SQL Database.

Nadat u Query Store voor secundaire replica's hebt ingeschakeld, kunt u desgewenst automatische optimalisatie inschakelen zodat de functie voor automatische correctie van plannen de plannen kan afdwingen voor secundaire replica's. Hierdoor kan de queryoptimalisatie automatisch problemen met queryprestaties identificeren en oplossen die worden veroorzaakt door regressies van het uitvoeringsplan op secundaire replica's.

Als u automatische correctie van plannen voor secundaire replica's wilt inschakelen, maakt u verbinding met de primaire replica en voert u het volgende script uit voor elke gewenste database:

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

Query Store uitschakelen voor secundaire replica's

Als u de functie Query Store voor secundaire replica's op alle secundaire replica's wilt uitschakelen, maakt u verbinding met de master database op de primary replica en voert u het volgende script uit voor elke gewenste database:

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

Valideren of Query Store aan staat op secundaire replica's

U kunt controleren of Query Store is ingeschakeld op een secondary replica door verbinding te maken met de database op de secundaire replica en de volgende t-sql-instructie uit te voeren:

SELECT desired_state_desc,
       actual_state_desc,
       readonly_reason
FROM sys.database_query_store_options;

De resultaten van het uitvoeren van query's op de sys.database_query_store_options catalogusweergave moeten aangeven dat de feitelijke status van de Query Store READ_CAPTURE_SECONDARY is met een readonly_reason van 8.

Opmerkingen

Terminologie

Een replicaset wordt gedefinieerd als een lees-/schrijfreplica van een database (primair) en een of meer alleen-lezen replica's (secundair) die worden behandeld als een logische eenheid. Een rol in deze context verwijst naar de rol van een specifieke replica. Wanneer een replica wordt gebruikt in de primaire rol, is dit de lees-/schrijfreplica die zowel gegevenswijzigingen als leesactiviteit kan uitvoeren. Wanneer een replica is geconfigureerd om alleen-lezen activiteit uit te voeren, vervult het een secundaire rol (secundair, geo-secundair, geo ha secundair). Rollen kunnen worden gewijzigd via geplande of niet-geplande failovergebeurtenissen. Wanneer dit gebeurt, kan een primaire een secundaire worden of omgekeerd.

De momenteel ondersteunde rollen zijn:

• Primary
• Secundair
• Geo secundaire
• Geo HA secundair
• Benoemde replica

Hoe het werkt

De gegevens die over query's zijn opgeslagen, kunnen op rollenbasis als workloads worden geanalyseerd. Query Store voor leesbare secundaire bestanden biedt u de mogelijkheid om de prestaties te bewaken van elke unieke, alleen-lezen workload die kan worden uitgevoerd op secundaire replica's. De gegevens worden geaggregeerd op rolniveau. Een configuratie van gedistribueerde SQL Server-beschikbaarheidsgroepen kan bijvoorbeeld bestaan uit:

• Eén primaire replica, onderdeel van beschikbaarheidsgroep 1 (AG1)

• Twee lokale secundaire replica's, ook onderdeel van AG1

• Eén externe primaire replica op een andere locatie die deel uitmaakt van een afzonderlijke beschikbaarheidsgroep (AG2). In SQL Server-termen wordt het ook vaak aangeduid als een globale doorstuurserver, maar de functie Query Store voor leesbare secundaire bestanden herkent en verwijst ernaar als een Geo secondary replica, ervan uitgaande dat het een geografisch gedistribueerde secundaire replica is.

Als AG1 en AG2 zodanig zijn geconfigureerd dat alleen-lezenverbindingen worden toegestaan wanneer een alleen-lezenworkload wordt uitgevoerd op een van de secundaire replica's van AG1, worden de uitvoeringsstatistieken van Query Store verzonden naar de primaire replica van AG1 en samengevoegd en bewaard als gegevens die zijn gegenereerd op basis van de secondary rol voordat die gegevens worden teruggestuurd naar alle secundaire replica's, inclusief de globale doorstuurserver in AG2. Wanneer een afzonderlijke workload tegen de primaire van AG2 wordt uitgevoerd, stuurt de globale forwarder de bijbehorende gegevens terug naar de primaire replica van AG1, en worden deze bewaard als gegevens die zijn gegenereerd vanuit de functie Geo secondary.

Vanuit het perspectief van waarneembaarheid wordt de weergave sys.query_store_runtime_stats systeemcatalogus uitgebreid om de rol te identificeren waar de uitvoeringsstatistieken van afkomstig zijn. Er is een relatie tussen deze weergave en de sys.query_store_replicas systeemcatalogusweergave, die een beschrijvendere naam van de rol kan bieden. In SQL Server, Azure SQL Database en Azure SQL Managed Instance is de kolom replica_name NULL. De kolom replica_name wordt echter ingevuld voor het Hyperscale-serviceniveau als er een benoemde replica aanwezig is en deze wordt gebruikt voor alleen-lezen workloads.

Een voorbeeld van een t-sql-query die kan worden gebruikt om een algemene analyse te bieden van de top 50 query's gedurende de afgelopen 8 uur, die CPU-resources van alle replica's verbruikt, zijn:

-- 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;

De Query Store-rapporten in SQL Server Management Studio (SSMS) 21 en nieuwere versies bieden een vervolgkeuzelijst Replica , die een manier biedt om Query Store-gegevens in verschillende replicasets/-rollen weer te geven. In de weergave Objectverkenner weerspiegelt het knooppunt Query Store ook de huidige status van Query Store (dat wil gezegd READ_CAPTURE) als deze is verbonden met een leesbare secundaire replica.

Query Store voor leesbare telemetriegegevens van secundaire databases in diagnostische instellingen van Azure

van toepassing op: Azure SQL Database

Bij het streamen van runtimestatistieken van Query Store via diagnostische azure-instellingen worden twee kolommen opgenomen om de replicabron van de telemetriegegevens te identificeren:

• is_primary_b: Een Booleaanse waarde die aangeeft of de gegevens afkomstig zijn van de primaire replica (waar) of een secundaire replica (onwaar)
• replica_group_id: Een geheel getal dat overeenkomt met de replicarol

Deze kolommen zijn essentieel voor het ondubbelzinnig maken van metrische gegevens en prestatiegegevens bij het analyseren van workloads in replicasets. Wanneer u de diagnostische instellingen configureert om runtimestatistieken van Query Store te streamen naar Log Analytics, Event Hubs of Azure Storage, moet u ervoor zorgen dat uw query's en dashboards rekening houden met deze kolommen om gegevens op basis van replicarol op de juiste manier te segmenteren. Zie Diagnostische instellingen in Azure Monitor voor meer informatie over het configureren van diagnostische instellingen en beschikbare metrische gegevens.

Prestatieoverwegingen voor Query Store voor leesbare secundaire bestanden

Het kanaal dat door secundaire replica's wordt gebruikt om querygegevens terug te sturen naar de primaire replica, is hetzelfde kanaal dat wordt gebruikt om secundaire replica's up-to-date te houden. Wat betekent channel hier?

In een HADR-configuratie (beschikbaarheidsgroep) worden replica's met elkaar gesynchroniseerd met behulp van een toegewezen transportlaag die logboekblokken, bevestigingen en statusberichten tussen de primaire en secundaire replica's bevat. Dit zorgt voor gegevensconsistentie en failovergereedheid.

Wanneer Query Store is ingeschakeld voor leesbare secundaire bestanden, wordt er geen afzonderlijk netwerkeindpunt gemaakt. In plaats daarvan wordt een nieuw logisch communicatiepad voor de bestaande transportlaag vastgesteld:

• Voor Azure SQL Database (niet-Hyperscale), Azure SQL Managed Instance en SQL Server maakt dit gebruik van de AlwaysOn-transportlaag voor hoge beschikbaarheid en herstel na noodgevallen (HADR).

• Voor Azure SQL Database Hyperscale wordt de transportlaag RbIo (Remote Blob I/O) gebruikt. Dit is het communicatiekanaal tussen de rekenknooppunten en de logservice-/paginaservers. RbIo biedt een betrouwbaar, versleuteld kanaal voor het verplaatsen van logboekrecords en gegevenspagina's.

Dit pad multiplexeert uitvoeringsgegevens van Query Store (querytekst, plannen, runtime- / wachtstatistieken) naast het normale logboekrecordverkeer, met dezelfde versleutelde sessie. De functie heeft een eigen opname- en ontvangstwachtrij, die vanuit het perspectief van een replica kan worden bekeken door een query uit te voeren op de sys.database_query_store_internal_state weergave:

SELECT pending_message_count,
       messaging_memory_used_mb
FROM sys.database_query_store_internal_state;

Gegevens van secundaire bestanden worden opgeslagen in dezelfde Query Store-tabellen op de primaire tabel, waardoor de opslagvereisten kunnen worden verhoogd. Bij zware belasting kunt u latentie of tegendruk op het transportkanaal observeren. Dezelfde beperkingen voor ad-hocquery's die van toepassing zijn op de Query Store op de primaire, zijn ook van toepassing op secundairen. Zie voor meer informatie en richtlijnen over het beheren van de grootte en vastleggingsbeleid van Query Store De meest relevante gegevens in Query Store behouden.

Negatieve zichtbaarheid van query-id/plan-id

Negatieve ID's geven tijdelijke aanduidingen in het geheugen aan voor query's/plannen op secundaire servers voordat ze worden opgeslagen naar de primaire server.

Voordat de Query Store-gegevens vanuit leesbare secundaire replica's worden bewaard naar de primair, kunnen aan query's en plannen tijdelijke ID's worden toegewezen in de lokale in-memory weergave van Query Store - de MEMORYCLERK_QUERYDISKSTORE_HASHMAP. De query- en plan-id's kunnen als negatieve getallen worden weergegeven en tijdelijke aanduidingen zijn totdat de primaire replica een gezaghebbende id toewijst, die plaatsvindt nadat Query Store heeft bepaald dat een query voldoet aan de geconfigureerde vereisten voor de capture-modus. Als er een aangepast opnamebeleid is ingesteld, kunt u de vereisten controleren waaraan moet worden voldaan door een query uit te voeren op de sys.database_query_store_options systeemcatalogusweergave.

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;

Zodra een query is aangewezen als vastgelegd, kunnen de runtime-/wachtstatistieken en het plan worden bewaard en worden de lokale tijdelijke id's vervangen door positieve id's. Hiermee kunt u ook plannen afdwingen of hintmogelijkheden gebruiken.

Verwante inhoud

• OPTIES VOOR ALTER DATABASE SET (Transact-SQL)
• sys.query_store_replicas
• sys.query_store_plan_forcing_locations (Transact-SQL)
• sys.sp_query_store_force_plan (Transact-SQL)
• Query Store-hints
• Query Store-gebruiksscenario's
• sys.database_query_store_options (Transact-SQL)
• Aanbevolen procedures voor het bewaken van workloads met Query Store
• Best practices voor het beheren van de Query Store
• Prestaties optimaliseren met de Query Store