Estimation de la cardinalité (SQL Server)

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

L’optimiseur de requête SQL Server est un optimiseur de requête basé sur les coûts. Cela signifie qu'il sélectionne des plans de requête dont l'exécution présente le plus faible coût de traitement estimé. L’optimiseur de requête détermine le coût d’exécution d’un plan de requête à partir de deux facteurs principaux :

  • Le nombre total de lignes traitées à chaque niveau d'un plan de requête, appelé « cardinalité du plan »
  • Le modèle de coût de l'algorithme imposé par les opérateurs utilisés dans la requête

Le premier facteur, la cardinalité, est utilisé comme paramètre d'entrée du deuxième facteur, le modèle de coût. Par conséquent, une amélioration de la cardinalité aboutit à de meilleurs coûts estimés, ce qui permet d'obtenir des plans d'exécution plus rapides.

L’estimation de cardinalité (CE) dans SQL Server est principalement dérivée d’histogrammes créés lors de la création d’index ou de statistiques, manuellement ou automatiquement. Parfois, SQL Server utilise également des informations de contrainte et des réécritures logiques de requêtes pour déterminer carte inalité.

Dans les cas suivants, SQL Server ne peut pas calculer les cardinalités avec précision. Cela provoque des calculs de coûts incorrects qui peuvent entraîner des plans de requête non optimaux. Éviter ces constructions dans les requêtes peut améliorer les performances des requêtes. Parfois, d’autres formulations de requête ou d’autres mesures sont possibles, auquel cas elles sont soulignées :

  • Les requêtes avec prédicats qui utilisent des opérateurs de comparaison entre différentes colonnes de la même table.
  • Les requêtes avec des prédicats qui utilisent des opérateurs, et l’une des opérations suivantes est vraie :
    • Il n'existe pas de statistiques sur les colonnes utilisées de chaque côté des opérateurs.
    • La répartition des valeurs dans les statistiques n'est pas uniforme, mais la requête recherche un ensemble de valeurs hautement sélectif. Cette situation se vérifie notamment si l'opérateur est tout opérateur autre que celui d'égalité (=).
    • Le prédicat utilise l’opérateur de comparaison de non-égalité (!=) ou l’opérateur logique NOT.
  • Les requêtes qui utilisent n’importe quelle fonction SQL Server intégrée ou une fonction scalaire définie par l’utilisateur dont l’argument n’est pas une valeur constante.
  • Les requêtes qui impliquent la jointure de colonnes par le biais d'opérateurs de concaténation de chaînes ou arithmétiques.
  • Les requêtes qui comparent des variables dont les valeurs ne sont pas connues au moment de la compilation et de l'optimisation des requêtes.

Cet article explique comment évaluer et choisir la meilleure configuration CE pour votre système. La plupart des systèmes bénéficient de la dernière version de l’estimation de la cardinalité, car il s’agit de la plus précise. L’estimation de la cardinalité prédit le nombre de lignes que votre requête est susceptible de renvoyer. La prédiction de la cardinalité est utilisée par l’optimiseur de requête pour générer un plan de requête optimal. Avec des estimations plus précises, l’optimiseur de requête est généralement en mesure de produire un plan de requête plus optimal.

Le système d’applications peut contenir une requête importante dont le plan est remplacé par un plan plus lent en raison des modifications apportées à l’estimateur de cardinalité dans le cadre d’une nouvelle version. Il existe des techniques et des outils qui permettent d’identifier les requêtes qui s’exécutent plus lentement en raison de problèmes liés à l’estimateur de cardinalité. Vous disposez également d’options pour résoudre les problèmes de performances qui en résultent.

Versions de l’estimation de cardinalité

