Transcript Slides

Les journées
SQL Server 2013
Un événement organisé par GUSS
#JSS2013
Merci à nos sponsors
#JSS2013
Les journées
SQL Server 2013
New Cardinality Estimation
Incremental Statistics
SQL Server 2014
Fred Pichaut
Un événement organisé par GUSS
#JSS2013
NEW CARDINALITY ESTIMATION
#JSS2013
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!
#JSS2013
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
#JSS2013
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
#JSS2013
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
#JSS2013
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
#JSS2013
Pas encore dans le scope
• Rester raisonnable quand à la faisabilité
• Pas d’investissement certain problèmes
connus sur les « leaf-level »
–
–
–
–
–
Pas de multi-column histogrames
Table-valued fonctions
Variables de type table
Table-valued parameters
Variables locales
#JSS2013
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
#JSS2013
Estimation du cout pour requêtes à 2 variables
SQL Server 2008 R2
Prototype with new CE
Le cout ne doit pas diminuer alors que le nombre d’enregistrements
retourné augmente
#JSS2013
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 Cardinality Estimator
#JSS2013
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.
#JSS2013
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é.
#JSS2013
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
#JSS2013
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é)
#JSS2013
Exemples
Estimer le sélectivité d’un equijoin
Select * from T1 join T2 on T1.c1 = T2.c2
|𝑇1 ⋈ 𝑇2|
𝑆𝑒𝑙𝑗𝑜𝑖𝑛 =
𝑇1 ×|𝑇2|
• On calcule la cardinalité de la jointure en combinant les
histogrammes de T1.c1 et T2.c2:
#JSS2013
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
#JSS2013
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é: 𝑝0 ≤ 𝑝1 ≤ 𝑝2 ≤ 𝑝3 ≤ ⋯.
• Indépendence: 𝑝0 × 𝑝1 × 𝑝2 × 𝑝3 × ⋯ Ancien CE
• Functional dependency: 𝑝0
1
1
1
• Exponential back-off: 𝑝0 × 𝑝1 2 × 𝑝2 4 × 𝑝3 8
Nouveau CE
#JSS2013
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
#JSS2013
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.
#JSS2013
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 * from FactResellerSales fs join FactCurrencyRate fc on fs.CurrencyKey = fc.CurrencyKey
#JSS2013
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
#JSS2013
Architecture
Division de Cardinality Estimation en deux étapes
• Étapes 1: Planning
Trouver un « cardinality calculator » pour les paramètres
• Étapes 2: Exécution
Execition des « calculator »
Bénéfices
– Meilleur supportabilité
– Maintenance et extension plus facile à intégrer
#JSS2013
Le futur
• Adresser les problèmes de « leaf-level »
–
–
–
–
–
Multi-column stats pour les prédicats corrélés
Table-valued functions
Table variables
Table-valued parameters
Local variables
#JSS2013
INCREMENTAL STATISTICS
#JSS2013
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
#JSS2013
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.
#JSS2013
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
#JSS2013
Exemples
• Sur une table avec 4 partitions
• Ajout d’une 5eme partition
#JSS2013
Management
• Create index with incremental statistics
CREATE INDEX idx ON tbl (x, y)
with STATISTICS_INCREMENTAL=ON
• Create incremental statistics
CREATE STATISTICS stat ON tbl (x, y)
with INCREMENTAL=ON
• Update statistics on a subset of partitions
UPDATE STATISTICS tbl (stat)
with RESAMPLE ON PARTITIONS (1,3,5)
• Enable/Disable incremental for an existing statistics
UPDATE STATISTICS tbl (stat)
with INCREMENTAL= ON
• Enable auto created statistics to be incremental
ALTER DATABASE db
SET INCREMENTAL ON
#JSS2013
#JSS2013
Vous pouvez respirer, c’est fini…
Des questions?
#JSS2013
#JSS2013
#JSS2013