Commentaires sur le degré de parallélisme (DOP)

  • Article

S’applique à :S’applique à : SQL Server 2022 (16.x) et versions ultérieures, base de données Azure SQL

SQL Server 2022 (16.x) a présenté une nouvelle caractéristique appelée retour d’expérience du degré de parallélisme (DOP) pour améliorer le niveau de performance des requêtes, en identifiant les inefficacités du parallélisme pour les requêtes répétées, en fonction du temps écoulé et des attentes. Les commentaires DOP font partie de la famille de fonctionnalités de traitement intelligent des requêtes et traitent de l’utilisation non optimale du parallélisme pour les requêtes répétées. Ce scénario permet d’optimiser l’utilisation des ressources et d’améliorer la scalabilité des charges de travail, quand un parallélisme excessif peut entraîner des problèmes de performances.

Au lieu d’entraîner des ajustements globaux par défaut ou manuels à chaque requête, le retour d’expérience sur le DOP ajuste automatiquement le DOP pour éviter les problèmes décrits ci-dessus.

Pour obtenir d’autres caractéristiques de retour d’expérience sur les requêtes, consultez Retour d’expérience sur l’allocation de mémoire et Retour d’expérience sur l’estimation de la cardinalité (CE).

Le retour d’expérience sur le degré de parallélisme (DOP) évite l’excès de parallélisme

Au lieu d’entraîner des ajustements globaux par défaut ou manuels à chaque requête, le retour d’expérience sur le DOP ajuste automatiquement le DOP pour éviter les problèmes décrits ci-dessus.. Si l’utilisation du parallélisme est jugée inefficace, la retour d’expérience sur le DOP réduit le DOP pour l’exécution suivante de la requête, quel que soit le DOP configuré, et vérifie si cela a un effet positif.

Le parallélisme est souvent bénéfique pour les requêtes de création de rapports et analytiques ou les requêtes qui gèrent autrement de grandes quantités de données. À l’inverse, les requêtes centrées sur OLTP exécutées en parallèle peuvent rencontrer des problèmes de performances lorsque le temps passé à coordonner tous les threads l’emporte sur les avantages de l’utilisation d’un plan parallèle. Pour plus d’informations, voir la section Exécution du plan parallèle.

  • Pour activer le retour d’expérience sur le DOP, activez la DOP_FEEDBACKconfiguration délimitée à la base de données dans une base de données. Par exemple, à partir de la base de données utilisateur :

    ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;

  • Pour désactiver le retour d’expérience sur le DOP au niveau de la base de données, utilisez la DOP_FEEDBACKconfiguration délimitée à la base de données. Par exemple, à partir de la base de données utilisateur :

    ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = OFF;

  • Pour désactiver les commentaires DOP au niveau de la requête, utilisez l’indicateur de requête DISABLE_DOP_FEEDBACK.

Le Magasin des requêtes doit être activé pour chaque base de données où les commentaires DOP sont utilisés et dans l’état « Lecture écriture ». Le retour d’expérience est conservé dans l’affichage catalogue sys.query_store_plan_feedback lorsque nous atteignons un degré stable de valeur de retour d’expérience du parallélisme.

Le retour d’expérience sur le DOP est disponible pour les requêtes qui fonctionnent dans le niveau de compatibilité de la base de données 160 (introduit avec SQL Server 2022 (16.X)) ou version ultérieure.

Seuls les commentaires vérifiés sont conservés. Si DOP ajusté entraîne une régression du niveau de performance, les commentaires DOP reviennent au dernier DOP correct connu. Dans ce contexte, une requête annulée par l’utilisateur est également perçue comme une régression. Le retour d’expérience sur le DOP ne recompile pas les plans.

Le retour d’expérience stable est vérifié à nouveau lors de la recompilation de plan et peut être réajusté vers le haut ou vers le bas, mais jamais au-dessus du paramètre MAXDOP (y compris un indicateur MAXDOP).

À partir de SQL Server 2022 (16.x), lorsque le Magasin des requêtes pour les réplicas secondaires est activé, le retour d’expérience sur le DOP prend également en charge les réplicas pour les réplicas secondaires dans les groupes de disponibilité. Le retour d’expérience sur le DOP peut appliquer le retour d’expérience différemment sur un réplica principal et sur un réplica secondaire. Toutefois, le retour d’expérience sur le DOP n’est pas conservé sur les réplicas secondaires et sur le basculement le retour d’expérience sur le DOP de l’ancien réplica principal n’est pas appliqué au nouveau réplica principal. Lors du basculement, le retour d’expérience appliqué aux réplicas principaux ou secondaires est perdu. Pour plus d’informations, consultez Magasin des requêtes pour réplicas secondaires.

