Récemment, j’ai publié un sondage sur LinkedIn adressé à des professionnels travaillant avec SQL Server dans des environnements VLDB :
« Lorsque vous disposez d’une fenêtre de maintenance courte et que vous devez hiérarchiser les actions pour améliorer la performance de l’environnement, qu’est-ce qui apporte le plus de résultats ? »
Le résultat a fortement retenu l’attention :
85,7 % ont voté pour la mise à jour des statistiques
14,3 % ont voté pour la reconstrution/ réorganisation
Cela montre une évolution importante de la maturité des opérations de bases de données.
Pendant de nombreuses années, la reconstruction d’index est devenue pratiquement une « recette standard » pour tout problème de performance. Dans de nombreux environnements, les travaux de maintenance exécutent automatiquement une reconstruction sans même évaluer si cet index nécessite réellement l’opération.
Mais dans les environnements VLDB (Very Large Databases), cette approche n’est pas toujours la plus efficace et bien souvent le problème réel réside dans des statistiques obsolètes.
Le point central est simple :
SQL Server prend des décisions basées sur des estimations.
Et ces estimations proviennent des statistiques.
Qu’est-ce qui impacte réellement le plan d’exécution ?
L’Optimiseur de requêtes utilise les statistiques pour estimer :
- la cardinalité;
- la sélectivité;
- la distribution des données;
- le nombre de lignes attendues;
- le coût des opérateurs.
Si ces informations sont incorrectes, l’optimiseur peut :
- choisir une Nested Loop au lieu d’un Hash Join ;
- utiliser un Index Scan alors qu’il aurait fallu effectuer un Seek ;
- générer des spills dans tempdb ;
- errer le parallélisme ;
- consommer la mémoire de façon inappropriée ;
- créer des memory grants incorrects.
Autrement dit :
Même avec des index « parfaits », de mauvaises statistiques continuent de générer de mauvais plans.
La propre Microsoft souligne que les statistiques sont fondamentales pour l’estimation de la cardinalité et la génération de plans efficaces.
L’optimiseur ne lit pas toute la table pour décider d’un plan ; il utilise les histogrammes et les densités contenus dans les statistiques pour prévoir combien de lignes seront renvoyées à chaque étape du plan.
Et voici le grand problème :
Lorsque la distribution des données change et que les statistiques ne suivent pas ce changement, surgissent les problèmes classiques :
- estimation de 1000 lignes retournant 10 millions ;
- opérateurs inappropriés ;
- plans régressifs ;
- parallélisme mal adapté ;
- consommation excessive de mémoire ;
- dégradation générale des requêtes.
En VLDB, cela devient encore plus critique, car de petites erreurs d’estimation peuvent se transformer en véritables catastrophes de performance.
Le mythe de la REBUILD comme solution universelle
De nombreux DBAs exécutent une reconstruction en espérant obtenir un gain de performance, mais ils oublient que la reconstruction améliore surtout :
- la fragmentation logique ;
- l’organisation physique des pages ;
- la densité des pages.
Cela aide principalement les charges de travail avec un volume élevé de lectures séquentielles.
Cependant, dans bien des cas de dégradation des performances, le problème ne réside pas dans l’accès physique aux données, mais dans l’optimiseur qui prend de mauvaises décisions. Et cela est généralement lié aux statistiques.
Dans la pratique, un index extrêmement fragmenté peut produire un plan optimal si l’optimiseur dispose de bonnes estimations. À l’inverse, un index parfaitement organisé peut continuer à dégrader la performance si les statistiques sont obsolètes.
C’est le point que beaucoup d’environnements commencent à remarquer lorsqu’ils montent en taille, atteignant plusieurs téraoctets.
Un détail important sur la reconstruction et les statistiques
À noter : la reconstruction d’un indice met à jour les statistiques de cet indice en tant qu’effet secondaire, ce qui peut créer une fausse impression de résoudre les deux problèmes en même temps, mais elle ne met pas à jour les statistiques des colonnes sans index, ni des index qui sont sortis de la fenêtre de maintenance. Dans les environnements VLDB, utiliser la reconstruction comme substitut à une politique de mise à jour des statistiques est un compromis défavorable dans la majorité des scénarios.
Le coût opérationnel de la reconstruction en VLDB
Dans des bases de données de petite taille, la reconstruction peut sembler inoffensive.
Mais dans VLDB, le scénario change radicalement.
Une reconstruction peut générer :
- un volume énorme du journal de transactions ;
- une croissance excessive du fichier de log ;
- pression sur les entrées/sorties (IO) ;
- utilisation CPU fortement accrue ;
- consommation intensive de tempdb ;
- verrouillages ;
- impact sur les répliques Always On ;
- augmentation du temps de sauvegarde des journaux ;
- longues fenêtres de maintenance.
De plus, la reconstruction d’index volumineux peut prendre des heures.
Pendant ce temps, l’UPDATE STATISTICS bien ciblé apporte fréquemment une amélioration perceptible en quelques minutes et avec un coût opérationnel bien moindre.
Dans de nombreux cas, la mise à jour des statistiques des tables critiques génère un impact positif immédiat sur l’Optimiseur sans nécessiter la reconstruction d’index massifs.
Le Cardinality Estimator et sa dépendance vis-à-vis des statistiques
Le Cardinality Estimator (CE) est l’un des composants les plus importants de SQL Server. Il est chargé d’estimer le nombre de lignes qui seront retournées par chaque opérateur du plan d’exécution — et toute la prise de décision de l’optimiseur dépend de ces estimations.
Lorsque les statistiques sont obsolètes, le CE commence à interpréter l’environnement de manière incorrecte. Cela impacte directement :
- le choix des jointures ;
- l’utilisation du parallélisme ;
- la quantité de mémoire réservée ;
- l’ordre d’accès aux tables ;
- le coût estimé des opérations.
En d’autres termes :
De mauvaises statistiques amènent l’optimiseur à prendre de mauvaises décisions. Et un optimiseur qui prend de mauvaises décisions dégrade la performance bien avant que la fragmentation ne devienne un problème réel.
Le point le plus important : la mise à jour automatique n’est pas toujours suffisante
Beaucoup pensent que le AUTO_UPDATE_STATISTICS résout tout.
Dans les environnements VLDB, ce n’est que rarement vrai et comprendre pourquoi est fondamental.
Le seuil classique exige qu’environ 20 % des lignes soient modifiées pour déclencher la mise à jour automatique. Dans une table de 500 millions d’enregistrements, cela signifie qu’environ 100 millions de modifications devraient avoir lieu pour déclencher le mécanisme. Dans des tables qui reçoivent des charges lourdes mais concentrées, comme des partitions historiques ou des tables de staging, ce seuil peut ne jamais être atteint, même avec des données complètement obsolètes.
À partir de SQL Server 2016 (et du 2014 avec le trace flag 2371), le dynamic auto-update threshold améliore ce comportement en utilisant une échelle sous-linéaire basée sur sqrt(1000 * table_rows). Même ainsi, dans les environnements VLDB à haut volume de données, le seuil dynamique peut encore être insuffisant pour garantir des statistiques à jour sur les tables les plus critiques.
C’est pourquoi les environnements critiques nécessitent généralement :
- une maintenance personnalisée ;
- une mise à jour sélective ;
- une priorisation basée sur le `modification_counter` ;
- une analyse de la charge ;
- un FULLSCAN sur les objets stratégiques ;
- un suivi continu des statistiques les plus sensibles.
La DMV ci-dessous est extrêmement utile pour identifier les statistiques potentiellement désuètes :
SELECT
OBJECT_NAME(s.object_id) AS tabela,
s.name AS estatistica,
sp.last_updated,
sp.rows,
sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.modification_counter > 100000
ORDER BY sp.modification_counter DESC;Cette DMV permet d’identifier combien de modifications ont eu lieu depuis la dernière mise à jour des statistiques, facilitant la priorisation de la maintenance.
Remarque : la valeur 100000 est un point de départ; l’ajustement doit être adapté au débit de votre environnement. Dans des environnements VLDB avec un trafic élevé, envisagez des seuils plus importants et croisez le modification_counter avec last_updated pour prioriser les statistiques qui sont à la fois anciennes et fortement modifiées.
Alors, la reconstruction n’est-elle pas importante ?
Bien sûr que si.
La reconstruction demeure extrêmement importante dans de nombreux scénarios, notamment lorsque l’on observe une dégradation physique significative des index.
Cependant, le marché observe quelque chose d’important :
Dans de nombreux environnements VLDB, les statistiques auront un plus grand impact sur la qualité du plan d’exécution que la fragmentation sur la lecture physique.
La reconstruction doit être envisagée comme une action chirurgicale et non comme une routine universelle pour tous les problèmes de performance.
En pratique :
- Les index facilitent l’accès aux données.
- Les statistiques guident l’optimiseur vers le chemin approprié.
Et dans les environnements VLDB, choisir correctement a généralement beaucoup plus de valeur que de simplement réorganiser les pages.
