Compartir a través de

Almacén de consultas para secundarias legibles

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores

El almacén de consultas para réplicas secundarias legibles permite obtener información del almacén de consultas para las cargas de trabajo que se ejecutan en réplicas secundarias. Cuando está habilitada, las réplicas secundarias transmiten información de ejecución de consultas (como estadísticas de tiempo de ejecución y espera) a la réplica principal, donde los datos se conservan en el Almacén de consultas y se hacen visibles en todas las réplicas.

La característica se introdujo originalmente en SQL Server 2022 (16.x), pero estaba desactivada de forma predeterminada y requería una marca de seguimiento para habilitar. Esto se debe en parte porque la característica era y sigue estando en un estado de versión preliminar para SQL Server 2022 (16.x).

A partir de la versión preliminar de SQL Server 2025 (17.x), el Almacén de consultas para secundarias legibles está habilitado de forma predeterminada.

Importante

En SQL Server 2022 (16.x), el Almacén de consultas para secundarias legibles es una característica de vista previa y requiere que la marca de seguimiento 12606 se aplique a las réplicas secundarias principales y legibles. No está pensado para implementaciones de producción basadas en SQL Server 2022 (16.x). Para obtener más información, vea notas de la versión de SQL Server 2022.

Para la versión preliminar de SQL Server 2025 (17.x), la característica está activada de forma predeterminada y la marca de seguimiento 12606 no es necesaria. Habilitar esta marca de seguimiento tiene el efecto de deshabilitar la característica.

Habilitar el Almacén de Consultas para réplicas secundarias legibles

Antes de usar el Almacén de consultas para secundarias legibles en una instancia en versión preliminar de SQL Server 2025 (17.x), se debe configurar un grupo de disponibilidad Always On.

Si Query Store aún no está habilitado en la réplica principal y en modo READ_WRITE, debe habilitarlo antes de continuar. Ejecute el siguiente script para cada base de datos deseada en la réplica principal:

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

Para habilitar el Almacén de consultas en todas las secundarias legibles, conéctese a la réplica principal y ejecute el siguiente script para cada base de datos que se va a inscribir para usar la característica.

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

Habilitar la corrección automática del plan de ejecución para réplicas secundarias

Después de habilitar el Almacén de consultas para réplicas secundarias, puede optar por habilitar el ajuste automático para permitir que la característica de corrección automática de planes force la aplicación de planes en las réplicas secundarias. Esto permite que el optimizador de consultas identifique y corrija automáticamente los problemas de rendimiento de las consultas causados por regresiones del plan de ejecución en réplicas secundarias.

Para habilitar la corrección automática del plan para las réplicas secundarias, conéctese a la réplica principal y ejecute el siguiente script para cada base de datos deseada:

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

Desactivar Query Store para réplicas secundarias

Para deshabilitar la característica Almacén de consultas para réplicas secundarias en todas las réplicas secundarias, conéctese a la master base de datos de la primary réplica y ejecute el siguiente script para cada base de datos deseada:

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

Verificar que el Almacén de Consultas está activado en las réplicas secundarias

Puede validar que el Almacén de consultas está habilitado en una secondary réplica mediante la conexión a la base de datos en la réplica secundaria y ejecute la siguiente instrucción t-sql:

SELECT desired_state_desc,
       actual_state_desc,
       readonly_reason
FROM sys.database_query_store_options;

Los resultados de consultar la vista de catálogo de sys.database_query_store_options deben indicar que el estado real del Almacén de consultas es READ_CAPTURE_SECONDARY con un readonly_reason de 8.

desired_state_desc actual_state_desc readonly_reason
READ_CAPTURE_SECONDARY READ_CAPTURE_SECONDARY 8

Observaciones

Terminología

Un conjunto de réplicas se define como réplica de lectura y escritura de una base de datos (principal) y una o varias réplicas de solo lectura (secundaria) tratadas como una unidad lógica. Un rol en este contexto hace referencia a la función de una réplica específica. Cuando una réplica actúa en el rol principal, es la réplica de lectura y escritura que puede realizar modificaciones de datos y actividad de lectura. Cuando una réplica está configurada para realizar solo operaciones de solo lectura, sirve en un rol secundario (secundario, secundario geográfico, secundario HA geográfico). Los roles pueden cambiar a través de eventos de conmutación por error planeados o no planeados, cuando esto sucede, una principal puede convertirse en secundaria o viceversa.

Los roles compatibles actualmente son:

  • Primary
  • Secondary
  • Base de datos secundaria geográfica
  • Secundario de alta disponibilidad geográfica
  • Réplica con nombre

Cómo funciona

Los datos almacenados sobre las consultas se pueden analizar como cargas de trabajo en función de un rol. El Almacén de consultas para secundarias legibles permite supervisar el rendimiento de cualquier carga de trabajo única de solo lectura que pueda ejecutarse en réplicas secundarias. Los datos se agregan en el nivel de rol. Por ejemplo, una configuración de grupos de disponibilidad distribuidos de SQL Server puede constar de:

  • Una réplica principal, parte del grupo de disponibilidad 1 (AG1)

  • Dos réplicas secundarias locales, también parte del AG1

  • Una réplica principal remota en otra ubicación que forma parte de un grupo de disponibilidad independiente (AG2). En términos de SQL Server, también se denominaría normalmente reenviador global; sin embargo, la característica Almacén de consultas para secundarias legibles reconocerá y hará referencia a ella como una Geo secondary réplica, suponiendo que se trata de una réplica secundaria distribuida geográficamente.

