Estimation de cardinalité (SQL Server)

La logique d’estimation de cardinalité, appelée estimateur de cardinalité, est réécrite dans SQL Server 2014 pour améliorer la qualité des plans de requête et, par conséquent, pour améliorer les performances des requêtes. Le nouvel estimateur de cardinalité intègre des hypothèses et des algorithmes qui fonctionnent bien sur les charges de travail d’entreposage de données et OLTP modernes. Il est basé sur des recherches approfondies d’estimation de cardinalité sur les charges de travail modernes, et nos apprentissages au cours des 15 dernières années d’amélioration de l’estimateur de cardinalité SQL Server. Les commentaires des clients indiquent que, bien que la plupart des requêtes bénéficient de la modification ou restent inchangées, un petit nombre peut afficher des régressions par rapport à l’estimateur de cardinalité précédent.

Recommandations relatives au test et au réglage des performances

Le nouvel estimateur de cardinalité est activé pour toutes les nouvelles bases de données créées dans SQL Server 2014. Toutefois, la mise à niveau vers SQL Server 2014 n’active pas le nouvel estimateur de cardinalité sur les bases de données existantes.

Pour garantir les meilleures performances de requête, utilisez ces recommandations pour tester votre charge de travail avec le nouvel estimateur de cardinalité avant de l’activer sur votre système de production.

1. Mettez à niveau toutes les bases de données existantes pour utiliser le nouvel estimateur de cardinalité. Pour ce faire, utilisez le niveau de compatibilité ALTER DATABASE (Transact-SQL) pour définir le niveau de compatibilité de la base de données sur 120.

2. Exécutez votre charge de travail de test avec le nouvel estimateur de cardinalité, puis résolvez les nouveaux problèmes de performances de la même façon que vous résolvez actuellement les problèmes de performances.

3. Une fois votre charge de travail exécutée avec le nouvel estimateur de cardinalité (niveau de compatibilité de base de données 120 (SQL Server 2014)) et qu’une requête spécifique a régressé, vous pouvez exécuter la requête avec l’indicateur de trace 9481 pour utiliser la version de l’estimateur de cardinalité utilisé dans SQL Server 2012 et versions antérieures. Pour exécuter une requête avec un trace flag, consultez l’article KB Activer le comportement de l’optimiseur de requête SQL Server affectant le plan qui peut être contrôlé par différents trace flags au niveau d’une requête spécifique.

4. Si vous ne pouvez pas modifier toutes les bases de données à la fois pour utiliser le nouvel estimateur de cardinalité, vous pouvez utiliser l’ancien estimateur de cardinalité pour toutes les bases de données à l’aide du niveau de compatibilité ALTER DATABASE (Transact-SQL) pour définir le niveau de compatibilité de la base de données sur 110.

5. Si votre charge de travail s’exécute avec le niveau de compatibilité de base de données 110 et que vous souhaitez tester ou exécuter une requête spécifique avec le nouvel estimateur de cardinalité, vous pouvez exécuter la requête avec l’indicateur de trace 2312 pour utiliser la version SQL Server 2014 de l’estimateur de cardinalité. Pour exécuter une requête avec un indicateur de trace, consultez l’article de la base de connaissances Activer le comportement de l’optimiseur de requête SQL Server affectant le plan qui peut être contrôlé par différents indicateurs de trace au niveau d’une requête spécifique.

Nouveaux événements XEvents

Il existe deux nouveaux query_optimizer_estimate_cardinality XEvents pour prendre en charge les nouveaux plans de requête.

• query_optimizer_estimate_cardinality se produit lorsque l’optimiseur de requête estime la cardinalité sur une expression relationnelle.

• query_optimizer_force_both_cardinality_estimation_behaviors se produit lorsque les deux traceflags 2312 et 9481 sont activés, en tentant de forcer à la fois l’ancien et le nouveau comportement d’estimation de cardinalité en même temps.

Exemples

Les exemples suivants montrent certaines des modifications apportées aux nouvelles estimations de cardinalité. Le code permettant d’estimer la cardinalité a été réécrit. La logique est complexe et il n’est pas possible de fournir une liste exhaustive de toutes les modifications.

