Partager via

Joindre l’hypothèse d’isolement dans le nouvel estimateur de cardinalité dégrade les performances des requêtes

  • Article

Cet article vous aide à résoudre les problèmes de performances qui peuvent se produire dans SQL Server 2014 et versions ultérieures lorsque vous compilez vos requêtes à l’aide du nouvel estimateur de cardinalité.

Version du produit d’origine : SQL Server
Numéro de base de connaissances d’origine : 3189675

Symptômes

Examinez le cas suivant :

  • Vous utilisez SQL Server 2014 ou une version ultérieure.
  • Vous exécutez une requête qui contient des jointures et des prédicats de filtre non de jointure.
  • Vous compilez la requête à l’aide de la nouvelle estimation de cardinalité (SQL Server) (Nouveau CE).

Dans ce scénario, vous rencontrez une dégradation des performances des requêtes.

Ce problème ne se produit pas si vous compilez la requête à l’aide de la version CE héritée.

Cause

À partir de SQL Server 2014, le nouvel estimateur de cardinalité (New CE) a été introduit pour le niveau de compatibilité de base de données 120 et supérieur. Le nouveau CE modifie plusieurs hypothèses de la version CE héritée dans le modèle utilisé par l’optimiseur de requête lorsqu’il estime la cardinalité pour différents opérateurs et prédicats.

L’une de ces modifications est liée à l’hypothèse de jointure de l’endiguement.

Le modèle CE hérité suppose que les utilisateurs interrogent toujours les données qui existent. Cela signifie que, pour un prédicat de jointure qui implique une opération équijoin pour deux tables, les colonnes jointes existent sur les deux côtés de la jointure. En présence de prédicats de filtre non-jointure supplémentaires sur la table de jointure, le ce hérité suppose un certain niveau de corrélation pour les prédicats de jointure et les prédicats de filtre non jointure. Cette corrélation implicite est appelée Simple Containment.

Sinon, le Nouveau CE utilise l’endiguement de base comme corrélation. Le nouveau modèle CE suppose que les utilisateurs peuvent interroger des données qui n’existent pas. Cela signifie que les prédicats de filtre sur des tables distinctes peuvent ne pas être corrélés les uns avec les autres. Par conséquent, nous utilisons une approche probabiliste.

Pour de nombreux scénarios pratiques, l’utilisation de l’hypothèse de l’endiguement de base crée de meilleures estimations. Cela crée à son tour des choix de plan de requête plus efficaces. Toutefois, dans certaines situations, l’utilisation de l’hypothèse simple de l’endiguement peut fournir de meilleurs résultats. Si cela se produit, vous risquez d’avoir un choix de plan de requête moins efficace lorsque vous utilisez le nouveau ce au lieu de l’ancienne version DE.

Pour plus d’informations sur la résolution des problèmes liés au nouveau ce, consultez Réduction des performances des requêtes après la mise à niveau de SQL Server 2012 ou version antérieure vers 2014 ou version ultérieure.

Résolution

Dans SQL Server 2014 et versions ultérieures, vous pouvez utiliser l’indicateur de trace 9476 pour forcer SQL Server à utiliser l’hypothèse simple de l’endiguement au lieu de l’hypothèse d’endiguement de base par défaut. Si vous pouvez modifier la requête d’application, la meilleure option consiste à utiliser l’indicateur ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS de requête après SQL Server 2016 (13.x) SP1. Pour plus d’informations, consultez USE HINT. Par exemple :

SELECT * FROM Table1 t1
JOIN Table2 t2
ON t1.Col1 = t2.Col1
WHERE Col1 = 10
OPTION (USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'));

L’activation de cet indicateur de trace ou l’utilisation de l’indicateur de requête peut améliorer le choix du plan de requête sans avoir à revenir entièrement au modèle CE hérité si les conditions suivantes sont remplies :

  • Vous rencontrez un choix de plan de requête non optimal qui provoque une dégradation globale des performances pour les requêtes qui contiennent des jointures et des prédicats de filtre non-jointure.
  • Vous pouvez vérifier une inexactitude significative dans une estimation de « cardinalité de jointure » (autrement dit, le nombre réel et estimé de lignes qui diffèrent considérablement).
  • Cette inexactitude n’existe pas lorsque vous compilez des requêtes à l’aide de la version CE héritée.

Vous pouvez activer cet indicateur de trace globalement, au niveau de la session ou au niveau de la requête.

Note

L’utilisation incorrecte d’indicateurs de trace peut dégrader les performances de votre charge de travail. Pour plus d’informations, consultez Indicateurs (Transact-SQL) – Requête.