Rétroaction d’estimation de cardinalité (CE) pour les expressions

S’applique à : SQL Server 2025 (17.x) Azure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

Les estimations de cardinalité inexactes provoquent souvent des performances médiocres lors de l’optimisation des requêtes. Les retours d’estimation de cardinalité (CE) pour les expressions étendent le cadre initié par la fonctionnalité de retour CE. L’objectif est d’améliorer les estimations de cardinalité pour les expressions répétées. La fonction de retour d'information pour les expressions apprend des exécutions précédentes d'expressions lors des requêtes, afin de déterminer les choix de modèle CE appropriés et d'appliquer ce qui a été appris aux futures exécutions de ces expressions. Comme les commentaires CE, les recommandations de modèle sont testées et appliquées automatiquement aux futures exécutions de requête.

La fonctionnalité commentaires pour les expressions identifie et utilise une hypothèse de modèle qui correspond mieux à l’expression et à la distribution des données d’une requête donnée, ce qui améliore à son tour la qualité du plan d’exécution de requête. Actuellement, les retours sur la fonctionnalité des expressions peuvent identifier les opérateurs de planification où le nombre estimé de lignes et le nombre réel de lignes sont très différents. Les commentaires sont appliqués aux expressions au sein d’une requête lorsque des erreurs d’estimation de modèle importantes se produisent et qu’il existe un autre modèle viable à essayer.

Différentes versions du moteur de base de données utilisent différentes hypothèses de modèle CE , en fonction de la façon dont les données sont distribuées et interrogées.

Utiliser les commentaires CE pour les expressions

Les commentaires CE pour expressions surveillent les exécutions de requêtes et identifient les sous-expressions qui entraînent de manière cohérente des misestimations de cardinalité. Les commentaires sont générés en fonction des modèles observés et appliqués pendant la compilation des requêtes pour améliorer la précision de l’estimation.

Conditions préalables et configuration

Pour utiliser les commentaires CE pour les expressions, les conditions préalables suivantes doivent être remplies :