En 1998, une mise à jour majeure de la ce faisait partie de SQL Server 7.0, pour laquelle le niveau de compatibilité était 70. Cette version du modèle CE est fondée sur quatre hypothèses de base :

  • Indépendance : Les distributions de données sur différentes colonnes sont supposées être indépendantes les unes des autres, à moins que des informations de corrélation soient disponibles et utilisables.

  • Homogénéité : Les valeurs distinctes sont espacées de manière égale et ont toutes la même fréquence. Plus précisément, dans chaque étape d’histogramme, les valeurs distinctes sont réparties uniformément et chaque valeur a la même fréquence.

  • Autonomie (simple) : Les utilisateurs interrogent des données qui existent. Par exemple, pour une jointure d’égalité entre deux tables, prendre en compte la sélectivité des prédicats 1 dans chaque histogramme d’entrée, avant de joindre les histogrammes pour estimer la sélectivité de jointure.

  • Inclusion : Pour les prédicats de filtres où Column = Constant, la constante est en fait supposée exister pour la colonne associée. Si une étape d’histogramme correspondante n’est pas vide, l’une des valeurs distinctes de l’étape est supposée correspondre à la valeur du prédicat.

    1 Nombre de lignes satisfaisant au prédicat.

Les mises à jour suivantes ont commencé avec SQL Server 2014 (12.x), ce qui signifie les niveaux de compatibilité 120 et versions ultérieures. Les mises à jour de l’estimation de cardinalité pour les niveaux 120 et au-delà comprennent des hypothèses et des algorithmes mis à jour qui fonctionnent bien sur l’entreposage moderne de données et sur les charges de travail OLTP. À partir des hypothèses CE 70, les hypothèses de modèle suivantes ont été changées à compter de CE 120 :

  • L’indépendance devient corrélation : la combinaison des différentes valeurs de colonne n’est pas nécessairement indépendante. Cela peut ressembler à des requêtes de données plus réelles.
  • Autonomie simple devient Autonomie de base : Les utilisateurs peuvent interroger des données qui n’existent pas. Par exemple, pour une jointure d’égalité entre deux tables, nous utilisons les histogrammes des tables de base pour estimer la sélectivité de jointure, puis nous prenons en compte la sélectivité des prédicats.

Utiliser le Magasin des requêtes pour évaluer la version de l’estimateur de cardinalité

À compter de SQL Server 2016 (13.x), le Magasin des requêtes est un outil pratique pour examiner les performances de vos requêtes. Une fois le Magasin des requêtes activé, celui-ci commence à suivre les performances des requêtes dans le temps, même si les plans d’exécution viennent à changer. Supervisez le Magasin des requêtes afin de détecter les éventuelles requêtes coûteuses ou régressées. Pour plus d’informations, consultez Analyse des performances à l’aide du magasin de requêtes.

Si vous préparez une mise à niveau vers SQL Server ou favorisez un niveau de compatibilité de base de données dans n’importe quelle plateforme SQL Server, envisagez de mettre à niveau des bases de données à l’aide de l’Assistant Paramétrage des requêtes, qui peut vous aider à comparer les performances des requêtes dans deux niveaux de compatibilité différents.

Important

Vérifiez que la magasin des requêtes est correctement configuré pour votre base de données et votre charge de travail. Pour plus d’informations, consultez Bonnes pratiques avec le magasin des requêtes.

Utiliser des événements étendus pour évaluer la version de l’estimateur de cardinalité

Une autre option pour le suivi du processus d’estimation de carte inalité consiste à utiliser l’événement étendu nommé query_optimizer_estimate_cardinality. L’exemple de code Transact-SQL suivant s’exécute sur SQL Server. Il écrit un fichier .xel dans C:\Temp\ (vous pouvez changer ce chemin). Lorsque vous ouvrez le fichier .xel dans Management Studio, ses informations détaillées s’affichent de manière conviviale.

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;  
go  
  
CREATE EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
ADD EVENT sqlserver.query_optimizer_estimate_cardinality  
 (  
 ACTION (sqlserver.sql_text)  
  WHERE (  
  sql_text LIKE '%yourTable%'  
  and sql_text LIKE '%SUM(%'  
  )  
 )  
ADD TARGET package0.asynchronous_file_target
 (SET  
  filename = 'c:\temp\xe_qoec_1.xel',  
  metadatafile = 'c:\temp\xe_qoec_1.xem'  
 );  
GO  
  
ALTER EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
STATE = START;  --STOP;  
GO

