Il m’arrive souvent lors de mes audits de trouver des plans de maintenance qui ne sont pas en adéquation avec le business des clients soit parce que les personnes de l’IT n’ont pas les connaissances nécessaires pour le faire correctement soit parce que cette phase est souvent négligée. Dans ce billet, j’essaierai de répertorier sans aucune prétention l’ensemble des problèmes ou maladresses que j’ai pu rencontré au cours de ces audits et les bonnes pratiques  à appliquer en sachant qu’il n’existe pas une seule solution. En effet, bien souvent c’est le contexte client qui fera pencher la balance.

Plan de maintenance sans vérification d’intégrité

Sauvegarder une base de données ne garantit pas pour autant que la restauration se fera sans problème. En effet, votre base peut être corrompue et sauvegarder une base corrompue vous donnera une sauvegarde ayant des pages de données corrompues. Bien qu’il soit possible maintenant d’effectuer une restauration à partir d’un tel jeu de données (avec l’option CONTINUE_AFTER_ERROR), il est tout de même bon de savoir le plus rapidement possible lorsqu’une corruption se produit sur vos données. Cela vous permet de réagir rapidement d’une part et de ne pas avoir de surprise lors de votre restauration d’autre part. En général lorsque vous restaurez une base de données, vous devez en général le faire le plus rapidement possible … vous savez les fameux SLA qui vous obligent à aller vite … très vite … alors si vous pouvez vous passer d’un tel désagrément lors de cette phase de restauration je pense que vous ne direz pas non !

Plan de maintenance sans vérification de la sauvegarde a postériori

Ici aussi cela peut paraitre idiot mais vérifier une sauvegarde après l’avoir effectué est quand même un gage de sa fiabilité. Il existe un bon nombre d’éléments externes qui peuvent interférer au bon déroulement de votre sauvegarde comme un disque défectueux qui héberge vos fichiers de sauvegarde ou encore un problème réseau qui corrompt vos données lors de leur transfert. Encore une fois, cela vous évitera bien des surprises lorsque vous vous retrouverez dans une situation de restauration …

Plan de maintenance sans maintenance des index ou des statistiques

Avoir des index fragmentés et des statistiques qui ne sont pas à jour est le meilleur moyen de voir les performances de la base de données chuter. Il ne faut pas négliger cette phase et la planifier en fonction de l’activité de production sur le serveur de bases de données.

Plan de maintenance qui mélange à la fois une tâche de mise à jour des index et tâche de reconstruction des index

L’adage qui dit « Il vaut mieux deux fois qu’une !!! » n’est évidemment pas valable dans ce cas. L’idée de réorganiser et de reconstruire les index dans un même processus de maintenance est plutôt bonne si l’on cible ceux qui en ont réellement besoin mais malheureusement les deux tâches par défaut proposées lors de la création d’un plan de maintenance et qui sont associées à la maintenance des index prennent en compte l’ensemble des index existant dans une base de données . Aucun seuil de fragmentation n’est pris en compte. Cela signifie que l’utilisation de ces deux tâches dans un même processus de plan de maintenance n’est pas utile, voir même déconseillé pour des raisons évidentes : le temps d’exécution du processus de maintenance en est allongé et il faudra par conséquent monopoliser beaucoup plus de ressources serveurs (CPU, RAM , disques) plus longtemps.

Plan de maintenance qui contient une reconstruction d’index et une mise à jour des statistiques

Que fait SQL Server lorsqu’il reconstruit les index ? Faisons un test :

CREATE TABLE T_TEST
(
  id INT IDENTITY(1,1) NOT NULL,
  texte VARCHAR(50)
);

INSERT T_TEST (texte) VALUES (REPLICATE(‘T’, 50));
GO 10000

CREATE UNIQUE CLUSTERED INDEX IDX_T_TEST
ON T_TEST ( id );
GO

Regardons les statistiques associées à l’index cluster IDX_T_TEST de la table T_TEST :

DBCC SHOW_STATISTICS(T_TEST, ‘IDX_T_TEST’);

Le résultat est le suivant :

Reconstruisons l’index cluster et regardons à nouveau les statistiques de cet index :

ALTER INDEX IDX_T_TEST
ON T_TEST REBUILD;

DBCC SHOW_STATISTICS(T_TEST, ‘IDX_T_TEST’);

Le résultat est le suivant :

La reconstruction de l’index cluster a provoqué la mise à jour de ces statistiques (L’heure de mise à jour a changé).

On en déduit facilement qu’une tâche de mise à jour des statistiques est inutile lorsque celle-ci est précédée par une tâche de reconstruction d’index. Celle-ci est par contre indispensable lorsqu’il s’agit d’une tâche de réorganisation des index.

Une contrainte de précédence en échec est définie entre une tâche de sauvegarde et un sous ensemble de tâches de maintenance des index

La performance d’une base de données est directement liée à la fragmentation de ses index et l’état de mise à jour des statistiques. Pensez vous réellement que l’échec de sauvegarde d’une base de données signifie que toute maintenance doit être arrêtée d’autant plus qu’un échec de sauvegarde d’une base de données peut avoir des causes multiples : Manque de place sur le support de sauvegarde, support de sauvegarde non prêt, support de sauvegarde corrompu etc. Personnellement je conseille aux clients de définir leur contrainte de précédence à « Completion » dans la plupart des cas en fonction du processus défini par la suite.

 Plan de maintenance composé d’une tâche de réduction des fichiers

On ne le dira jamais assez mais la réduction des fichiers d’une base de données de manière répétitive est génératrice de fragmentation logique (index) et physique (fichiers sur disque). Cette opération doit rester une opération exceptionnelle pour un administrateur de bases de données. 

Plusieurs plans de maintenance pour une même base de données

Il m’arrive souvent de voir chez les clients plusieurs plans de maintenance pour une même base de données. Par exemple :

- un plan de maintenance pour la sauvegarde de la base de données
- un plan de maintenance pour la sauvegarde du journal de la base de données
- un plan de maintenance pour la maintenance des index

A mon sens, multiplier les plans de maintenance pour une base de données rend complexe son administration surtout si le serveur de bases de données contient un certain nombre de bases de données. Depuis SQL Server 2005, il est possible d’utiliser les sous plans à l’intérieur d’un même plan de maintenance. L’avantage ici est d’avoir un seul plan de maintenance visible pour la base de données concernée. Il est également possible de planifier l’exécution de chaque sous plan à des périodes différentes.

Il existe certainement d’autres cas que vous avez vu (d’ailleurs n’hésitez pas à partager vos expériences), que je verrai certainement et que je corrigerai. Une chose importante est à retenir ici : un bon plan de maintenance s’aligne avec le business d’une entreprise !!!

David BARBARIN (Mikedavem)
MVP SQL Server