Query Store per le repliche secondarie leggibili (in anteprima)

Applica a: SQL Server 2022 (16.x) e versioni successive database SQL di AzureIstanza gestita di SQL di Azure

Query Store per le repliche secondarie leggibili consente di ottenere approfondimenti sui carichi di lavoro eseguiti nelle repliche secondarie. Se abilitata, le repliche secondarie eseguono il flusso di informazioni sull'esecuzione delle query ,ad esempio le statistiche di runtime e di attesa, nella replica primaria, in cui i dati vengono salvati in modo permanente in Query Store e resi visibili in tutte le repliche.

Availability

Query Store per le repliche secondarie leggibili è disponibile a partire da SQL Server 2025 (17.x), nonché database SQL di Azure e Istanza gestita di SQL di Azure con Always-up-to-date update policy. Per SQL Server 2022 (16.x), Query Store per le repliche secondarie leggibili richiede l'abilitazione del flag di traccia 12606 per l'uso della funzionalità. Per le versioni precedenti di SQL Server e Istanza gestita di SQL di Azure con altri criteri di aggiornamento, Query Store per le repliche secondarie leggibili non è disponibile.

La tabella seguente riepiloga la disponibilità e lo stato attivato del query store per le secondarie leggibili.

¹ L'archivio delle query per le repliche secondarie leggibili non è attualmente disponibile nel livello di servizio Hyperscale di database SQL di Azure.
² Query Store per i database secondari leggibili rimane in anteprima per SQL Server 2022 (16.x) e quindi non è supportato in ambiente di produzione, ed è disabilitato per impostazione predefinita. Per abilitare Query Store solo per i database secondari leggibili in SQL Server 2022 (16.x), è necessario abilitare un flag di traccia 12606 per le repliche secondarie primarie e leggibili. Il flag di tracciamento 12606 non è destinato alle distribuzioni di produzione basate su SQL Server 2022 (16.x). Per altre informazioni, vedere note sulla versione SQL Server 2022.

Scenari di disponibilità elevata supportati

• Prima di usare Query Store per le repliche secondarie leggibili in un'istanza di SQL Server 2025 (17.x), è necessario configurare un gruppo di disponibilità Always On.

• Per database SQL di Azure, Query Store per le repliche secondarie leggibili supporta i livelli di servizio seguenti:

  • Utilizzo generico con replica geografica attiva o configurazione di un gruppo di failover (nessuna replica a disponibilità elevata predefinita; richiede la replica geografica o la configurazione del gruppo di failover per il supporto secondario)
  • Premium (include repliche a disponibilità elevata predefinite; è supportata anche la replica geografica attiva o i gruppi di failover)
  • Business critical (include repliche a disponibilità elevata predefinite; è supportata anche la replica geografica attiva o i gruppi di failover)

• Per Istanza gestita di SQL di Azure con i criteri Always-up-to-date, Query Store per le repliche secondarie leggibili supporta i livelli di servizio seguenti:

  • Utilizzo generico con un gruppo di failover (nessuna replica a disponibilità elevata predefinita; richiede una configurazione del gruppo di failover per il supporto secondario)
  • Business critical (include repliche integrate ad alta disponibilità)

Abilitare Query Store per le repliche secondarie leggibili

Se Query Store non è già abilitato e in modalità READ_WRITE nella replica primaria, è necessario abilitarlo prima di procedere. Eseguire lo script seguente per ogni database desiderato nella replica primaria:

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

Per abilitare la Query Store in tutte le repliche secondarie leggibili, connettersi alla replica primaria ed eseguire lo script seguente per ogni database da integrare per usare la funzionalità.

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

Abilitare la correzione automatica dei piani per le repliche secondarie

Applica a: SQL Server 2022 (16.x) e versioni successive, database SQL di Azure.

Dopo aver abilitato Query Store per le repliche secondarie, è possibile abilitare l'ottimizzazione automatica dei piani per consentire alla funzionalità di correzione automatica dei piani di forzare i piani sulle repliche secondarie. Ciò consente a Query Optimizer di identificare e correggere automaticamente i problemi di prestazioni delle query causati dalle regressioni del piano di esecuzione nelle repliche secondarie.

Per abilitare la correzione automatica dei piani per le repliche secondarie, connettersi alla replica primaria ed eseguire lo script seguente per ogni database desiderato:

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

Disabilitare Query Store per le repliche secondarie

Per disabilitare la funzionalità di Query Store per le repliche secondarie in tutte le repliche secondarie, connettersi al database master nella replica primary ed eseguire lo script seguente per ogni database desiderato:

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

Verificare che Query Store sia abilitato nelle repliche secondarie

