Repositório de Consultas para réplicas secundárias legíveis (versão prévia)

Applies to: SQL Server 2022 (16.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Repositório de Consultas para réplicas secundárias legíveis permite obter insights do Repositório de Consultas para cargas de trabalho executadas em réplicas secundárias. Quando habilitadas, as réplicas secundárias transmitem informações de execução de consulta (como estatísticas de tempo de execução e espera) para a réplica primária, em que os dados são mantidos em Repositório de Consultas e tornados visíveis em todas as réplicas.

Disponibilidade

Repositório de Consultas para réplicas secundárias legíveis está disponível a partir do SQL Server 2025 (17.x), bem como Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure com a política de atualização Always-up-to-date. Para o SQL Server 2022 (16.x), é necessário habilitar a sinalização de rastreamento 12606 para que o Repositório de Consultas funcione em réplicas secundárias legíveis. Para versões anteriores do SQL Server e Instância Gerenciada de SQL do Azure com outras políticas de atualização, Repositório de Consultas para réplicas secundárias legíveis não está disponível.

A tabela a seguir resume a disponibilidade e o estado habilitado do repositório de consultas para secundários legíveis.

¹ Repositório de consultas para secundários legíveis não está disponível atualmente na camada de serviço hiperescala do Banco de Dados SQL do Azure.
² Repositório de Consultas para secundários legíveis permanece em versão prévia para SQL Server 2022 (16.x) e, portanto, não tem suporte em produção, e está desabilitado por padrão. Para habilitar o Repositório de Consultas apenas para os secundários legíveis no SQL Server 2022 (16.x), é necessário ativar o sinalizador de rastreamento 12606 em todas as réplicas primárias e secundárias legíveis. O sinalizador de rastreamento 12606 não é destinado a implantações de produção baseadas em SQL Server 2022 (16.x). Para obter mais informações, consulte as notas de versão do SQL Server 2022.

Cenários de alta disponibilidade suportados

• Antes de usar Repositório de Consultas para réplicas secundárias legíveis em uma instância do SQL Server 2025 (17.x), é necessário configurar um grupo de disponibilidade Always On.

• Para Banco de Dados SQL do Azure, Repositório de Consultas para réplicas secundárias legíveis dá suporte às seguintes camadas de serviço:

  • Uso geral com configuração de replicação geográfica ativa ou de grupo de failover (sem réplicas internas de alta disponibilidade; requer configuração de replicação geográfica ou de grupo de failover para suporte secundário)
  • Premium (inclui réplicas integradas de alta disponibilidade; também há suporte para replicação geográfica ativa ou grupos de failover)
  • Comercialmente crítico (inclui réplicas internas de alta disponibilidade; também há suporte para replicação geográfica ativa ou grupos de failover)

• Para Instância Gerenciada de SQL do Azure com a política Always-up-to-date, Repositório de Consultas para réplicas secundárias legíveis dá suporte às seguintes camadas de serviço:

  • Uso geral com um grupo de failover (sem réplicas de alta disponibilidade integradas; requer uma configuração de grupo de failover para suporte secundário)
  • Crítico para o negócio (inclui réplicas integradas de alta disponibilidade)

Habilitar Repositório de Consultas para réplicas secundárias legíveis

Se o Repositório de Consultas ainda não estiver habilitado e no modo READ_WRITE na réplica primária, você deve habilitá-lo antes de continuar. Execute o seguinte script para cada banco de dados desejado na réplica primária:

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

Para habilitar o Repositório de Consultas em todas as réplicas secundárias legíveis, conecte-se à réplica primária e execute o script a seguir para cada banco de dados que deve ser inscrito para usar o recurso.

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

Habilitar a correção automática de plano para réplicas secundárias

Aplicável a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure.

Depois de habilitar o Repositório de Consultas para réplicas secundárias, você pode, opcionalmente, habilitar o ajuste automático para permitir que o recurso de correção automática de plano force os planos em réplicas secundárias. Isso permite que o otimizador de consulta identifique e corrija automaticamente problemas de desempenho de consulta causados por regressões de plano de execução em réplicas secundárias.

Para habilitar a correção automática de plano para réplicas secundárias, conecte-se à réplica primária e execute o seguinte script para cada banco de dados desejado:

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

Desabilitar Repositório de Consultas para réplicas secundárias

Para desabilitar o recurso Repositório de Consultas de réplicas secundárias em todas as réplicas secundárias, conecte-se ao banco de dados master na réplica primary e execute o seguinte script para cada banco de dados desejado:

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

Verifique se o Repositório de Consultas está habilitado em réplicas secundárias

Você pode validar que Repositório de Consultas está habilitado em uma réplica secondary conectando-se ao banco de dados na réplica secundária e executando a seguinte instrução T-SQL:

SELECT desired_state_desc,
       actual_state_desc,
       readonly_reason
FROM sys.database_query_store_options;

Espera-se que os resultados da consulta da exibição do catálogo sys.database_query_store_options indiquem que o estado real do Repositório de Consultas é READ_CAPTURE_SECONDARY com um readonly_reason de 8.

Observações

Terminologia

Um conjunto de réplicas é definido como uma réplica de leitura/gravação de um banco de dados (primária) e uma ou mais réplicas somente leitura (secundárias) tratadas como uma unidade lógica. Uma função nesse contexto refere-se à função de uma réplica específica. Quando uma réplica está desempenhando o papel primário, é a réplica de leitura/gravação que pode executar tanto modificações de dados quanto atividades de leitura. Quando uma réplica é configurada para executar apenas atividades de leitura, ela está servindo em uma função secundária (secundária, secundária geográfica, secundária de alta disponibilidade geográfica). As funções podem ser alteradas por meio de eventos de failover planejados ou não planejados. Quando isso acontece, um primário pode se tornar um secundário ou vice-versa.

As funções com suporte no momento são:

• Primária
• Secundário
• Secundário geográfico
• Geo HA secundário
• Réplica nomeada

Como funciona

Os dados armazenados sobre consultas podem ser analisados como cargas de trabalho em uma função. Repositório de Consultas para réplicas secundárias legíveis oferece a capacidade de monitorar o desempenho de qualquer carga de trabalho somente leitura única que possa estar sendo executada em réplicas secundárias. Os dados são agregados no nível da função. Por exemplo, uma configuração de grupos de disponibilidade distribuídos do SQL Server pode consistir de:

• Uma réplica primária, parte do Grupo de Disponibilidade 1 (AG1)

• Duas réplicas secundárias locais, também parte do AG1

• Uma réplica primária remota em outro local que faz parte de um grupo de disponibilidade separado (AG2). Em termos de SQL Server, ele também seria comumente conhecido como um encaminhador global, no entanto, o recurso Repositório de Consultas para réplicas secundárias legíveis reconhecerá e se referirá a ele como uma réplica Geo secondary, supondo que seja uma réplica secundária distribuída geograficamente.

Se o AG1 e o AG2 estiverem configurados para permitir conexões somente leitura quando uma carga de trabalho somente leitura for executada em qualquer uma das réplicas secundárias do AG1, as estatísticas de execução Repositório de Consultas serão enviadas para a réplica primária do AG1 e agregadas e mantidas como dados gerados a partir da função secondary antes que esses dados sejam enviados de volta para todas as réplicas secundárias, incluindo o encaminhador global no AG2. Quando uma carga de trabalho separada é executada no principal do AG2, o fowarder global, seus dados são enviados de volta para a réplica primária do AG1 e mantidos como dados gerados a partir da Geo secondary função.

Do ponto de vista da observabilidade, a exibição do catálogo do sistema sys.query_store_runtime_stats é estendida para ajudar a identificar a função da qual as estatísticas de execução se originaram. Há uma relação entre essa visão e a vista de catálogo de sistema sys.query_store_replicas, que pode fornecer um nome mais amigável para o papel. Em SQL Server, a coluna replica_name é NULL. No entanto, a coluna replica_name é preenchida para a camada de serviço de Hyperscale se houver uma réplica nomeada e estiver sendo usada para cargas de trabalho somente leitura.

Um exemplo de uma consulta T-SQL que pode ser usada para fornecer uma análise geral das 50 principais consultas nas últimas 8 horas, que consumiram recursos de CPU de todas as réplicas seria:

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

Os relatórios Repositório de Consultas no SQL Server Management Studio (SSMS) 21 e versões posteriores fornecem uma lista suspensa Replica, que fornece uma maneira de exibir dados do Repositório de Consultas em vários conjuntos ou funções de réplica. Além disso, dentro da exibição Object explorer, o nó Repositório de Consultas reflete o estado atual de Repositório de Consultas (ou seja, READ_CAPTURE) se conectado a uma réplica secundária legível.

Repositório de Consultas para telemetria de réplicas secundárias legíveis no Banco de Dados SQL do Azure

Applies para: Banco de Dados SQL do Azure

Ao transmitir as estatísticas de tempo de execução do Repositório de Consultas por meio das configurações de diagnóstico do Azure, duas colunas são incluídas para ajudar a identificar a fonte de réplica dos dados de telemetria:

• is_primary_b: um valor booliano que indica se os dados se originaram da réplica primária (true) ou de uma réplica secundária (false)
• replica_group_id: um inteiro que corresponde à função de réplica

Essas colunas são essenciais para desambiguar métricas e dados de desempenho ao analisar cargas de trabalho entre conjuntos de réplicas. Ao definir as configurações de diagnóstico para transmitir as estatísticas de runtime do Repositório de Consultas para Log Analytics, Event Hubs ou Armazenamento do Azure, certifique-se de que as suas queries e dashboards contabilizem essas colunas para segmentar corretamente os dados por função de réplica. Para obter mais informações sobre como definir as configurações de diagnóstico e as métricas disponíveis, consulte Configurações de diagnóstico no Azure Monitor.

Considerações de desempenho para Repositório de Consultas para réplicas secundárias legíveis

O canal usado por réplicas secundárias para enviar informações de consulta de volta para a réplica primária é o mesmo canal usado para manter as réplicas secundárias atualizadas. O que significa channel aqui?

Em uma configuração de grupo de disponibilidade (HADR), as réplicas sincronizam entre si usando uma camada de transporte dedicada que carrega blocos de log, confirmações e mensagens de status entre as réplicas primária e secundária. Isso garante a consistência de dados e a preparação para failover.

Quando o Repositório de Consultas para réplicas secundárias legíveis é habilitado, não cria um ponto de extremidade de rede separado. Em vez disso, estabelece um novo caminho de comunicação lógica sobre a camada de transporte existente:

• Para Banco de Dados SQL do Azure (não-Hyperscale), Instância Gerenciada de SQL do Azure e SQL Server, isso usa a camada de transporte Always On de alta disponibilidade e recuperação de desastres (HADR).

• Para Banco de Dados SQL do Azure Hiperescala, uma camada de transporte diferente chamada camada de transporte de E/S de Blob Remoto é usada. A camada de transporte de I/O de Blob Remoto é o canal de comunicação entre os nós computacionais e o Serviço de Log/Servidores de Página. A camada de transporte de E/S de Blob Remoto fornece um canal criptografado confiável para mover registros de log e páginas de dados.

Esse caminho multiplexa os dados de execução do Repositório de Consultas (texto da consulta, planos, estatísticas de tempo de execução/espera) junto com o tráfego normal de registros de log, usando a mesma sessão criptografada. O recurso tem suas próprias filas de captura de dados e recebimento, que podem ser visualizadas consultando a sys.database_query_store_internal_state perspectiva de exibição de qualquer réplica:

SELECT pending_message_count,
       messaging_memory_used_mb
FROM sys.database_query_store_internal_state;

Os dados de réplicas secundárias são mantidos nas mesmas tabelas do Repositório de Consultas no primário, o que pode aumentar os requisitos de armazenamento. Sob carga pesada, você pode observar latência ou recompactação no canal de transporte. As mesmas limitações de captura de consultas ad hoc que se aplicam ao Repositório de Consultas no servidor primário também se aplicam às réplicas secundárias. Para obter mais informações e orientações sobre como gerenciar o tamanho do Repositório de Consultas e as políticas de captura, consulte Mantenha os dados mais relevantes no Repositório de Consultas.

Visibilidade de consulta/ID do plano negativa

IDs negativas indicam espaços reservados temporários na memória para consultas ou planos em réplicas secundárias antes da persistência para a réplica primária.

Antes que os dados do Repositório de Consultas sejam tornados persistentes no primário a partir de réplicas secundárias legíveis, consultas e planos podem ser atribuídos identificadores temporários dentro da representação local na memória do Repositório de Consultas - o MEMORYCLERK_QUERYDISKSTORE_HASHMAP. As IDs de consulta e plano podem aparecer como números negativos e são espaços reservados até que a réplica primária atribua um identificador autoritativo, que ocorre após o Repositório de Consultas determinar que uma consulta atende aos requisitos de modo de captura configurados. Se uma política de captura personalizada estiver em vigor, você poderá examinar os requisitos que devem ser atendidos consultando a exibição do catálogo do sys.database_query_store_options 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;

Depois que uma consulta é designada como capturada, suas estatísticas de tempo de execução/espera e o plano podem ser persistidos e as IDs temporárias locais são substituídas por IDs positivas. Isso também permite que você use a capacidade de forçar ou sugerir planos.

Conteúdo relacionado

• opções 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)
• Repositório de Consultas hints
• Cenários de Uso do Repositório de Consultas
• sys.database_query_store_options (Transact-SQL)
• Práticas recomendadas para monitorar cargas de trabalho com Repositório de Consultas
• Melhores práticas para gerenciar o Repositório de Consultas
• Otimize o desempenho com o Repositório de Consultas