Estimation de la cardinalité (SQL Server)

S’applique à : SQL Server Azure SQL Database Azure 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 la cardinalité (CE) dans SQL Server est dérivée principalement d’histogrammes générés lors de la création manuelle ou automatique d’index ou de statistiques. Parfois, SQL Server utilise également des informations de contraintes et des réécritures logiques de requêtes pour déterminer la cardinalité.

Dans les cas suivants, SQL Server ne peut pas calculer les cardinalités avec précision. Il en résulte des calculs de coût imprécis qui peuvent aboutir à des plans de requête non optimisés. La non-utilisation de ces constructions dans les requêtes peut améliorer les performances de celles-ci. 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 utilisant des opérateurs et pour lesquelles l’une des conditions 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, SQL Server 7.0 a bénéficié d’une mise à jour majeure de l’estimateur de cardinalité (CE), pour lequel le niveau de compatibilité est défini à 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 ¹ 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.

  ¹ Nombre de lignes satisfaisant au prédicat.

Les mises à jour suivantes ont commencé avec SQL Server 2014 (12.x), ce qui signifie que les niveaux de compatibilité sont de 120 et au-delà. 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 :

• Indépendance devient Corrélation : La combinaison des valeurs de différentes colonnes n’est pas nécessairement indépendante. Cela peut ressembler davantage à une interrogation de données 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é

À partir 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.

Lors d’une préparation de mise à niveau de SQL Server ou d’une modification du niveau de compatibilité d’une base de données sur n’importe quelle plateforme SQL Server, envisagez d’utiliser l’Assistant Paramétrage de requêtes pour la mise à niveau des bases de données. Cet outil permet de comparer les performances des requêtes selon deux niveaux de compatibilité différents.

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

Une autre option pour suivre le processus d’estimation de la cardinalité 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  

Pour plus d’informations sur les événements étendus spécifiques à Azure SQL Database, consultez Événements étendus dans Azure 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 le 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 une option de configuration au niveau de la base de données LEGACY_CARDINALITY_ESTIMATION pour forcer l’ensemble de la base de données à utiliser l’ancien estimateur de cardinalité (CE), tout en conservant les autres améliorations apportées à 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.

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 existantes fonctionnant avec des niveaux de compatibilité plus anciens, tant que l’application n’a pas besoin d’utiliser des améliorations disponibles uniquement dans un niveau de compatibilité plus élevé de la base de données, il est tout à fait possible de conserver le niveau de compatibilité précédent de la base de données. 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.

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

Dans une base de données SQL Server dont le niveau de compatibilité est défini à 120 ou supérieur, l’estimateur de cardinalité hérité (version CE 70) peut être activé au niveau de la base de données à l’aide de l’instruction 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

À partir de SQL Server 2016 (13.x) SP1, modifiez la requête pour utiliser l’indicateur de requête USE 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

Dans SQL Server, il est possible de forcer les requêtes à utiliser l’estimateur de cardinalité hérité sans modifier la requête, grâce aux indicateurs du Magasin des requêtes.

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 illustre l’application d’un indicateur du Magasin des requêtes pour forcer l’estimateur de cardinalité hérité sur query_id 39, sans modifier la requête elle-même :

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

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 quelques expressions constantes à l’avance 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 d’utiliser des variables locales dans les requêtes. Utilisez plutôt des paramètres, des littéraux ou des expressions dans la requête.

• Dans une requête paramétrée, limitez l’utilisation des opérateurs et des fonctions intégrés à la requête à ceux répertoriés dans la documentation sur l’évaluation des expressions au moment de la compilation pour l’estimation de cardinalité.

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

• Si vous devez utiliser une variable locale pour évaluer une expression destinée à une requête, envisagez de l’évaluer dans un autre étendue que la requête. Par exemple, l’une des options suivantes peut s’avérer utile :

  • Transférez la valeur de la variable à une procédure stockée contenant la requête à évaluer. La requête devra alors utiliser le paramètre de la procédure à la place 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.

À partir de la version CE 120, SQL Server peut prendre en compte une corrélation possible entre deux colonnes d’une même table, Model et ModelVariant. 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

• Retour d'expérience sur l'estimation de la cardinalité (CE)
• Surveillance et réglage des performances
• Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator (Optimiser vos plans de requêtes avec l’Estimateur de la cardinalité de SQL Server 2014)
• Indicateurs (Transact-SQL) - Requête
• Indicateurs de requête USE HINT
• Mettre à niveau des bases de données en utilisant l’Assistant Paramétrage de requêtes
• Superviser le niveau de performance avec le Magasin des requêtes
• Guide d’architecture de traitement des requêtes
• Indicateurs du Magasin des requêtes
• Traitement de requêtes intelligent dans les bases de données SQL
• DBCC TRACEON – Indicateurs de trace (Transact-SQL)
• sys.query_store_query_hints