Merci à nos sponsors

2. Merci à nos sponsors

3. New Cardinality Estimation Incremental Statistics SQL Server 2014 Fred Pichaut

4. New Cardinality Estimation

5. L’exécution des requêtes • L’exécution des requêtes et un composent de plus en plus critique • Un degré de complexité de plus en plus grand avec l’augmentation du volume des données • L’optimisation des requêtes doit déterminer le chemin le plus efficace avec des workloads très différents • Avoir une performance prédictible est très dure!

6. Les bases du Query Optimization • Un plan est mesuré en terme de cout (« cost ») • Tous va dépendre des estimations de cardinalité (« Cardinality Estimation » ou CE) • L’optimisation des requêtes est par nature imprévisibles et très sensible aux estimation des cardinalité • Les cout d’un plan est principalement impacté par le nombre d’enregistrement traité par chaque étapes ou operateurs du plan • L’optimisation est critique avec les workloads actuels

7. Les Challenges du Développement • SQL Server Cardinality Estimation est un chalange • Les problèmes des clients sont difficiles a supporter • Fixer ces problèmes est complexe pour l’engineering • Il n’y a pas de moyen simple pour fixe les bugs ou de nettoyer le code • Cette partie du code doit être réécrite et retesté • Des changements importants dans le design • Une nouvelle stratégie de testes • Des changements de design complexe • Le model mathématique des estimations • Le design du code

8. Les Objectifs • Améliorer la fiabilité de l’estimation des cardinalités pour un large éventail de requêtes et workloads, OLTP, DW et DS • Améliorer les performances moyennes • Avoir des performances plus lisses et plus prédictibles • Avoir deux version du CE • Eviter les régressions • Pouvoir utiliser l’ancien CE en cas de régressions

9. Scope • Améliorer les éléments du CE associé avec les operateur logique « non-leaf » • Inner, outer, semi, et anti-semi jointures • Group By, Distinct, Union • Améliorer la fiabilité ua respect du model • Uniformité, Indépendance, confinement • Faciliter les diagnostiques des problèmes de cardinalité • Améliorer la maintenabilité du code de base • Pas de régression lors d’un fixe

10. Pas encore dans le scope • Rester raisonnable quand à la faisabilité • Pas d’investissement certain problèmes connus sur les « leaf-level » • Pas de multi-columnhistogrames • Table-valued fonctions • Variables de type table • Table-valuedparameters • Variables locales

11. Picasso Database Optimizer Visualizer • De Database Systems Lab, Indian Institute of Science • Un outils de visualisation graphique • Visualiser et analyser le comportement des optimiseurs • Operationel pour plusieurs moteurs • Microsoft SQL Server • IBM DB2 • Oracle • Sybase • PostgreSQL

12. SQL Server 2008 R2 Prototype with new CE Estimation du cout pour requêtes à 2 variables Le cout ne doit pas diminuer alors que le nombre d’enregistrements retourné augmente

13. Diagramme Picasso la même requête SQL Server 2008 R2 Prototype with new CE Moins de plans générés avec le nouveau CardinalityEstimator

14. Le Nouveau Model Mathématique (1) Hypothèse d’uniformité: • Dans chaque palier d’histogramme les valeurs distinctes sont équidistantes et ont la même fréquence.

15. Le Nouveau Model Mathématique (2) Hypothèse de confinement: Les requêtes concernent des données qui existent. • Pour un prédicat Column-Equal-Constant, nous supposons que la constante existe dans le colonne. • Pour une requête equijoin sur deux tables, nous supposons que sur la colonne de jointure les valeurs d’un coté de la jointure existent de l’autre coté.

16. Le Nouveau Model Mathématique (3) Hypothèse d’indépendance: Les données de différentes colonnes sont distribuées de façon indépendantes • Pour la sélectivité (Sel) • Suivant deux prédicats P, Q qui n’impliquent pas les même colonnes Sel(P ^ Q) = Sel(P) * Sel(Q) • Pour le nombre de valeurs distinctes (NDV) • Suivant deux colonnes c1, c2 NDV(c1, c2) = NDV(c1) * NDV(c2) Sauf si une stats multi-colonnes indique une autre valeur

17. Exemples Prédicat column-equal-constant Select * from T where T.c1 = 50 La valeur 50 existe dans l’histogramme (confinement) • La fréquence de 50 est la fréquence moyenne du palier dans l’histogramme (uniformité) Prédicat de type Range (interval) Select * from T where T.c1 > 35 and T.c1 < 50 • Interpolation linéaire pour estimer le # de lignes et # Valeurs distinctes dans l’interval (35, 50) (uniformité)

18. Exemples Estimer le sélectivité d’un equijoin Select * from T1 join T2 on T1.c1 = T2.c2 = • On calcule la cardinalité de la jointure en combinant les histogrammes de T1.c1 et T2.c2:

19. Qu’est-ce qui a été fait? • Essai de différentes variations du model • Testé sur les benchmarks et workloads client • DW benchmarks • OLTP benchmarks • Des douzaines de workloads clients • Choix des variations qui ont démontrées, en moyenne, les meilleurs performances