Si AG1 y AG2 están configurados para permitir conexiones de solo lectura cuando se ejecuta una carga de trabajo de solo lectura en cualquiera de las réplicas secundarias de AG1, las estadísticas de ejecución del Almacén de consultas se envían a la réplica principal de AG1 y se agregan y conservan como datos generados a partir del secondary rol antes de que los datos se devuelvan a todas las réplicas secundarias, incluido el reenviador global en AG2. Cuando se ejecuta una carga de trabajo independiente en el primario de AG2, el reenviador global, sus datos se devuelven a la réplica primaria de AG1 y se conservan como datos generados a partir de la Geo secondary función.

Desde una perspectiva de observabilidad, la vista de catálogo del sistema sys.query_store_runtime_stats se extiende para ayudar a identificar el rol desde el que se originaron las estadísticas de ejecución. Hay una relación entre esta vista y la vista de catálogo del sistema sys.query_store_replicas, que puede ofrecer un nombre más amigable para el rol. En SQL Server, la columna replica_name es NULL. Sin embargo, la columna replica_name se rellena para el nivel de servicio Hiperescala si hay una réplica con nombre presente y se usa para cargas de trabajo de solo lectura.

Un ejemplo de una consulta t-sql que se podría usar para proporcionar un análisis general de las 50 consultas principales en las últimas 8 horas, que consumieron recursos de CPU de todas las réplicas sería:

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

Los informes de Query Store en SQL Server Management Studio (SSMS) 21 y versiones posteriores ofrecen una lista desplegable de Réplica, que proporciona una manera de ver los datos de Query Store en varios conjuntos de réplicas o roles. Además, dentro de la vista Explorador de objetos, el nodo Almacén de consultas refleja el estado actual del Almacén de consultas (es decir, READ_CAPTURE) si está conectado a una réplica secundaria legible.

Consideraciones de rendimiento para el Almacén de consultas para secundarias legibles

El canal usado por las réplicas secundarias para devolver información de consulta a la réplica principal es el mismo canal que se usa para mantener actualizadas las réplicas secundarias. channel¿Qué significa aquí?

En una configuración de grupo de disponibilidad (HADR), las réplicas se sincronizan entre sí mediante una capa de transporte dedicada que lleva bloques de registro, confirmaciones y mensajes de estado entre las réplicas principales y secundarias. Esto garantiza la coherencia de los datos y la disposición para la conmutación por error.

Cuando el Almacén de consultas para secundarias legibles está habilitado, no crea un punto de conexión de red independiente. En su lugar, establece una nueva ruta de comunicación lógica a través de la capa de transporte existente:

Esta ruta de acceso multiplexa los datos de ejecución del Query Store (texto de consulta, planes, estadísticas de ejecución/espera en tiempo de ejecución) junto con el tráfico normal de registro de logs, empleando la misma sesión cifrada. La característica tiene sus propias colas de captura y recepción, que se pueden ver consultando la vista desde la sys.database_query_store_internal_state perspectiva de cualquier réplica.

SELECT pending_message_count,
       messaging_memory_used_mb
FROM sys.database_query_store_internal_state;

Los datos de secundarias se conservan en las mismas tablas del Almacén de consultas en el servidor principal, lo que puede aumentar los requisitos de almacenamiento. Bajo una carga pesada, es posible que observe la latencia o la contrapresión en el canal de transporte. Las mismas limitaciones de captura de consultas ad hoc que se aplican al Query Store en la base de datos principal también se aplican a las bases de datos secundarias. Para obtener más información e instrucciones sobre cómo administrar las directivas de captura y tamaño del Almacén de consultas, consulte Mantener los datos más relevantes en el Almacén de consultas.

Visibilidad negativa del identificador de consulta o del plan

Los identificadores negativos indican marcadores de posición temporales en memoria para consultas o planes en secundarios antes de la persistencia en el nodo primario.

Antes de que los datos del Almacén de Consultas se persistan en el primario a partir de réplicas secundarias legibles, es posible que a las consultas y los planes se les asignen identificadores temporales en la representación local en memoria del Almacén de Consultas: el MEMORYCLERK_QUERYDISKSTORE_HASHMAP. Los identificadores de consulta y de plan pueden aparecer como números negativos y actúan como marcadores de posición hasta que la réplica principal les asigne un identificador autoritativo, lo cual ocurre después de que el almacén de consultas determine que una consulta cumple con los requisitos del modo de captura configurados. Si hay una directiva de captura personalizada , puede revisar los requisitos que se deben cumplir consultando la vista de catálogo del 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;

Una vez que se designa una consulta como capturada, se pueden conservar sus estadísticas de tiempo de ejecución o espera y el plan, y los identificadores temporales locales se reemplazan por identificadores positivos. Esto también le permite utilizar las capacidades de forzar el plan o sugerir con pistas.

Contenido relacionado

  • Last updated on