LLI Wiki — My Digital Knowledge Base

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

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 :

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

Mastodon LLIMastodon SocialFosstodon