Remarque

L’événement sqlserver.query_optimizer_estimate_cardinality n’est pas disponible pour Azure SQL Database.

Pour plus d’informations sur les événements étendus adaptés à SQL Database, consultez Les événements étendus dans SQL Database.

Étapes d’évaluation de la version d’estimation de la cardinalité

Les étapes suivantes permettent d’évaluer si l’une de vos requêtes importantes est moins performante avec la dernière version de l’estimateur de cardinalité. Certaines étapes sont effectuées en exécutant l’exemple de code présenté dans la section précédente.

  1. Ouvrez SQL Server Management Studio (SSMS). Vérifiez que le niveau de compatibilité de votre base de données SQL Server est configuré sur la valeur maximale.

  2. Effectuez les étapes préliminaires suivantes :

    1. Ouvrez SQL Server Management Studio (SSMS).

    2. Exécutez le code Transact-SQL pour vérifier que le niveau de compatibilité de votre base de données SQL Server est configuré sur la valeur de compatibilité disponible maximale.

    3. Vérifiez dans votre base de données que la configuration LEGACY_CARDINALITY_ESTIMATION est désactivée.

    4. Supprimez le contenu de votre magasin de requêtes. Dans votre base de données, vérifiez que Magasin des requêtes est activé.

    5. Exécutez l’instruction suivante : SET NOCOUNT OFF;

  3. Exécutez l’instruction suivante : SET STATISTICS XML ON;

  4. Exécutez votre requête importante.

  5. Dans le volet de résultats, sous l’onglet Messages , notez le nombre de lignes affectées.

  6. Dans le volet de résultats de l’onglet Résultats , double-cliquez sur la cellule qui contient les statistiques au format XML. Un plan de requête graphique s’affiche.

  7. Cliquez avec le bouton de droite sur la première zone du plan de requête graphique, puis sélectionnez Propriétés.

  8. Notez les valeurs des propriétés suivantes en vue de les comparer ultérieurement avec une configuration différente :

    • CardinalityEstimationModelVersion.

    • Nombre de lignes estimé.

    • Coût E/S estimé, et plusieurs propriétés estimées similaires qui impliquent des performances réelles plutôt que des prédictions de nombre de lignes.

    • Opération logique et Opération physique. Parallélisme est une bonne valeur.

    • Mode d’exécution réel. Lot est une bonne valeur, meilleure que Ligne.

  9. Comparez le nombre estimé de lignes au nombre réel de lignes. Le pourcentage d’inexactitude de l’estimation de la cardinalité est-il de 1 % (haut ou bas) ou de 10 % ?

  10. Exécutez la commande suivante : SET STATISTICS XML OFF;

  11. Exécutez le code Transact-SQL pour baisser d’un niveau le niveau de compatibilité de votre base de données (par exemple, de 130 à 120).

  12. Exécutez à nouveau toutes les étapes non préliminaires.

  13. Comparez les valeurs des propriétés de l’estimation de la cardinalité des deux exécutions.

    • Le pourcentage d’inexactitude est-il moins élevé avec la nouvelle estimation de la cardinalité ?

  14. Enfin, comparez les différentes valeurs de propriétés de performances des deux exécutions.

    • Votre requête a-t-elle utilisé un plan différent pour les deux estimations ?

    • Votre requête s’est-elle exécutée plus lentement sous la dernière version de l’estimation ?

    • À moins que votre requête ne s’exécute mieux et avec un autre plan sous l’ancienne version de l’estimation de la cardinalité, il est préférable d’utiliser la dernière version.

    • Toutefois, si votre requête s’exécute avec un plan plus rapide sous l’ancienne version de l’estimation de la cardinalité, envisagez de forcer le système à utiliser le plan plus rapide et à ignorer l’estimation de la cardinalité. Ainsi, vous pourrez disposer de la dernière version de l’estimation de la cardinalité, tout en gardant le plan plus rapide pour les cas exceptionnels.

Comment activer le meilleur plan de requête

