Partager via

Magasin des requêtes pour les fichiers secondaires lisibles

S’applique à : SQL Server 2022 (16.x) et versions ultérieures.

Le Magasin des requêtes pour les fichiers secondaires lisibles active les insights du Magasin des requêtes pour les charges de travail qui s’exécutent sur des réplicas secondaires. En cas d’activation, les réplicas secondaires diffusent des informations d’exécution de requête (telles que les statistiques d’exécution et d’attente) sur le réplica principal, où les données sont conservées dans le Magasin des requêtes et rendues visibles sur tous les réplicas.

La fonctionnalité a été introduite à l’origine dans SQL Server 2022 (16.x), mais elle était désactivée par défaut et exigeait l’activation d’un indicateur de trace. Cela était dû en partie parce que la fonctionnalité était et reste dans un état d’aperçu pour SQL Server 2022 (16.x).

À compter de la préversion de SQL Server 2025 (17.x), le Query Store pour les secondaires lisibles est activé par défaut.

Important

Dans SQL Server 2022 (16.x), le Magasin des requêtes pour les réplicas secondaires lisibles est une fonctionnalité d’aperçu et nécessite l’application de l’indicateur de trace 12606 au réplica principal et à tous les réplicas secondaires lisibles. Il n’est pas destiné aux déploiements de production basés sur SQL Server 2022 (16.x). Pour plus d’informations, consultez les notes de publication de SQL Server 2022.

Pour SQL Server 2025 (17.x) Preview, la fonctionnalité est activée par défaut et l’indicateur de trace 12606 n’est pas obligatoire. L’activation de cet indicateur de trace a pour effet de désactiver la fonctionnalité.

Activer le Magasin des requêtes pour les fichiers secondaires lisibles

Avant d’utiliser Query Store pour les secondaires lisibles sur une instance SQL Server 2025 (17.x) Preview, un groupe de disponibilité Always On doit être configuré.

Si le Magasin des requêtes n’est pas déjà activé et en READ_WRITE mode sur la réplique principale, vous devez l’activer avant de poursuivre. Exécutez le script suivant pour chaque base de données souhaitée sur le réplica principal :

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

Pour activer le Query Store sur tous les réplicas secondaires lisibles, connectez-vous au réplica principal et exécutez le script suivant pour chaque base de données à inscrire pour utiliser la fonctionnalité.

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

Activer la correction automatique du plan pour les répliques secondaires

Après avoir activé le Store de requêtes pour les réplicas secondaires, vous avez la possibilité d'activer le réglage automatique pour permettre à la fonctionnalité de correction automatique du plan d'imposer des plans sur les réplicas secondaires. Cela permet à l’optimiseur de requête d’identifier et de corriger automatiquement les problèmes de performances des requêtes causés par les régressions de plan d’exécution sur les réplicas secondaires.

Pour activer la correction automatique des plans procéduraux pour les réplicas secondaires, connectez-vous au réplica principal et exécutez le script suivant pour chaque base de données concernée :

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

Désactiver le magasin de requêtes pour les réplicas secondaires

Pour désactiver la fonctionnalité Query Store pour les réplicas secondaires, connectez-vous à la base de données master sur le réplica primary et exécutez le script suivant pour chaque base de données souhaitée sur tous les réplicas secondaires :

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

Valider que le Magasin des requêtes est activé sur les réplicas secondaires

Vous pouvez vérifier que le Magasin des requêtes est activé sur un secondary réplica en vous connectant à la base de données sur le réplica secondaire et en exécutant l’instruction t-sql suivante :

SELECT desired_state_desc,
       actual_state_desc,
       readonly_reason
FROM sys.database_query_store_options;

Les résultats de l’interrogation de la vue catalogue sys.database_query_store_options doivent indiquer que l’état réel du Query Store est READ_CAPTURE_SECONDARY avec un readonly_reason de 8.

desired_state_desc actual_state_desc readonly_reason
READ_CAPTURE_SECONDARY READ_CAPTURE_SECONDARY 8

Remarques

Terminologie

Un ensemble de réplicas est défini comme réplica principal en lecture/écriture d’une base de données et une ou plusieurs unités de réplication en lecture seule (secondaire) traitées comme une unité logique. Un rôle dans ce contexte fait référence au rôle d'une réplique spécifique. Lorsqu'un réplica assure le rôle principal, c'est le réplica lecture-écriture qui peut effectuer à la fois des modifications de données et des activités de lecture. Lorsqu’un réplica est configuré pour effectuer uniquement une activité en lecture seule, il sert dans un rôle secondaire (secondaire, géo secondaire, géo-ha secondaire). Les rôles peuvent changer par le biais d'événements de transfert planifiés ou non planifiés ; lorsque cela se produit, un serveur principal peut devenir un serveur secondaire ou inversement.

Les rôles actuellement pris en charge sont les suivants :

  • Primary
  • Secondary
  • Géographie secondaire
  • Géo HA secondaire
  • Réplique nommée

Fonctionnement

Les données stockées sur les requêtes peuvent être analysées en tant que charges de travail sur une base de rôle. Le Magasin des requêtes pour les réplicas secondaires lisibles vous permet de surveiller les performances de toute charge de travail en lecture seule unique susceptible de s’exécuter sur des réplicas secondaires. Les données sont agrégées au niveau du rôle. Par exemple, une configuration de groupes de disponibilité distribués SQL Server peut se composer des éléments suivants :

  • Un réplica principal, faisant partie du groupe de disponibilité 1 (AG1)

  • Deux réplicas secondaires locaux, également partie de AG1

  • Un réplica principal à distance dans un autre emplacement qui fait partie d’un groupe de disponibilité distinct (AG2). En termes SQL Server, il serait également communément appelé redirecteur global. Toutefois, la fonctionnalité magasin de requêtes pour les fichiers secondaires lisibles reconnaîtra et fera référence à celle-ci en tant que Geo secondary réplica, en supposant qu’il s’agit d’un réplica secondaire distribué géographiquement.