• La base de données doit utiliser le niveau de compatibilité 160 ou version ultérieure.
• La configuration sur la portée de la base de données CE_FEEDBACK_FOR_EXPRESSIONS doit être activée (elle l'est par défaut).
• Pour consulter l’état actuel de la configuration à portée de la base de données :

SELECT name,
       value,
       value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = 'CE_FEEDBACK_FOR_EXPRESSIONS';

La fonctionnalité peut être activée sur une base de données avec la commande de configuration étendue à la base de données suivante :

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK_FOR_EXPRESSIONS = ON;

Pour désactiver la fonctionnalité de retour des expressions pour une base de données, désactivez la configuration à portée de base de données CE_FEEDBACK_FOR_EXPRESSIONS :

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK_FOR_EXPRESSIONS = OFF;

Fonctionnement

Bien que les empreintes digitales ne soient pas un nouveau concept avec le moteur de base de données SQL Server, une empreinte digitale dans le contexte de la fonctionnalité commentaires pour les expressions fait référence à la combinaison des signatures calculées à partir d’une expression. Par exemple, un analyste d’entreprise dans une entreprise fictive peut souhaiter obtenir des informations sur l’une des commandes de ses clients où ces clients ont dépensé plus de 10 000 $. Une instruction select qui implique la collecte de données à partir d’une table Customers qui se joint également à une table Orders peut être une façon d’afficher ce type de données :

SELECT *
FROM Customer AS C
     INNER JOIN Orders AS O
         ON C.custkey = O.o_custkey
WHERE O.o_totalprice > 10000;

Pour cette requête, l’optimiseur de requête peut choisir d’obtenir des données à partir de chaque table , Customersuivi de Orders, sélectionnez toutes les colonnes associées dans les deux tables et joignez les données (avec un filtre) où l’ordre totalprice est supérieur à 10 000 $. Chaque expression logique telle qu’un filtre ou une jointure au sein d’un plan de requête génère une signature qui contribue à une empreinte digitale. Les commentaires CE pour les expressions utilisent ces empreintes digitales pour apprendre et appliquer des commentaires entre les requêtes qui partagent des sous-expressions similaires, même si la structure de requête globale est différente.

La fonctionnalité se concentre sur les expressions présentant une surestimation/sous-estimation cohérente à travers les requêtes. Il analyse deux modèles de charge de travail différents qui ne sont actuellement pas éligibles pour les commentaires CE :

• Charges de travail sans exécutions répétées, mais avec des modèles d’expression répétés. Par exemple, un modèle de jointure couramment utilisé.

• Requêtes dans lesquelles une partie de la requête peut tirer parti d’un modèle CE différent de celui d’une autre partie de la même requête. Par exemple, la jointure entre les tables A et B peut nécessiter un simple confinement, et la jointure entre les tables C et D, ce qui peut nécessiter un confinement de base.

La fonctionnalité des commentaires sur les expressions applique des hypothèses de filtre et de jointure pour corriger les problèmes de mauvaise estimation, tels que :

Filters:

• ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES
• ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES
• ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES

Joins:

• ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS
• Supposition de l'isolement de base (aucune indication ne doit être fournie)

Ces hypothèses reflètent différentes stratégies de modèle CE, telles que l’isolement et l’indépendance. Pour plus d’informations conceptuelles, consultez Commentaires d’estimation de cardinalité expliqués par Kate Smith et estimation de cardinalité pour les colonnes corrélées dans SQL Server 2016.

Cycle de vie de l’indicateur

Les indicateurs de commentaires progressent dans les états suivants :

• Surveillance : le système observe des exécutions répétées d’une sous-expression et vérifie si une mauvaise estimation de cardinalité persiste.
• Application : Si l'erreur d'estimation se poursuit, un indice de rétroaction peut être généré et appliqué lors de la compilation des requêtes pour ajuster le modèle CE.
• Bloqué : si l’indicateur appliqué entraîne une estimation de cardinalité non optimale, il est bloqué contre l’utilisation ultérieure.

Ce cycle de vie garantit que les commentaires sont appliqués uniquement lorsqu’ils sont bénéfiques et évitent la régression dans la qualité d’estimation.

Protection contre la régression

Les retours CE concernant les expressions incluent une protection contre la régression. Si un indice provoque une estimation de cardinalité moins précise qu’auparavant, il est bloqué. Toutefois, cette protection est limitée à l’estimation de cardinalité et n’évalue pas le temps d’exécution des requêtes. Pour les régressions liées à l'exécution ou au temps d'exécution, la correction automatique du plan peut intervenir. Si la fonctionnalité de correction automatique de plan n’est pas activée, les actions que cette fonctionnalité effectuerait sont enregistrées et disponibles en interrogeant la vue de gestion dynamique sys.dm_db_tuning_recommendations.

Télémétrie et surveillance

Les commentaires CE pour l’activité Expressions peuvent être surveillés à l’aide des outils suivants :

• Événements étendus :
  • adhoc_ce_feedback_query_level_telemetry
  • query_adhoc_ce_feedback_expression_hint
  • query_adhoc_ce_feedback_hint

Les événements étendus de feedback CE query_ce_feedback_begin_analysis et query_ce_feedback_telemetry peuvent également être utiles lors du suivi de l'utilisation de la fonctionnalité.

• Les données d’empreinte digitale sont mises en cache dans un gestionnaire de mémoire dédié nommé AdHocCEFeedbackCache. Ce cache est accessible via la vue sys.dm_exec_ce_feedback_cache du catalogue système.

• Intégration de Showplan

  Lorsqu’un commentaire CE pour l’indicateur d’expressions est appliqué, le plan de requête inclut un CardinalityFeedback attribut dans le code XML showplan. Cette balise indique que les commentaires ont été utilisés pour ajuster l’estimation de cardinalité pour une sous-expression spécifique.

Cache et persistance

Les commentaires persistants sont stockés dans une table du Magasin des requêtes interne (sys.plan_persist_ce_feedback_for_expressions) et rechargés au démarrage. Cela garantit que le système n'a pas besoin de réassimiler les commentaires pour les empreintes qu'il connaît déjà. Le mécanisme de persistance du cache est de type « avec perte », ce qui signifie que les données de retour ne sont enregistrées sur le disque que périodiquement. La fréquence de persistance n’est pas actuellement configurable.

Si l’instance SQL Server redémarre ou que la mémoire est effacée avant le prochain cycle de persistance, les retours générés depuis la dernière purge peuvent être perdus.

Limitations

La persistance n’est actuellement pas disponible pour le Magasin des requêtes sur les fichiers secondaires lisibles. Les commentaires CE pour les expressions peuvent s'appliquer différemment sur une réplique principale et sur une réplique secondaire. Toutefois, les commentaires ne sont pas conservés sur les réplicas secondaires et existent uniquement dans le cache basé sur la mémoire dans ce scénario. Si un événement de basculement se produit, les données de retour qui avaient été apprises sur l’un des serveurs secondaires lisibles sont perdues.

Contenu connexe

• Retour d'expérience sur l'estimation de la cardinalité (CE)