Condividi tramite

Spazio di archiviazione query per repliche secondarie leggibili

Si applica a: SQL Server 2025 (17.x) Azure SQL Database

Query Store per repliche secondarie leggibili abilita le informazioni dettagliate di Query Store per i carichi di lavoro eseguiti nelle repliche secondarie. Se abilitata, le repliche secondarie inseguono 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.

Supporto delle piattaforme

Attualmente, la funzionalità Query Store per repliche secondarie leggibili è disponibile e supportata nell'ambiente di produzione in SQL Server 2025 (17.x) e nel database SQL di Azure. A partire da SQL Server 2025 (17.x) e nel database SQL di Azure, il Query Store è abilitato per impostazione predefinita per le repliche secondarie leggibili.

In SQL Server 2022 (16.x), Query Store per repliche secondarie leggibili rimane in anteprima e pertanto non è supportato nell'ambiente di produzione ed è disabilitato per impostazione predefinita. Per abilitare Query Store per repliche secondarie leggibili solo in SQL Server 2022 (16.x), è necessario abilitare un flag di traccia 12606 per le repliche secondarie primarie e leggibili. Il flag di traccia 12606 non è destinato alle distribuzioni di produzione basate su SQL Server 2022 (16.x). Per altre informazioni, vedere note sulla versione di SQL Server 2022. Per SQL Server 2025 (17.x), la funzionalità Query Store nei database secondari leggibili è attivata per impostazione predefinita.

Database SQL di Azure, tutti i database vengono registrati automaticamente e abilitati per supportare la funzionalità Query Store per repliche secondarie leggibili, nei livelli di servizio supportati e negli scenari di disponibilità elevata. Attualmente, questa funzionalità non è supportata in Hyperscale del database SQL di Azure.

Attualmente, questa funzionalità non è supportata in Istanza gestita di SQL di Azure o nel database SQL in Microsoft Fabric.

Scenari di disponibilità elevata supportati

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

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

    • Utilizzo generico con replica geografica attiva (nessuna replica a disponibilità elevata predefinita; richiede la configurazione della replica geografica per il supporto secondario)
    • Premium (include repliche a disponibilità elevata predefinite; è supportata anche la replica geografica attiva)
    • Critico per il business (include repliche ad alta disponibilità integrate; è supportata anche la replica geografica attiva)

Abilitare Query Store per repliche secondarie leggibili

Se Query Store non è già abilitato ed impostato in modalità READ_WRITE sul 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 Query Store in tutti i database secondari 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

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

Dopo aver abilitato Query Store per le repliche secondarie, puoi abilitare l'ottimizzazione automatica per consentire alla funzione di correzione automatica dei piani di applicare i piani forzati 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à Query Store per repliche secondarie in tutte le repliche secondarie, connettersi al master database nella primary replica 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 secondary replica 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'esecuzione di query sulla vista del catalogo sys.database_query_store_options devono indicare che lo stato effettivo del Query Store è READ_CAPTURE_SECONDARY con un readonly_reason di 8.

desired_state_desc actual_state_desc readonly_reason
READ_CAPTURE_SECONDARY READ_CAPTURE_SECONDARY 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 repliche secondarie leggibili consente di monitorare le prestazioni di qualsiasi carico di lavoro univoco di sola lettura che potrebbe essere in esecuzione su repliche secondarie. I dati vengono aggregati a livello di ruolo. Ad esempio, una configurazione dei gruppi di disponibilità distribuiti di SQL Server 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 termini di SQL Server, viene anche comunemente definito server d'inoltro globale, ma la funzionalità Query Store per repliche secondarie leggibili riconoscerà e farà riferimento a essa come Geo secondary replica, presupponendo che si tratti di una replica secondaria distribuita geograficamente.

Se AG1 e AG2 sono configurati per consentire le 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 di Query Store vengono inviate alla replica primaria di AG1 e aggregate e mantenute come dati generati dal secondary ruolo 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 replica_name colonna è 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 di Query Store in SQL Server Management Studio (SSMS) 21 e versioni successive forniscono un elenco a discesa Replica , che consente di visualizzare i dati di Query Store in diversi set di repliche/ruoli. Inoltre, all'interno della visualizzazione Esplora oggetti, il nodo Query Store riflette lo stato corrente di Query Store ,ovvero READ_CAPTURE_SECONDARY, se connesso a una replica secondaria leggibile.

Archivio query per dati di telemetria secondari leggibili nel database SQL di Azure

Si applica a: Database SQL di Azure

Quando si esegue lo streaming delle statistiche di 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 trasmettere le statistiche di runtime di Query Store a Log Analytics, Event Hub 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.

Importante

Approfondimenti sulle prestazioni delle query per il database SQL di Azure (QPI)does not attualmente supportano il replica_group_id concetto. I dati visualizzati nel dashboard aggregheranno tutte le statistiche di runtime e di attesa da tutte le repliche.

Considerazioni sulle prestazioni di Query Store per le repliche secondarie leggibili

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 repliche secondarie leggibili è abilitato, non crea un endpoint di rete separato. Stabilisce invece un nuovo percorso di comunicazione logico sul livello di trasporto esistente:

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

  • Per Azure SQL Database Hyperscale viene utilizzato un livello di trasporto diverso, chiamato Remote Blob I/O transport layer. 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 di Query Store (testo della query, piani, statistiche di runtime/attesa) insieme al normale traffico dei record di log, usando 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 dei database secondari vengono salvati in modo permanente nelle stesse tabelle di Query Store nel database primario, che può aumentare i requisiti di archiviazione. Con carico elevato, è possibile osservare la latenza o la pressione posteriore sul canale di trasporto. Le stesse limitazioni di acquisizione di query ad hoc applicabili a Query Store sul database primario si applicano anche ai database secondari. Per altre informazioni e indicazioni sulla gestione delle dimensioni e dei criteri di acquisizione 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 su repliche secondarie prima della persistenza nel database primario.

Prima che i dati di Query Store vengano mantenuti nel database primario da repliche secondarie leggibili, è possibile assegnare identificatori temporanei alle query e ai piani all'interno della rappresentazione locale in memoria di Query Store, ovvero il MEMORYCLERK_QUERYDISKSTORE_HASHMAP. Gli ID di query e di piano possono apparire come numeri negativi e fungono da segnaposto finché la replica primaria non assegna un identificatore autorevole, cosa che avviene dopo che Query Store determina che una query soddisfa i requisiti della modalità di acquisizione configurati. 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

  • Last updated on