Malheureusement, tout le monde ne le sait pas toujours mais une base de données se fragmente avec le temps. Je vous renvoie à un précédent post : https://blog.djeepy1.net/2008/08/13/indexation-gerer-la-fragmentation/ pour les détails.

Le billet du jour est lié aux plans de maintenance “automatique”. En effet, je vois souvent des SI dans lesquels l’administrateur ou l’équipe de développement sont fiers de m’annoncer qu’ils ont mis des plans de maintenance en place pour gérer la fragmentation.

C’est bien sauf que, si la base est conséquente, ces maintenances automatiques peuvent faire plus de tort que de bien.

En effet, le composant Rebuild Index Task, dans sa configuration par défaut reconstruit TOUS les index de TOUTES les tables.

Le problème est que si vous avez des tables importantes (plusieurs millions de lignes et plusieurs Go de données), reconstruire l’index CLUSTERED peut être une opération lourde :

• Remplissage du LOG (si vous êtes en RECOVERY FULL)
• Lock de la table (si vous êtes en Standard Edition)
• Opération longue

D’autant plus que la tâche génère un script SQL par ordre alphabetique des index et non dans un ordre logique. On reconstruirait normalement les CLUSTERED puis les NON-CLUSTERED.

Bref, j’ai été confronté à des situations où cette tâche engendrait plus de problèmes qu’elle n’en résolvait. Par exemple, la tâche qui dure jusqu’au petit matin, se superposant à un autre plan de maintenance (le backup), dégradant les performances d’un job de synchro, etc.

La solution

La solution consiste à faire un job un peu plus complexe. L’idée est de se baser sur la vue système nous donnant les infos de fragmentation : sys.dm_db_index_physical_stats. Déjà, on peut filtrer sur les index les plus fragmentés (> 25/30% pour moi).

On croise ensuite avec les infos de l’index (type CLUSTERED ou non) et les infos de volumétrie (nombre de lignes) que l’on trouve respectivement dans sys.indexes et sys.partitions.

A partir de ces informations, je peux donc obtenir dynamiquement les instructions à jouer.

SELECT 
    Schema_Name(o.schema_id) as SchemaName,
    OBJECT_NAME(stat.object_id) as TableName,
    ix.name, stat.index_id, 
    avg_fragmentation_in_percent, ix.fill_factor, rows,
    'Alter Index ['+ ix.name
        + '] on [' + Schema_Name(o.schema_id) 
        + '].[' + OBJECT_NAME(stat.object_id) 
        + '] Rebuild' as Statement
FROM sys.dm_db_index_physical_stats(
    DB_ID('AdventureWorks'),
    NULL,
    NULL, NULL, NULL) stat
  inner join sys.indexes ix 
    on ix.index_id = stat.index_id
    and ix.object_id = stat.object_id
  inner join sys.partitions p
    on ix.index_id = p.index_id
    and ix.object_id = p.object_id
  inner join sys.objects o on stat.object_id = o.object_id
where avg_fragmentation_in_percent > 25
order by index_id, avg_fragmentation_in_percent desc

Je prends toutefois ces informations avec précaution. En effet, j’essaie d’avoir une idée de la durée de chaque reconstruction en fonction de la volumétrie. Ceci pour faire ma planification au mieux. Par exemple si en testant (eh oui, le test reste primordial) je m’aperçois que la reconstruction de tel index prends 35 minutes, je l’isole et le planifie à part, à un moment où cela ne va pas gêner les autres tâches.

Pour cela, il suffit de bien connaître sa base et ce qui s’y passe.

Epilogue

Malgré toute la pédagogie que nous pouvons faire lors de formation, consulting, projets, etc., les développeurs ne sont pas encore assez vigilants aux problématiques de base de données et pensent qu’un produit comme SQL Server se maintient tout seul à un haut niveau de performance. Je prends souvent la métophore avec l’automobile. Cela fait quelques siècles que ça existe et pourtant vous allez toujours au garage régulièrement ;-).

Evidemment, loin de moi l’idée d’entretenir le clivage DBA/développeurs. Mon métier de consultant me place bien souvent au milieu et je privilégie la pédagogie.

Donc la règle à retenir : SQL Server est un produit puissant mais complexe, faites-vous aider par des spécialistes.