Supposez qu’avec l’estimation de la cardinalité 120 ou plus, un plan de requête moins efficace est généré pour votre requête. Voici quelques options permettant d’activer le plan optimal, classées de l’étendue la plus grande à la plus petite :

  • Vous pouvez définir le niveau de compatibilité de la base de données sur une valeur inférieure à la dernière disponible pour l’ensemble de votre base de données.

    • Par exemple, définir le niveau de compatibilité à une valeur inférieure ou égale à 110 active l’estimation de la cardinalité 70, mais rend toutes les requêtes soumises au modèle d’estimation de la cardinalité précédent.

    • De plus, si vous définissez un niveau de compatibilité inférieur, vous ne bénéficierez pas d’un certain nombre d’améliorations de l’optimiseur de requête pour les versions les plus récentes, et cela affectera toutes les requêtes que vous enverrez à la base de données.

  • Vous pouvez utiliser l’option LEGACY_CARDINALITY_ESTIMATION de configuration délimitée à la base de données pour que l’ensemble de la base de données utilise l’ancienne VERSION, tout en conservant d’autres améliorations dans l’optimiseur de requête.

  • Vous pouvez utiliser l’indicateur de requête LEGACY_CARDINALITY_ESTIMATION pour faire en sorte qu’une requête spécifique utilise l’ancienne estimation de la cardinalité, tout en conservant les autres améliorations de l’optimiseur de requête.

  • Si vous souhaitez qu’une requête utilise l’ancien estimateur de cardinalité, vous pouvez appliquer LEGACY_CARDINALITY_ESTIMATION via l’indicateur du Magasin des requêtes sans avoir à modifier cette requête.

  • Forcez un autre plan avec le Magasin des requêtes.

Niveau de compatibilité de la base de données

Vous pouvez garantir le niveau de votre base de données en utilisant le code Transact-SQL suivant pour COMPATIBILITY_LEVEL.

Important

Les numéros de version du moteur de base de données pour SQL Server et Azure SQL Database ne sont pas comparables les uns aux autres. Il s’agit plutôt de numéros de build internes pour ces produits distincts. Le moteur de base de données pour Azure SQL Server est basé sur la même base de code que le moteur de base de données SQL Server. Plus important encore, le moteur de base de données dans Azure SQL Database a toujours les bits les plus récents du moteur de base de données SQL. La version 12 de Azure SQL Database est plus récente que la version 15 de SQL Server. À partir de novembre 2019, dans Azure SQL Database, le niveau de compatibilité par défaut est 150 pour les bases de données nouvellement créées. Microsoft ne met pas à jour le niveau de compatibilité de la base de données pour les bases de données existantes. Il incombe aux clients de le faire à leur convenance.

SELECT ServerProperty('ProductVersion');  
GO  

SELECT d.name, d.compatibility_level  
FROM sys.databases AS d  
WHERE d.name = 'yourDatabase';  
GO

Pour les bases de données préexistantes s’exécutant à des niveaux de compatibilité inférieurs, tant que l’application n’a pas besoin d’utiliser des améliorations disponibles uniquement dans un niveau de compatibilité de base de données supérieur, il s’agit d’une approche valide pour maintenir le niveau de compatibilité de la base de données précédent. Pour un nouveau travail de développement ou quand une application existante exige l’utilisation de nouvelles fonctionnalités comme le Traitement de requêtes intelligent ou un nouveau Transact-SQL, envisagez de mettre à jour le niveau de compatibilité de base de données vers la dernière version disponible. Pour plus d’informations, consultez Niveaux de compatibilité et mises à niveau du moteur de base de données.

Attention

Avant de modifier le niveau de compatibilité de la base de données, lisez Bonnes pratiques pour la mise à niveau du niveau de compatibilité de base de données.

ALTER DATABASE <yourDatabase>  
SET COMPATIBILITY_LEVEL = 150;  
GO

Pour une base de données SQL Server définie au niveau de compatibilité 120 ou supérieur, l’activation de l’indicateur de trace 9481 force le système à utiliser la version 70 de l’estimation de la cardinalité.

Estimateur de cardinalité héritée