Mise en œuvre du retour d’expérience sur le degré de parallélisme (DOP)

Le retour d’expérience sur le degré de parallélisme (DOP) identifie les inefficacités du parallélisme pour les requêtes répétées, en fonction du temps écoulé et des attentes. Si l’utilisation du parallélisme est jugée inefficace, le retour d’expérience sur le DOP réduit le DOP pour l’exécution suivante de la requête, quel que soit le DOP configuré et vérifie si cela a un effet positif.

Pour évaluer l’éligibilité des requêtes, le temps écoulé de la requête ajustée est mesuré sur quelques exécutions. Le temps écoulé total pour chaque requête est ajusté en ignorant le verrou de mémoire tampon, les E/S tampons et les attentes d’E/S réseau qui sont externes à l’exécution de la requête parallèle. Le but de la caractéristique du retour d’expérience sur le DOP est d’augmenter la concurrence globale et de réduire considérablement les attentes, même si elle augmente légèrement le temps écoulé pendant les requêtes.

Seuls les commentaires vérifiés sont conservés. Si DOP ajusté entraîne une régression du niveau de performance, les commentaires DOP reviennent au dernier DOP correct connu. Dans ce contexte, une requête annulée par l’utilisateur est également perçue comme une régression.

Remarque

Le retour d’expérience sur le DOP ne recompile pas les plans.

Considérations sur le retour d’expérience sur le degré de parallélisme (DOP)

La valeur minimale du DOP pour toute requête ajustée avec le retour d’expérience sur le DOP est de 2. Les exécutions en série sont hors de portée du retour d’expérience sur le DOP.

Les informations sur le retour d’expérience peuvent être suivies à l’aide de l’affichage catalogue sys.query_store_plan_feedback.

Si une requête a un plan de requête forcé via Magasin des requêtes, les commentaires DOP ne seront pas utilisés pour cette requête.

Actuellement, le le retour d’expérience sur le DOP n’est pas compatible avec les indicateurs de requête. Pour plus d’informations, consultez Indicateurs (Transact-SQL) - Requête et Indicateurs du Magasin des requêtes.

Événements étendus pour le le retour d’expérience sur le degré de parallélisme (DOP)

Les XEs suivantes sont disponibles pour le retour d’expérience sur le degré de parallélisme (DOP) :

  • dop_feedback_eligible_query : se produit lorsque le plan de requête devient éligible pour les commentaires DOP. Des événements supplémentaires peuvent être déclenchés si une recompilation ou un redémarrage d’instance de SQL Server se produit.
  • dop_feedback_provided : se produit lorsque des commentaires DOP ont fourni des données pour une requête donnée. Cet événement contient des statistiques de ligne de base lorsque les commentaires fournis pour la première fois et les statistiques de commentaires précédents sont fournies lors de la remise de commentaires ultérieurs.
  • dop_feedback_validation : se produit lorsque la validation se produit pour les statistiques du runtime de requête par rapport à une ligne de base ou à des statistiques de commentaires précédentes.
  • dop_feedback_stabilized : se produit lorsque les commentaires DOP sont stabilisés pour une requête.
  • dop_feedback_reverted : se produit lorsqu’un retour DOP est rétabli. L’événement se déclenche lorsque la validation du retour d’expérience échoue au premier retour d’expérience fourni. Le système revient à aucun état de commentaires.
  • dop_feedback_analysis_stopped : se produit lorsque l’analyse des commentaires DOP est arrêtée pour une requête.

Retour d’expérience sur la conservation du degré de parallélisme (DOP)

S’applique à :S’applique à : SQL Server 2022 (16.x) et versions ultérieures, base de données Azure SQL

Si le mécanisme de le retour d’expérience sur le DOP constate que le nouveau degré de parallélisme est bon, cette optimisation est conservée dans le magasin des requêtes et sera appliquée de manière appropriée à une requête pour des exécutions futures.

Cette caractéristique a été introduite dans SQL Server 2022 (16.x) et est disponible pour les requêtes qui fonctionnent dans le niveau de compatibilité de la base de données 160 ou postérieur, ou l’indicateur QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n de 160 et postérieur, et quand le magasin des requêtes est activé pour la base de données et est dans un état « lecture écriture ».

Contenu connexe