Si AG1 et AG2 sont configurés pour autoriser les connexions en lecture seule lorsqu’une charge de travail en lecture seule s’exécute sur l’un des réplicas secondaires de AG1, les statistiques d’exécution du Magasin de requêtes sont envoyées au réplica principal de AG1 et agrégées et conservées en tant que données générées à partir du secondary rôle avant que ces données ne soient renvoyées à tous les réplicas secondaires, y compris le redirecteur global dans AG2. Lorsqu'une charge de travail distincte est exécutée sur le principal d'AG2, le forwarder global, ses données sont renvoyées au réplica principal d'AG1 et conservées en tant que données générées à partir du rôle Geo secondary.

Du point de vue de l’observabilité, l’affichage catalogue système sys.query_store_runtime_stats est étendu pour aider à identifier le rôle à partir duquel les statistiques d’exécution proviennent. Il existe une relation entre cette vue et la vue de catalogue système sys.query_store_replicas, qui peut fournir un nom de rôle plus convivial. Dans SQL Server, la colonne replica_name est NULL. Toutefois, la colonne replica_name est remplie pour le niveau de service Hyperscale s’il existe un réplica nommé et qu'il est utilisé pour les charges de travail en lecture seule.

Voici un exemple de requête t-sql qui peut être utilisée pour fournir une analyse globale des 50 premières requêtes au cours des 8 dernières heures, qui consommaient des ressources processeur de tous les réplicas :

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

Les rapports du Magasin des requêtes dans SQL Server Management Studio (SSMS) 21 et les versions ultérieures fournissent une liste déroulante Réplique, qui permet d’afficher les données du Magasin des requêtes à travers divers ensembles/rôles de répliques. En outre, dans l'affichage Object Explorer, le nœud Query Store reflète l'état actuel de Query Store (c'est-à-dire READ_CAPTURE) s'il est connecté à une réplique secondaire lisible.

Considérations relatives aux performances pour le Magasin des requêtes pour les fichiers secondaires lisibles

Le canal utilisé par les réplicas secondaires pour renvoyer des informations de requête au réplica principal est le même canal que celui utilisé pour maintenir les réplicas secondaires à jour. Qu’est-ce channel que cela veut dire ici ?

Dans une configuration de groupe de disponibilité (HADR), les répliques se synchronisent entre elles à l’aide d’une couche de transport dédiée qui transporte des blocs de journaux, des accusés de réception et des messages d’état entre la réplique principale et les répliques secondaires. Cela garantit la cohérence des données et la préparation du basculement.

Lorsque le Magasin des requêtes pour les fichiers secondaires lisibles est activé, il ne crée pas de point de terminaison réseau distinct. Au lieu de cela, il établit un nouveau chemin de communication logique sur la couche de transport existante :

Ce chemin multiplexe les données d’exécution du Store des requêtes (texte de la requête, plans, statistiques d’exécution/attente), parallèlement au trafic normal des enregistrements du journal, en utilisant la même session chiffrée. La fonctionnalité possède ses propres files d’attente de capture et de réception, qui peuvent être consultées en interrogeant la sys.database_query_store_internal_state vue du point de vue de n’importe quel réplica :

SELECT pending_message_count,
       messaging_memory_used_mb
FROM sys.database_query_store_internal_state;

Les données des secondaires sont conservées dans les mêmes tables du Magasin des requêtes sur le serveur principal, ce qui peut augmenter les besoins de stockage. En cas de charge importante, vous pouvez observer une latence ou une rétropression sur le canal de transport. Les mêmes limitations de capture de requêtes ad hoc qui s’appliquent au Magasin des requêtes sur le serveur principal s’appliquent également aux secondaires. Pour plus d’informations et des conseils sur la gestion des stratégies de taille et de capture du Magasin des requêtes, consultez Conserver les données les plus pertinentes dans le Magasin des requêtes.

Visibilité négative de l’ID de requête/id de plan

Les ID négatifs indiquent des espaces réservés temporaires en mémoire pour les requêtes/plans sur les fichiers secondaires avant la persistance vers le serveur principal.

Avant que les données du Magasin des requêtes ne soient persistées dans la base de données principale depuis les réplicas secondaires en lecture seule, les requêtes et les plans peuvent se voir attribuer des identificateurs temporaires dans la représentation locale en mémoire du Magasin des requêtes, le MEMORYCLERK_QUERYDISKSTORE_HASHMAP. Les ID de requête et de plan peuvent apparaître sous forme de nombres négatifs et sont des espaces réservés jusqu’à ce que le réplica principal attribue un identificateur faisant autorité, qui se produit après que le Magasin des requêtes détermine qu’une requête répond aux exigences en mode de capture configurées. Si une stratégie de capture personnalisée est en place, vous pouvez passer en revue les exigences qui doivent être remplies en interrogeant l’affichage sys.database_query_store_options catalogue système.

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;

Une fois qu’une requête est désignée comme capturée, ses statistiques d’exécution/attente et son plan peuvent être conservés et les ID temporaires locaux sont remplacés par des ID positifs. Cela vous permet également d'utiliser des capacités de contraintes ou de suggestion de planification.

Contenu connexe

  • Last updated on