Pour un ensemble de bases de données SQL Server au niveau de compatibilité 120 et versions ultérieures, l’estimateur de carte inalité hérité (CE version 70) peut être activé au niveau de la base de données à l’aide de la configuration ALTER DATABASE SCOPED CONFIGURATION.

ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;  
GO  
  
SELECT name, value  
FROM sys.database_scoped_configurations  
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';  
GO

Modifier la requête pour utiliser l’indicateur

À compter de SQL Server 2016 (13.x) SP1, vous devez modifier la requête pour utiliser l’indicateur de requêteUSE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION').

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  

Définir un indicateur du Magasin des requêtes

Les requêtes peuvent être forcées d’utiliser l’estimateur de carte inalité héritée sans modifier la requête, à l’aide de Magasin des requêtes indicateurs.

  1. Identifiez la requête dans les affichages catalogue sys.query_store_query_text et sys.query_store_query du Magasin des requêtes. Par exemple, recherchez une requête exécutée à partir d’un fragment de texte :

    SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
INNER JOIN sys.query_store_query q ON 
qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'  
AND query_sql_text not like N'%query_store%';

  2. L’exemple suivant applique un indicateur de Magasin des requêtes pour forcer l’estimateur de carte inalité hérité sur query_id 39, sans modifier la requête :

    EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

Remarque

Pour plus d’informations, consultez Indicateurs du Magasin des requêtes (préversion). Actuellement, cette fonctionnalité est disponible uniquement dans Azure SQL Database.

Comment forcer un plan de requête

Pour un contrôle accru, vous pouvez forcer le système à utiliser le plan généré avec l’estimation de la cardinalité 70 durant vos tests. Après avoir épinglé votre plan par défaut, vous pouvez configurer votre base de données dans son intégralité pour qu’elle utilise le niveau de compatibilité et l’estimation de cardinalité les plus récents. L’utilisation de cette option est développée plus loin dans cette rubrique.

Le Magasin des requêtes vous propose différentes façons de forcer le système à utiliser un plan de requête donné :

  • Exécutez sys.sp_query_store_force_plan.

  • Dans SQL Server Management Studio (SSMS), développez le nœud de votre Magasin de requêtes, cliquez avec le bouton de droite sur Principaux nœuds consommateurs de ressources, puis sélectionnez Afficher les principaux nœuds consommateurs de ressources. L’affichage montre les boutons Forcer le plan et Annuler l’obligation d’utiliser le plan.

Pour plus d’informations sur le Magasin des requêtes, consultez Analyse des performances à l’aide du Magasin des requêtes.

Constant folding et évaluation des expressions pendant l’estimation de la cardinalité

Le Moteur de base de données évalue rapidement certaines expressions constantes pour améliorer les performances des requêtes. On parle d'assemblage de constantes. Une constante est un littéral Transact-SQL, comme 3, 'ABC', '2005-12-31', 1.0e3 ou 0x12345678. Pour plus d’informations, consultez Constant folding.

Certaines expressions qui n’ont pas fait l’objet d’un repli constant, mais dont les arguments sont connus au moment de la compilation, qu’il s’agisse de paramètres ou de constantes, sont évaluées pendant l’optimisation par l’estimateur de la taille de l’ensemble de résultats (cardinalité) qui est inclus dans l’optimiseur de requête. Pour plus d’informations, consultez Évaluation des expressions.

Bonnes pratiques : utilisation du constant folding et de l’évaluation d’expressions au moment de la compilation en vue de générer des plans de requête optimaux

Pour être sûr de générer des plans de requêtes optimaux, il est préférable de concevoir des requêtes, des procédures stockées et des lots de telle sorte que l’optimiseur de requête puisse estimer correctement la sélectivité des conditions en fonction de statistiques relatives à la distribution de vos données. Dans le cas contraire, l’optimiseur de requête devra utiliser une estimation par défaut lors de l’estimation de la sélectivité.