Exemple A. Les nouvelles estimations de cardinalité utilisent une cardinalité moyenne pour les données ascendantes récemment ajoutées

Cet exemple montre comment le nouvel estimateur de cardinalité peut améliorer les estimations de cardinalité pour les données croissants qui dépassent la valeur maximale dans la table pendant la dernière mise à jour des statistiques.

SELECT item, category, amount FROM dbo.Sales AS s WHERE Date = '2013-12-19';  

Dans cet exemple, de nouvelles lignes sont ajoutées à la table Sales chaque jour, la requête demande des ventes qui se sont produites le 12/19/2013 et les statistiques ont été mises à jour le 12/18/2013. L’estimateur de cardinalité précédent suppose que les valeurs 12/19/2013 n’existent pas depuis que la date dépasse la date maximale et que les statistiques n’ont pas été mises à jour pour inclure les 12/19/2013 valeurs. Cette situation, appelée problème de clé croissant, se produit si vous chargez des données pendant la journée, puis exécutez des requêtes sur les données avant la mise à jour des statistiques.

Ce comportement a changé. Maintenant, même si les statistiques n’ont pas été mises à jour pour les données croissants les plus récentes ajoutées depuis la dernière mise à jour des statistiques, le nouvel estimateur de cardinalité suppose que les valeurs existent et utilise la cardinalité moyenne pour chaque valeur de la colonne comme estimation de cardinalité.

Exemple B. Les nouvelles estimations de cardinalité supposent que les prédicats filtrés sur la même table ont une corrélation

Pour cet exemple, supposons que la table Cars contient 1000 lignes, Make a 200 correspondances de « Honda », Model a 50 correspondances de « Civic », et que tous les Civic sont des Honda. Par conséquent, 20% des valeurs de la colonne Make sont « Honda », 5% des valeurs de la colonne Model sont « Civic », et le nombre réel de Honda Civics est de 50. Les estimations de cardinalité précédentes supposent que les valeurs dans les colonnes Make et Model sont indépendantes les unes des autres. L’optimiseur de requête précédent estime qu’il y a 10 Honda Civics (.05 * .20 * 1000 lignes = 10 lignes).

SELECT year, purchase_price FROM dbo.Cars WHERE Make = 'Honda' AND Model = 'Civic';  

Ce comportement a changé. À présent, les nouvelles estimations de cardinalité supposent que les colonnes Make et Model ont une corrélation. L’optimiseur de requête estime une cardinalité plus élevée en ajoutant un composant exponentiel à l’équation d’estimation. L’optimiseur de requête estime désormais que 22,36 lignes (.05 * SQRT(.20) * 1000 lignes = 22,36 lignes ) correspondent au prédicat. Pour ce scénario et une distribution de données spécifique, 22,36 lignes sont plus proches des 50 lignes réelles retournées par la requête.

Notez que la nouvelle logique estimateur de cardinalité trie les sélections de prédicat et augmente l’exposant. Par exemple, si les sélections de prédicat étaient .05, .20 et .25, l’estimation de la cardinalité serait (.05 * SQRT(.20) * SQRT(.25)) ).

Exemple C. Les nouvelles estimations de cardinalité supposent que les prédicats filtrés sur différentes tables sont indépendants

Pour cet exemple, l’estimateur de cardinalité précédent suppose que les prédicats filtrent s.type et r.date sont corrélés. Toutefois, les résultats des tests sur les charges de travail modernes ont montré que les filtres de prédicat sur les colonnes de différentes tables ne sont généralement pas corrélés les uns avec les autres.

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 = '2013-12-19';  

Ce comportement a changé. À présent, la nouvelle logique d’estimateur de cardinalité suppose que s.type n’est pas corrélé à r.date. En pratique, l’hypothèse est que les jouets sont retournés tous les jours et non seulement sur une journée spécifique. Dans ce cas, les nouvelles estimations de cardinalité seront un nombre plus petit que les estimations de cardinalité précédentes.

Voir aussi

Surveiller et régler pour la performance