20. Qu’est-ce qui à changé: Exponential back-off vs. Independence Variations du model de corrélation avec différentes colonnes. Conjonction de prédicats sur une table, avec comme sélectivité: . • Indépendence: Ancien CE • Functionaldependency: • Exponential back-off: Nouveau CE

21. Simplification de l’algorithme de jointure Estimation de jointure – Ancien CE • Equijoin avec un prédicat simple select * from T1 join T2 on T1.c1 = T2.c2 • Alignement des paliers des histogrammes avant de les combiner • C’est une cause d’inconsistance dans l’ancien CE • Le logique derrière est trop compliqué à expliquer

22. Simplification de l’algorithme de jointure Estimation de jointure – Nouveau CE • Equijoin avec un prédicat simple select * from T1 join T2 on T1.c1 = T2.c2 Alignement des histogrammes par les valeurs Min/Max • Equijoin avec un prédicat multiple select * from T1 join T2 on T1.c1=T2.c2 and T1.d1=T2.d2 On prend le plus petit des nombres de valeurs distinctes entre les deux prédicats et on le multiplie par leur fréquence moyenne • Non-equijoin select * from T1 join T2 on T1.c1 < T2.c2 On prend la cardinalité du coté le plus grand des prédicats.

23. Exemple: Jointure simple sur deux colonnes • Ancien CE:  L’optimiseur prend les paliers dans les histogrammes en les associe un par un. • Nouveau CE:  L’optimiseur utilise une méthode plus grossière.  L’optimiseur regroupe en premier les histogrammes en une palier unique et ensuite les associe. • Deux Trace Flags disponibles • 9481: Force l’ancien CE • 2312: Force le nouveau CE (par default) select * fromFactResellerSalesfsjoinFactCurrencyRatefc on fs.CurrencyKey = fc.CurrencyKey

24. Problème de clé ascendante • Qu’est ce que le problème de clé ascendante? • Les données sont ascendantes • Les nouvelles donnés ne sont pas dans l’histogramme • Comment le nouveau CE le solutionne? • Toujours supposer que les valeurs demandées existe • Estime la cardinalité en utilisant la fréquence moyenne • Les même supposition sont prisent pour les « missing values » dans des statistiques échantillonnées

25. Architecture Division de Cardinality Estimation en deux étapes • Étapes 1: Planning Trouver un « cardinalitycalculator » pour les paramètres • Étapes 2: Exécution Execition des « calculator » Bénéfices • Meilleur supportabilité • Maintenance et extension plus facile à intégrer

26. Le futur • Adresser les problèmes de « leaf-level » • Multi-columnstatspour les prédicats corrélés • Table-valuedfunctions • Table variables • Table-valuedparameters • Local variables

27. IncrementalStatistics

28. Les Statistiques dans SQL Server • Utilisées par l'optimiseur pour évaluer la sélectivité des expressions, et donc la taille des résultats intermédiaires et finaux • Elles peuvent être: • Crées automatiquement ou manuellement • Mises à jour automatiquement ou manuellement • Mises à jour en synchrone ou en asynchrone • Basées sur un échantillonnage de valeurs ou toutes les valeurs • Le plus elles sont à jour, le meilleur c’est. • Mises à jour automatiquement est déclenchée quand au moins 20% des données de la table ont évoluées

29. Problème client • Pour une tables avec des millions de lignes, il peut prendre des jours voir des mois avant d’atteindre le seuil de 20% • Si une nouvelle partition est ajoutée à la table et que ça ne modifie pas 20% de celle-ci, les statistiques ne sont pas mise à jour et il n’y a pas d’information sur cette nouvelle partition. • Une mise à jour manuelle des statistiques peut être déclenché mais elle va échantillonner toute la table ce qui peut être trop long.

30. Les Statistiques Incrémentales • Objectif: • Mise à jour plus rapide sur des tables avec de large partition • Des mises à jour automatiques plus fréquentes • La cible est les tables partitionnées • Une page de statistique par partition • Merge binaire des statistiques de chaque partition pour créer une statistique globale • L’ensemble des pages sont persistante sur disque. • La mise à jour peut être globale ou indépendante par partitions • (500 + 20% de la taille moyenne des partitions) pour la mise à jour de la stat globale • 20% de modification dans une partition -> Auto Stat

31. Exemples • Sur une table avec 4 partitions • Ajout d’une 5eme partition

32. Management • Create index withincrementalstatistics CREATE INDEX idx ON tbl (x, y) withSTATISTICS_INCREMENTAL=ON • Createincrementalstatistics CREATE STATISTICS stat ON tbl (x, y) withINCREMENTAL=ON • Update statistics on a subset of partitions UPDATE STATISTICS tbl (stat) withRESAMPLE ON PARTITIONS (1,3,5) • Enable/Disableincremental for an existingstatistics UPDATE STATISTICS tbl (stat) withINCREMENTAL= ON • Enable auto createdstatistics to beincremental ALTER DATABASE db SET INCREMENTAL ON

34. Vous pouvez respirer, c’est fini… Des questions? Le Support Microsoft