Pour garantir que l’estimation de cardinalité de l’optimiseur de requête fournira des estimations correctes, vous devez commencer par vérifier que les options SET de base de données AUTO_CREATE_STATISTICS et AUTO_UPDATE_STATISTICS ont la valeur ON (configuration par défaut) ou qu’il existe des statistiques créées manuellement pour toutes les colonnes référencées dans une condition de requête. Ensuite, lorsque vous créez les conditions dans vos requêtes, tenez si possible compte des recommandations suivantes :

  • Évitez l’utilisation de variables locales dans les requêtes. Utilisez plutôt des paramètres, des littéraux ou des expressions dans la requête.

  • Limitez l’utilisation d’opérateurs et de fonctions incorporées dans une requête qui contient un paramètre à ceux répertoriés sous Compilation-Time Expression Evaluation for Cardinality Estimation.

  • Assurez-vous que les expressions constantes uniquement dans la condition de votre requête sont pliables en constantes ou peuvent être évaluées au moment de la compilation.

  • Si vous devez utiliser une variable locale pour évaluer une expression à utiliser dans une requête, envisagez de l’évaluer dans une étendue différente de celle de la requête. Par exemple, il peut être utile d’effectuer l’une des options suivantes :

    • Transmettez la valeur de la variable à une procédure stockée qui contient la requête que vous souhaitez évaluer et que la requête utilise le paramètre de procédure au lieu d’une variable locale.

    • Créez une chaîne contenant une requête basée en partie sur la valeur de la variable locale, puis exécutez cette chaîne via un code SQL dynamique (EXEC ou sp_executesql de préférence).

    • Paramétrisez la requête et exécutez-la à l’aide de sp_executesql, puis passez la valeur de la variable en tant que paramètre à la requête.

Exemples d’améliorations apportées à l’estimation de la cardinalité

Cette section contient des exemples de requêtes qui tirent parti des améliorations implémentées dans les versions récentes de l’estimation de la cardinalité. Il s’agit là d’informations d’arrière-plan qui ne nécessitent pas d’intervention de votre part.

Exemple A. L’estimation de la cardinalité comprend que la valeur maximale peut être plus élevée que lors de la dernière collecte de statistiques

Supposez que la dernière collecte de statistiques pour OrderTable a eu lieu le 2016-04-30, quand la valeur maximale OrderAddedDate était 2016-04-30. L’estimation de la cardinalité 120 (et versions ultérieures) comprend que les colonnes dans OrderTable ayant des données croissantes peuvent avoir des valeurs supérieures aux valeurs maximales enregistrées par les statistiques. Cette compréhension permet d’améliorer le plan de requête pour les instructions SELECT Transact-SQL telles que la suivante.

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01';

Exemple B. L’estimation de la cardinalité comprend que les prédicats filtrés sur la même table sont souvent corrélés

Dans l’instruction SELECT suivante, nous voyons les prédicats filtrés sur Model et ModelVariant. Nous comprenons intuitivement que quand Model est « Xbox », il est possible que ModelVariant soit « One », étant donné que Xbox a une variante appelée One.

À compter de CE 120, SQL Server comprend qu’il peut y avoir une corrélation entre les deux colonnes sur la même table et ModelModelVariant. L’estimation de la cardinalité fait une estimation plus précise du nombre de lignes retournées par la requête, et l’optimiseur de requête génère un plan plus optimal.

SELECT Model, Purchase_Price  
FROM dbo.Hardware  
WHERE Model = 'Xbox' AND  
ModelVariant = 'Series X';

Exemple C. L’estimation de la cardinalité ne suppose plus aucune corrélation entre les prédicats filtrés issus de tables différentes

De nouvelles recherches étendues sur les charges de travail modernes et les données métier ont révélé que les filtres de prédicat issus de différentes tables ne sont généralement pas corrélés. Dans la requête suivante, l’estimation de la cardinalité suppose qu’il n’existe aucune corrélation entre s.type et r.date. Par conséquent, l’estimation de la cardinalité obtient une estimation plus basse que le nombre de lignes retournées.

SELECT s.ticket, s.customer, r.store  
FROM dbo.Sales AS s  
CROSS JOIN dbo.Returns AS r  
WHERE s.ticket = r.ticket AND  
s.type = 'toy' AND  
r.date = '2016-05-11';

Contenu connexe