È possibile verificare che Query Store sia abilitato in una replica secondary connettendosi al database nella replica secondaria ed eseguire l'istruzione T-SQL seguente:

SELECT desired_state_desc,
       actual_state_desc,
       readonly_reason
FROM sys.database_query_store_options;

I risultati dell'interrogazione della vista del catalogo sys.database_query_store_options dovrebbero indicare che lo stato effettivo del Query Store è READ_CAPTURE_SECONDARY con un readonly_reason di 8.

Osservazioni:

Terminologia

Un set di repliche viene definito come replica di lettura/scrittura di un database (primario) e una o più repliche di sola lettura (secondarie) considerate come un'unità logica. Un ruolo in questo contesto fa riferimento al ruolo di una replica specifica. Quando una replica viene utilizzata nel ruolo primario, è la replica di lettura/scrittura che può eseguire sia modifiche ai dati che attività di lettura. Quando una replica è configurata per eseguire solo operazioni di sola lettura, viene utilizzata in un ruolo secondario (secondario, secondario geografico, secondario geo ha). I ruoli possono cambiare tramite eventi di failover pianificati o non pianificati, quando ciò accade, un primario può diventare secondario o viceversa.

I ruoli attualmente supportati sono:

• Primario
• Secondary
• Replicazione geografica secondaria
• Secondario Geo HA
• Replica denominata

Come funziona

I dati archiviati sulle query possono essere analizzati come carichi di lavoro in base al ruolo. Query Store per le repliche secondarie leggibili consente di monitorare le prestazioni di qualsiasi carico di lavoro specifico di sola lettura che potrebbe essere in esecuzione su repliche secondarie. I dati vengono aggregati a livello di ruolo. Ad esempio, una configurazione SQL Server distributed availability groups può essere costituita da:

• Una replica primaria, parte del gruppo di disponibilità 1 (AG1)

• Due repliche secondarie locali, anche parte AG1

• Una replica primaria remota in un'altra posizione che fa parte di un gruppo di disponibilità separato (AG2). In SQL Server termini, viene anche comunemente definito server d'inoltro globale, ma la funzionalità di Query Store per le repliche secondarie leggibili riconoscerà e farà riferimento a essa come replica Geo secondary, presupponendo che si tratti di una replica secondaria distribuita geograficamente.

Se AG1 e AG2 sono configurati per consentire connessioni di sola lettura quando un carico di lavoro di sola lettura viene eseguito su una delle repliche secondarie di AG1, le statistiche di esecuzione Query Store vengono inviate alla replica primaria di AG1 e aggregate e rese persistenti come dati generati dal ruolo secondary prima che tali dati vengano inviati a tutte le repliche secondarie, incluso il server d'inoltro globale in AG2. Quando un carico di lavoro separato viene eseguito sulla replica primaria di AG2, il global forwarder invia i dati alla replica primaria di AG1, dove vengono resi persistenti come dati generati dal ruolo Geo secondary.

Dal punto di vista dell'osservabilità, la vista del catalogo di sistema sys.query_store_runtime_stats viene estesa per identificare il ruolo da cui provengono le statistiche di esecuzione. Esiste una relazione tra questa vista e la vista del catalogo di sistema sys.query_store_replicas , che può fornire un nome più descrittivo del ruolo. In SQL Server la colonna replica_name è NULL. Tuttavia, la replica_name colonna viene popolata per il livello di servizio Hyperscale se è presente una replica denominata e viene usata per i carichi di lavoro di sola lettura.

Un esempio di query T-SQL che può essere usata per fornire un'analisi complessiva delle prime 50 query nelle ultime 8 ore, che utilizzava risorse CPU da tutte le repliche sarebbe:

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

I report del Query Store in SQL Server Management Studio (SSMS) 21 e versioni successive forniscono un elenco a discesa 'Replica', che consente di visualizzare i dati del Query Store attraverso vari set di repliche/ruoli. Inoltre, all'interno della visualizzazione Object explorer, il nodo Query Store riflette lo stato corrente di Query Store (ovvero READ_CAPTURE) se connesso a una replica secondaria leggibile.

Query Store per i dati di telemetria delle repliche secondarie leggibili in database SQL di Azure

Applies to: database SQL di Azure

Quando si esegue lo streaming delle statistiche runtime di Query Store tramite le impostazioni di diagnostica di Azure, vengono incluse due colonne per identificare l'origine di replica dei dati di telemetria.

• is_primary_b: valore booleano che indica se i dati hanno avuto origine dalla replica primaria (true) o da una replica secondaria (false)
• replica_group_id: numero intero che corrisponde al ruolo della replica

