Maintenance des index et statistiques dans les pools élastiques Azure SQL
Pourquoi faire moins, reconstruction en ligne reprenable, mise à jour des statistiques et planification sur de nombreuses bases sans saturer le pool.
La maintenance des index est une habitude héritée des serveurs on-premises : tout reconstruire le dimanche soir. Sur Azure SQL Database, et plus encore dans les pools élastiques où des centaines de bases partagent le même budget de CPU, d'IO et de journal, cette habitude coûte cher. Voici comment je maintiens index et statistiques aujourd'hui : moins de travail, ciblé, et planifié pour que le pool ne s'en aperçoive pas.
Pourquoi moins, c'est souvent mieux
- Une reconstruction écrit tout l'index dans le journal de transactions. Sur Azure SQL Database, le débit de journal est gouverné par base et par pool : une grosse reconstruction peut ralentir toutes les autres charges (voir l'article sur les wait statistics).
- La fragmentation compte surtout pour les grands scans séquentiels sur disques rotatifs. Sur stockage SSD, un index fragmenté lu par seeks ne coûte presque rien.
- La reconstruction n'apporte rien que les statistiques ne donnent déjà : ce qui améliore vraiment les plans, ce sont des statistiques fraîches, pas un index défragmenté.
- Chaque reconstruction invalide les plans qui utilisent l'index : on repaie de la compilation ensuite.
Ma règle : pas de reconstruction par défaut. Je ne reconstruis que les gros index (plusieurs milliers de pages), très fragmentés, réellement scannés par des requêtes. Je réorganise ou je laisse le reste.
SELECT TOP (50)
OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
OBJECT_NAME(ips.object_id) AS table_name,
i.name AS index_name,
ips.avg_fragmentation_in_percent,
ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.indexes AS i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 5000
AND ips.avg_fragmentation_in_percent > 30
AND i.index_id > 0
ORDER BY ips.page_count DESC;
Le mode LIMITED ne lit que les niveaux supérieurs de l'index : c'est la façon économique de scanner.
Reconstruction en ligne reprenable
Quand une reconstruction se justifie, je la fais en ligne et reprenable. Elle s'applique à Azure SQL Database et permet de s'arrêter après un budget de temps pour continuer la nuit suivante, au lieu de perdre des heures de travail :
ALTER INDEX IX_Orders_Status_Date_Id ON dbo.Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);
-- état des opérations en pause ou en cours
SELECT name, state_desc, percent_complete FROM sys.index_resumable_operations;
ALTER INDEX IX_Orders_Status_Date_Id ON dbo.Orders PAUSE;
ALTER INDEX IX_Orders_Status_Date_Id ON dbo.Orders RESUME;
À retenir de la documentation : RESUMABLE exige ONLINE ; il n'est pas pris en charge avec REBUILD ALL, les index columnstore ou filtrés ; SORT_IN_TEMPDB n'est pas pris en charge ; et pendant la pause l'ancien et le nouvel index occupent de l'espace et sont tous deux mis à jour par les écritures. Ne laissez pas une opération en pause pendant des semaines.
Les statistiques
C'est là que se trouve le vrai gain. La mise à jour automatique est active par défaut, mais son seuil peut laisser de grosses tables périmées longtemps. Je regarde le compteur de modifications et je rafraîchis ce qui a bougé :
SELECT OBJECT_NAME(s.object_id) AS table_name, s.name, sp.rows, sp.modification_counter, sp.last_updated
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
AND sp.modification_counter > 0.1 * sp.rows
ORDER BY sp.modification_counter DESC;
UPDATE STATISTICS dbo.Orders WITH SAMPLE 20 PERCENT;
FULLSCAN seulement sur les rares statistiques qui demandent de la précision ; l'échantillon par défaut suffit dans la plupart des cas.
Planifier sur de nombreuses bases
L'erreur classique est de lancer le job sur toutes les bases en même temps. Dans un pool, la somme des reconstructions tape dans les limites partagées. Mes contraintes :
- Une base à la fois par pool (ou un parallélisme faible et mesuré). Des pools différents peuvent tourner en parallèle.
- Une fenêtre de temps (la nuit), avec un arrêt ferme : à la fermeture, on met en pause les opérations reprenables et on ne lance plus rien.
- Classer le travail : le plus gros bénéfice d'abord, pour qu'une exécution interrompue ait fait l'utile.
- Des scripts idempotents : une exécution qui échoue à mi-chemin peut être relancée sans risque.
Deux façons de l'exécuter. Les elastic jobs exécutent du T-SQL sur des groupes cibles (un pool, un serveur) et peuvent limiter la concurrence avec le paramètre @max_parallelism de jobs.sp_add_jobstep (nombre maximal de bases par pool qui exécutent l'étape en même temps) ; les planifications sont en UTC. Azure Batch (ou tout ordonnanceur qui lance un script) est mon choix quand il me faut une logique que le T-SQL ne donne pas : une boucle sur les pools, un arrêt à une heure donnée, un mail de rapport, des reprises et des logs par base.
flowchart LR
A["Ordonnanceur : fenêtre de nuit"] --> B["Lister pools et bases"]
B --> C["Par pool : une base à la fois"]
C --> D["Candidats : taille, fragmentation, stats"]
D --> E["Reconstruction en ligne reprenable + update statistics"]
E --> F{"Fenêtre fermée ?"}
F -- "oui" --> G["PAUSE des opérations reprenables"]
F -- "non" --> C
G --> H["Rapport"]
Retour d'expérience
- Surveillez les pourcentages d'écriture de journal et de CPU du pool pendant les premières exécutions ; ajustez le parallélisme, pas l'inverse.
- Un job « réussi » peut cacher des bases ignorées : rapportez les nombres d'ignorées, de mises en pause et d'échecs.
- Les opérations reprenables en pause sont un état : listez-les (
sys.index_resumable_operations) au début de chaque exécution et reprenez-les avant de commencer du nouveau travail. - Testez les scripts sur une copie et gardez un seuil de taille minimal : des milliers de petits index ne valent pas leur coût en métadonnées.