Reconstruire tous les index chaque nuit est souvent considéré comme une mauvaise pratique sur SQL Server, car cela part du principe que tous les index se dégradent au même rythme et nécessitent systématiquement une maintenance. En réalité, ce n’est généralement pas le cas.
Voici les principales raisons.
1. Cela consomme beaucoup de ressources pour un bénéfice souvent faible
Une reconstruction (ALTER INDEX ... REBUILD) :
- lit et réécrit l’intégralité de l’index ;
- génère beaucoup d’E/S disque (I/O) ;
- consomme du CPU ;
- utilise de l’espace dans le journal des transactions (transaction log) ;
- peut augmenter les sauvegardes de journaux en mode de récupération Full.
Si un index n’est fragmenté qu’à 2 ou 3 %, le gain de performance sera généralement imperceptible.
2. La fragmentation n’est pas toujours un problème
La fragmentation interne ou externe n’a un impact significatif que dans certains cas :
- gros index ;
- nombreuses lectures séquentielles (scan) ;
- stockage sur disque traditionnel.
Sur des SSD modernes, l’impact de la fragmentation est souvent beaucoup plus faible qu’autrefois.
3. Les petits index n’ont quasiment rien à gagner
Un index de quelques centaines de pages est souvent lu entièrement en mémoire.
Même avec 50 % de fragmentation, la différence de performance est souvent négligeable.
4. La reconstruction met à jour les statistiques… parfois inutilement
Un REBUILD :
- supprime la fragmentation ;
- met à jour les statistiques avec un échantillonnage complet.
Mais si les statistiques étaient déjà correctes, ce travail supplémentaire n’apporte rien.
5. Cela peut perturber les performances des utilisateurs
Selon l’édition de SQL Server et les options utilisées :
- des verrous peuvent être pris ;
- des blocages peuvent apparaître ;
- les réplications, groupes de disponibilité ou sauvegardes peuvent être impactés.
Même avec ONLINE = ON, il reste un coût non négligeable.
6. La fragmentation revient rapidement
Certaines tables très actives (journalisation, commandes, événements…) retrouvent leur fragmentation en quelques heures.
Reconstruire chaque nuit revient parfois à effectuer un travail coûteux qui sera rapidement annulé.
7. Tous les index n’ont pas le même besoin
Par exemple :
- Une table de référence modifiée une fois par mois n’a presque jamais besoin d’une reconstruction.
- Une table d’historique en insertion uniquement ne se fragmente presque pas.
- Une table OLTP très sollicitée peut nécessiter une maintenance plus fréquente.
Une stratégie uniforme est donc rarement optimale.
8. On risque de masquer un vrai problème
Une fragmentation importante peut être le symptôme de :
- un mauvais facteur de remplissage (Fill Factor) ;
- des clés aléatoires (GUID) ;
- des mises à jour importantes ;
- une mauvaise conception de l’index.
Reconstruire les index chaque nuit traite le symptôme plutôt que la cause.
Une meilleure stratégie
La plupart des DBA privilégient une maintenance conditionnelle.
Par exemple :
- ne rien faire si la fragmentation est inférieure à 5 % ;
- effectuer un
REORGANIZEentre 5 % et 30 % ; - effectuer un
REBUILDau-delà de 30 %.
Ces seuils sont des recommandations courantes, pas des règles absolues. Ils doivent être adaptés à la taille des index, au type de charge (OLTP, décisionnel, mixte) et aux performances observées.
Encore mieux : mesurer avant d’agir
Une bonne stratégie consiste à :
- mesurer la fragmentation avec
sys.dm_db_index_physical_stats; - ignorer les très petits index ;
- reconstruire uniquement les index réellement concernés ;
- surveiller les gains obtenus plutôt que de suivre un calendrier fixe.
En résumé
Reconstruire tous les index chaque nuit est souvent une mauvaise idée parce que cela :
- gaspille du CPU, des E/S et de l’espace dans le journal des transactions ;
- augmente les temps de maintenance ;
- apporte peu de bénéfices sur de nombreux index ;
- peut perturber les utilisateurs ;
- ne corrige pas les causes de la fragmentation.
Une maintenance basée sur l’état réel des index, leur taille et leur utilisation est généralement plus efficace et moins coûteuse qu’une reconstruction systématique quotidienne.