Queste colonne sono essenziali per disambiguare le metriche e i dati sulle prestazioni durante l'analisi dei carichi di lavoro tra set di repliche. Quando si configurano le impostazioni di diagnostica per lo streaming delle statistiche di runtime di Query Store verso Log Analytics, Event Hubs o Archiviazione di Azure, assicurarsi che le query e i dashboard considerino queste colonne per segmentare correttamente i dati per ruolo di replica. Per altre informazioni sulla configurazione delle impostazioni di diagnostica e sulle metriche disponibili, vedere Impostazioni di diagnostica in Monitoraggio di Azure.

Considerazioni sulle prestazioni per il Query Store per le repliche secondarie di sola lettura

Il canale usato dalle repliche secondarie per inviare informazioni sulle query alla replica primaria è lo stesso canale usato per mantenere aggiornate le repliche secondarie. channel Cosa significa qui?

In una configurazione del gruppo di disponibilità (HADR) le repliche vengono sincronizzate tra loro usando un livello di trasporto dedicato che contiene blocchi di log, riconoscimenti e messaggi di stato tra le repliche primarie e secondarie. In questo modo si garantisce la coerenza dei dati e l'idoneità del failover.

Quando Query Store per le repliche secondarie leggibili è abilitata, non crea un endpoint di rete separato. Stabilisce invece un nuovo percorso di comunicazione logico sul livello di trasporto esistente:

• Per database SQL di Azure (non-Hyperscale), Istanza gestita di SQL di Azure e SQL Server, questo utilizza il livello di trasporto Always On per l'alta disponibilità e il ripristino di emergenza.

• Per database SQL di Azure Hyperscale, viene usato un livello di trasporto diverso denominato livello di trasporto I/O BLOB remoto. Il livello di trasporto I/O BLOB remoto è il canale di comunicazione tra i nodi di calcolo e i server del servizio di log/pagine. Il livello di trasporto I/O BLOB remoto fornisce un canale affidabile e crittografato per lo spostamento di record di log e pagine di dati.

Questo percorso multiplexa i dati di esecuzione del Query Store (testo di query, piani, statistiche di runtime/attesa) insieme al normale traffico dei record di log, utilizzando la stessa sessione crittografata. La funzionalità ha una propria coda di acquisizione e ricezione, che può essere visualizzata eseguendo una query sulla sys.database_query_store_internal_state vista dal punto di vista di qualsiasi replica:

SELECT pending_message_count,
       messaging_memory_used_mb
FROM sys.database_query_store_internal_state;

I dati delle repliche secondarie vengono resi persistenti nelle stesse tabelle Query Store nel database primario, che possono aumentare i requisiti di archiviazione. Con carico elevato, è possibile osservare la latenza o la pressione posteriore sul canale di trasporto. Le stesse limitazioni dell'acquisizione di query ad hoc che si applicano alle Query Store sul database primario si applicano anche alle repliche secondarie. Per altre informazioni e indicazioni sulla gestione dei criteri di acquisizione e delle dimensioni di Query Store, vedere Mantenere i dati più rilevanti in Query Store.

Visibilità dell'ID query/ID del piano negativo

Gli ID negativi indicano segnaposto temporanei in memoria per query/piani nelle repliche secondarie prima della persistenza nel database primario.

Prima che i dati Query Store vengano salvati in modo permanente nella replica primaria da repliche secondarie leggibili, è possibile assegnare identificatori temporanei e piani all'interno della rappresentazione locale in memoria di Query Store, ovvero il MEMORYCLERK_QUERYDISKSTORE_HASHMAP. Gli ID di query e piano possono essere visualizzati come numeri negativi e sono segnaposto finché la replica primaria non assegna un identificatore autorevole, che si verifica dopo che Query Store determina che una query soddisfa i requisiti della modalità di acquisizione configurata. Se sono presenti criteri di acquisizione personalizzati , è possibile esaminare i requisiti che devono essere soddisfatti eseguendo una query sulla vista del sys.database_query_store_options catalogo di sistema.

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;

Dopo che una query è identificata come catturata, le sue statistiche di tempo di esecuzione/attesa e il piano possono essere conservati, e gli ID temporanei locali vengono sostituiti con ID positivi. In questo modo è anche possibile usare le funzionalità di forzatura o di hinting dei piani.

Contenuti correlati

• opzioni 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)
• Suggerimenti Query Store
• Query Store Scenari di utilizzo
• sys.database_query_store_options (Transact-SQL)
• Le migliori pratiche per il monitoraggio dei carichi di lavoro con Query Store
• Migliori pratiche per la gestione del Query Store
• Ottimizza le prestazioni con il Query Store