Des gains de performance discrets : planifier la maintenance des index SQL dans Optimizely CMS

Ce guide vous accompagne dans la configuration de la maintenance des index SQL dans Optimizely CMS (version 12 et plus récente), de la planification de base à la personnalisation avancée. Vous apprendrez comment configurer des fenêtres de maintenance, ajuster les seuils de fragmentation et surveiller les résultats, le tout sans perturber vos utilisateurs.

Prérequis

Avant de commencer à configurer la maintenance des index, assurez-vous d'avoir :

• Un accès administrateur à votre instance Optimizely CMS (vous devrez accéder à la section Tâches planifiées)
• Une compréhension de base des index SQL Server et de leur impact sur la performance
• L'accès aux paramètres d'application (pour personnaliser les seuils et les délais d'expiration)
• SQL Server 2016 ou plus récent (les versions antérieures fonctionnent mais ont des options de reconstruction en ligne limitées)
• Une connaissance des habitudes de trafic de votre site (pour identifier les meilleures fenêtres de maintenance)

Si vous utilisez Optimizely Commerce avec CMS, la même tâche planifiée gère les deux bases de données, mais vous pourriez devoir ajuster les paramètres de délai d'expiration pour les catalogues plus volumineux.

Mise en œuvre étape par étape

Étape 1 : Localiser la tâche de maintenance intégrée

Naviguez vers votre panneau d'administration Optimizely et cliquez sur Admin > Tâches planifiées. Cherchez une tâche appelée « Maintenir les index de base de données » ou quelque chose de similaire. Cette tâche intégrée est préinstallée avec Optimizely CMS et gère à la fois la réorganisation et la reconstruction des index selon les niveaux de fragmentation.

Si vous ne voyez pas cette tâche, vérifiez que votre installation Optimizely est complète et que les fonctionnalités de maintenance de base de données n'ont pas été désactivées lors de la configuration.

Étape 2 : Configurer l'horaire

Cliquez sur la tâche de maintenance pour ouvrir ses paramètres. Vous verrez des options pour :

• Active : Activez cette option pour permettre l'exécution automatique
• Horaire : Définissez quand et à quelle fréquence la tâche s'exécute
• Prochaine exécution : Indique quand la tâche s'exécutera ensuite

Pour l'horaire, choisissez un moment où votre site a un trafic minimal. La plupart des sites connaissent l'activité la plus faible entre 2 h et 4 h du matin, heure locale. Commencez par un horaire hebdomadaire, le dimanche matin à 3 h est un choix populaire.

Étape 3 : Personnaliser les seuils de fragmentation

Par défaut, Optimizely reconstruit les index avec plus de 30 % de fragmentation et réorganise ceux entre 10 % et 30 %. Ces valeurs par défaut conviennent à la plupart des sites, mais vous pouvez les ajuster dans votre appsettings.json :

{
  "EPiServer": {
    "Commerce": {
      "HighFragmentationThreshold": 30,
      "LowFragmentationThreshold": 10,
      "DataBaseIndicesJobCommandTimeOut": 120
    }
  }
}

Ce que ces paramètres signifient :

• HighFragmentationThreshold : Les index au-dessus de ce pourcentage sont reconstruits (recréation complète)
• LowFragmentationThreshold : Les index entre ce seuil et le seuil élevé sont réorganisés (nettoyage léger)
• DataBaseIndicesJobCommandTimeOut : Nombre maximum de secondes que la tâche peut s'exécuter avant l'expiration du délai

Pour les sites riches en contenu avec des mises à jour fréquentes, envisagez de réduire le seuil élevé à 25 % pour une maintenance plus agressive.

Étape 4 : Gérer les bases de données volumineuses

Si votre base de données dépasse 50 Go ou contient des millions d'éléments de contenu, le délai d'expiration par défaut de 30 secondes ne suffira pas. Augmentez progressivement la valeur du délai d'expiration, commencez avec 120 secondes et ajustez selon les journaux d'achèvement des tâches.

Pour les très grandes bases de données, envisagez de répartir la maintenance sur plusieurs nuits :

{
  "EPiServer": {
    "Cms": {
      "DataBaseIndicesJobCommandTimeOut": 180
    },
    "Commerce": {
      "DataBaseIndicesJobCommandTimeOut": 240
    }
  }
}

Étape 5 : Configurer la surveillance

Après avoir configuré la tâche, surveillez son exécution via l'historique des tâches. Recherchez :

• Temps d'achèvement : Les tâches prenant plus de 5 minutes pourraient affecter les utilisateurs matinaux
• Messages d'erreur : Les délais d'expiration ou problèmes de permissions nécessitent une attention immédiate
• Index ignorés : Certains index pourraient être trop volumineux pour la fenêtre de délai d'expiration

Créez une requête SQL simple pour vérifier manuellement les niveaux de fragmentation :

SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id 
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
    AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

Exécutez cette requête avant et après la maintenance pour vérifier l'efficacité de la tâche.

Exemples de code et configuration

Script de maintenance personnalisé pour un contrôle avancé

Bien que la tâche intégrée gère la plupart des scénarios, certaines équipes préfèrent des scripts personnalisés pour un contrôle granulaire. Voici une procédure de maintenance de base que vous pouvez planifier via SQL Server Agent :

CREATE PROCEDURE [dbo].[CustomIndexMaintenance]
AS
BEGIN
    DECLARE @TableName NVARCHAR(255)
    DECLARE @IndexName NVARCHAR(255)
    DECLARE @Fragmentation FLOAT
    DECLARE @SQL NVARCHAR(MAX)
    
    DECLARE index_cursor CURSOR FOR
    SELECT 
        OBJECT_NAME(ips.object_id),
        i.name,
        ips.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    INNER JOIN sys.indexes i ON ips.object_id = i.object_id 
        AND ips.index_id = i.index_id
    WHERE ips.avg_fragmentation_in_percent > 10
        AND ips.page_count > 1000
        AND i.name IS NOT NULL
    
    OPEN index_cursor
    FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @Fragmentation
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @Fragmentation > 30
            SET @SQL = 'ALTER INDEX ['   @IndexName   '] ON [dbo].['   @TableName   '] REBUILD'
        ELSE
            SET @SQL = 'ALTER INDEX ['   @IndexName   '] ON [dbo].['   @TableName   '] REORGANIZE'
        
        EXEC sp_executesql @SQL
        
        FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @Fragmentation
    END
    
    CLOSE index_cursor
    DEALLOCATE index_cursor
END

Ajustement selon la taille de la base de données

Les petites bases de données (moins de 10 Go) peuvent utiliser des paramètres agressifs :

{
  "EPiServer": {
    "Commerce": {
      "HighFragmentationThreshold": 20,
      "LowFragmentationThreshold": 5,
      "DataBaseIndicesJobCommandTimeOut": 60
    }
  }
}

Les grandes bases de données (plus de 100 Go) nécessitent des paramètres conservateurs :

{
  "EPiServer": {
    "Commerce": {
      "HighFragmentationThreshold": 40,
      "LowFragmentationThreshold": 15,
      "DataBaseIndicesJobCommandTimeOut": 600
    }
  }
}

Erreurs courantes à éviter

Exécuter la maintenance pendant les heures d'affaires

La plus grosse erreur est de planifier la maintenance pendant les heures actives. Même les opérations de réorganisation « légères » peuvent causer des blocages sur les tables occupées. Vérifiez toujours vos paramètres de fuseau horaire. Une tâche planifiée pour 3 h UTC pourrait s'exécuter à 22 h HNE.

Sur-entretenir les index

Les reconstructions quotidiennes sur des bases de données avec peu de changements gaspillent des ressources et augmentent l'usure des SSD. Nous avons constaté que la plupart des sites Optimizely fonctionnent bien avec une maintenance hebdomadaire, sauf s'ils traitent des milliers de mises à jour de contenu quotidiennement.

Ignorer les échecs de tâches

Les échecs silencieux sont dangereux. Si la tâche de maintenance expire régulièrement, la fragmentation s'accumule jusqu'à ce que la performance se dégrade visiblement. Configurez des alertes par courriel pour les échecs de tâches :

• Accédez aux paramètres de la tâche planifiée
• Activez « Envoyer un courriel en cas d'échec »
• Configurez les paramètres SMTP dans votre application
• Ajoutez l'adresse courriel de surveillance de votre équipe

Oublier les statistiques

La maintenance des index ne met pas toujours à jour les statistiques, qui aident SQL Server à choisir des plans de requête efficaces. Si vous remarquez des requêtes lentes malgré une faible fragmentation, mettez à jour les statistiques manuellement :

EXEC sp_updatestats;

Ne pas tester après les mises à jour

Les mises à jour d'Optimizely réinitialisent parfois les configurations des tâches planifiées. Après les mises à jour de la plateforme, vérifiez que votre horaire de maintenance et vos paramètres personnalisés restent intacts.

Étapes de test et de vérification

Référence pré-maintenance

Avant votre première maintenance planifiée, capturez des métriques de référence :

• Exécutez la requête de vérification de fragmentation fournie précédemment
• Notez les temps moyens de chargement des pages dans Application Insights ou votre outil de surveillance
• Enregistrez les tailles des fichiers de base de données
• Vérifiez les modèles actuels d'utilisation du processeur et de la mémoire

Validation post-maintenance

Après l'exécution réussie de la tâche :

• Vérifiez l'historique des tâches : Confirmez l'achèvement sans erreurs
• Vérifiez la réduction de la fragmentation : Réexécutez la requête de fragmentation
• Surveillez la performance : Recherchez des temps de réponse améliorés au cours des 24 heures suivantes
• Examinez les journaux : Vérifiez s'il y a des avertissements de blocage ou de délai d'expiration

Test de performance

Créez un test de charge simple pour mesurer l'amélioration :

[Fact]
public async Task IndexMaintenanceImprovesFindPagesQuery()
{
    // Arrange
    var stopwatch = new Stopwatch();
    var iterations = 100;
    var timings = new List();
    
    // Act
    for (int i = 0; i < iterations; i  )
    {
        stopwatch.Restart();
        var pages = await _contentLoader
            .GetChildren(ContentReference.StartPage)
            .ToListAsync();
        stopwatch.Stop();
        timings.Add(stopwatch.ElapsedMilliseconds);
    }
    
    // Assert
    var averageTime = timings.Average();
    var baseline = 50; // millisecondes, ajustez selon votre référence
    Assert.True(averageTime < baseline, 
        $"Requête moyenne de {averageTime}ms, attendue sous {baseline}ms");
}

Surveillance à long terme

Établissez un processus de révision mensuel :

• Exportez l'historique des tâches pour suivre les taux d'achèvement
• Graphiquez les niveaux de fragmentation au fil du temps
• Corrélez les fenêtres de maintenance avec les métriques de performance
• Ajustez les horaires selon les modèles de fragmentation réels

Notre expérience montre que les sites ont souvent besoin d'ajustements d'horaire après des migrations de contenu majeures ou des changements de trafic saisonniers.

Considérations avancées

Reconstructions en ligne vs hors ligne

SQL Server Édition Standard effectue des reconstructions hors ligne par défaut, rendant les tables indisponibles pendant l'opération. Si vous avez SQL Server Édition Entreprise, activez les reconstructions en ligne dans vos scripts personnalisés :

ALTER INDEX [IndexName] ON [TableName] 
REBUILD WITH (ONLINE = ON, MAXDOP = 4);

Le paramètre MAXDOP limite le traitement parallèle pour éviter la saturation du processeur.

Gestion des catalogues Commerce

Les bases de données Optimizely Commerce nécessitent souvent des modèles de maintenance différents de ceux des bases de données CMS. Les catalogues de produits avec des mises à jour fréquentes de prix pourraient bénéficier d'une maintenance bihebdomadaire, tandis que le contenu CMS reste sur un horaire hebdomadaire.

Intégration aux pipelines de déploiement

Envisagez de déclencher la maintenance des index après les déploiements majeurs :

# Script PowerShell pour la maintenance post-déploiement
Invoke-Sqlcmd -Query "EXEC dbo.CustomIndexMaintenance" `
              -ServerInstance "votre-serveur" `
              -Database "votre-base-de-donnees" `
              -QueryTimeout 600

Conclusion

Configurer une maintenance appropriée des index SQL n'est peut-être pas la tâche la plus excitante, mais c'est l'un de ces investissements qui rapporte des dividendes grâce à une performance constante et à moins de sessions de débogage d'urgence. Commencez avec la tâche intégrée d'Optimizely, planifiez-la pour les heures creuses et ajustez selon vos modèles de charge de travail spécifiques.

Rappelez-vous qu'une maintenance parfaite des index n'existe pas. Vous visez une performance suffisante sans utilisation excessive de ressources. Surveillez vos métriques, ajustez graduellement, et n'oubliez pas ces mises à jour de statistiques.

Le travail avec des équipes nous a appris que les sites avec les meilleures performances ne sont pas nécessairement ceux avec les horaires de maintenance les plus agressifs, mais plutôt ceux avec des routines de maintenance cohérentes et bien surveillées qui correspondent à leurs modèles d'utilisation réels.

Besoin d'aide pour analyser la performance de votre base de données Optimizely ou configurer un plan de maintenance adapté à vos modèles de contenu spécifiques et à vos charges de trafic? Notre équipe peut examiner vos niveaux actuels de fragmentation des index, identifier les goulots d'étranglement dans vos requêtes et créer un horaire de maintenance personnalisé qui maintient votre site en bon état de marche sans surcharge inutile. Contactez-nous pour discuter de la façon dont nous pouvons vous aider à obtenir ces gains de performance discrets.