Magasin des requêtes pour les réplicas secondaires

  • Article

SQL Server 2022 (16.x)

La fonctionnalité Magasin des requêtes pour les réplicas secondaires active la même fonctionnalité de Magasin des requêtes sur les charges de travail de réplica secondaire que celle disponible pour les réplicas principaux. Quand le Magasin des requêtes pour les réplicas secondaires est activé, les réplicas envoient les informations d’exécution de requête qui seraient normalement stockées dans le Magasin des requêtes vers le réplica principal. Le réplica principal conserve ensuite les données sur le disque dans son propre Magasin des requêtes. En substance, il existe un Magasin des requêtes partagé entre le réplica principal et tous les réplicas secondaires. Le Magasin des requêtes existe sur le réplica principal et stocke les données de tous les réplicas ensemble. Actuellement, le Magasin des requêtes pour les réplicas secondaires est disponible avec les instances SQL Server 2022 (16.x) configurées dans les groupes de disponibilité.

Important

Le Magasin des requêtes pour les réplicas secondaires est une fonctionnalité en préversion . Elle n’est pas destinée aux déploiements de production. Consultez les notes de publication de SQL Server 2022 (16.0).

Vous devez activer l’indicateur de trace 12606 avant de pouvoir activer le Magasin des requêtes pour les réplicas secondaires. Pour activer les indicateurs de trace :

  1. Dans Windows, lancez le Gestionnaire de configuration SQL Server.
  2. Dans la liste des services SQL Server, cliquez avec le bouton droit sur le service d’instance SQL Server pour votre instance SQL Server 2022 (16.x). Sélectionnez Propriétés.
  3. Sélectionnez l’onglet Paramètres de démarrage. Dans le champ Spécifier un paramètre de démarrage, ajoutez les valeurs : -T12606 puis sélectionnez Ajouter.
  4. Le service d’instance SQL Server doit être redémarré avant que les modifications prennent effet.

Activer le Magasin des requêtes pour les réplicas secondaires

Avant d’utiliser le Magasin des requêtes pour les réplicas secondaires sur une instance SQL Server, vous devez disposer d’un groupe de disponibilité Always On. Ensuite, activez le Magasin des requêtes pour les réplicas secondaires à l’aide des options ALTER DATABASE SET (Transact-SQL).

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

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

Pour activer le Magasin des requêtes sur tous les réplicas secondaires, connectez-vous au réplica principal et exécutez ce qui suit pour chaque base de données souhaitée. Actuellement, lorsque le Magasin des requêtes pour les réplicas secondaires est activé, il est activé pour tous les réplicas secondaires.

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

Pour désactiver le Magasin des requêtes sur tous les réplicas secondaires, connectez-vous au réplica principal et exécutez les éléments suivants pour chaque base de données souhaitée :

ALTER DATABASE [Database_Name]
FOR SECONDARY SET QUERY_STORE = OFF;
GO

Vous pouvez vérifier que le Magasin des requêtes est activé sur un réplica secondaire en vous connectant à la base de données sur le réplica secondaire et en exécutant les éléments suivants :

SELECT desired_state, desired_state_desc, actual_state, actual_state_desc, readonly_reason
FROM sys.database_query_store_options;
GO

Les résultats suivants de l’interrogation de sys.database_query_store_options indiquent que le Magasin des requêtes est dans un état READ_CAPTURE_SECONDARY pour la base de données secondaire. 8 Indique readonly_reason que la requête a été exécutée sur un réplica secondaire. Ces résultats indiquent que le Magasin des requêtes a été activé avec succès sur le réplica secondaire.

desired_state desired_state_desc actual_state actual_state_desc readonly_reason
4 READ_CAPTURE_SECONDARY 4 READ_CAPTURE_SECONDARY 8

Une fois activé, vous pouvez utiliser sys.query_store_replicas pour vérifier l’intégrité du Magasin des requêtes sur le réplica secondaire.

Pour désactiver le Magasin des requêtes pour les réplicas secondaires, connectez-vous à la base de données sur le réplica principal et exécutez le code suivant :

ALTER DATABASE CURRENT
FOR SECONDARY SET QUERY_STORE = OFF;
GO

Jeux de réplicas

Actuellement, lorsque le Magasin des requêtes pour les réplicas secondaires est activé, il est activé pour tous les réplicas secondaires.

Un jeu de réplicas est défini comme étant tous les réplicas non nommés qui partagent un rôle (principal, secondaire, géo secondaire, géo-primaire) ou comme étant un réplica nommé individuel. Les données stockées sur les requêtes peuvent être analysées en tant que charges de travail en fonction d’un jeu de réplicas. Le Magasin des requêtes pour les réplicas permet de superviser et d’ajuster les performances de toutes les charges de travail uniques en lecture seule qui peuvent s’exécuter sur des réplicas secondaires.

Considérations relatives aux performances pour le Magasin des requêtes pour les réplicas secondaires

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. Les données sont stockées sur le réplica principal dans les mêmes tables qu’utilise le Magasin des requêtes pour les requêtes exécutées sur le réplica principal, ce qui entraîne une augmentation de la taille du Magasin des requêtes.

Ainsi, quand un système est soumis à une charge importante, vous pouvez remarquer un ralentissement lié à la surcharge subie par le canal. En outre, les mêmes problèmes de capture de requêtes adhoc qui existent aujourd’hui pour le Magasin des requêtes continueront pour les charges de travail exécutées sur des réplicas secondaires. Découvrez-en plus sur la façon de conserver les données les plus pertinentes dans le Magasin des requêtes.

Voir aussi

Étapes suivantes