♪
I. Introduction▲
Quel DBA ignore ce qu'est la vérification d'intégrité ? Ce processus est important, car il permet de prévenir une anomalie dans une base de données. Cette opération, en apparence simple à intégrer dans un plan de maintenance, l'est beaucoup moins lorsqu'il s'agit de VLDB (Very Large Data Bases).
II. Détection des problèmes d'intégrité▲
L'option de bases de données PAGE_VERIFY indique à SQL Server le procédé à utiliser pour détecter les pages endommagées. Depuis la version 2005 de SQL Server, il est possible de paramétrer l'option PAGE_VERIFY avec la valeur TORN_PAGE_DETECTION ou CHECKSUM.
Si l'option TORN_PAGE_DETECTION est activée, un bit spécifique à chaque secteur de 512 octets d'une page de 8Ko (soit 16 secteurs) est inscrit dans l'en-tête de la page. Lorsqu'une page est lue depuis le disque, ces bits sont comparés aux informations réelles de chaque secteur. Si les informations diffèrent, alors il y a corruption de la page.
Si l'option CHECKSUM est activée, une somme de contrôle pour la page entière est calculée puis stockée dans son en-tête. Lorsqu'une page est lue depuis le disque, une somme de contrôle est recalculée et comparée à celle écrite dans l'en-tête de la page. Si celles-ci ne correspondent pas, la page est corrompue.
La détection d'erreur par l'option CHECKSUM est beaucoup plus précise que celle utilisée par l'option TORN_PAGE_DETECTION, mais elle monopolise quelques ressources CPU de plus.
À partir de la version 2005, lors SQL Server détecte une page corrompue, il génère une erreur. La commande qui voulait accéder à la page est annulée et une entrée est créée dans la table suspect_pages au sein de la base de données msdb.
L'inconvénient de cette 1re approche est qu'il faut attendre que la page soit lue par le moteur de bases de données pour détecter une corruption. Une autre approche, beaucoup plus proactive, consiste à lancer la commande DBCC CHECKDB pour forcer la lecture de toutes les pages d'une base de données.
III. Fonctionnement de la commande DBCC▲
La commande DBCC CHECKDB vérifie l'intégrité physique et logique d'une base de données et s'effectue en trois phases bien distinctes.
1re phase : CHECKALLOC : une vérification de la cohérence d'allocation d'espace disque est effectuée. Les informations contenues dans les pages IAM, GAM, SGAM sont vérifiées pour chaque Extent de la base de données.
2e phase : CHECKTABLE : une vérification logique et physique de l'intégrité des pages, des structures pour chaque table, des vues indexées et des index est effectuée. Dans le cas des vues indexées, chaque ligne de données est régénérée en se basant sur sa requête de création puis comparée aux données réelles de cette même vue. Cette ressource peut être consommatrice en ressources CPU.
3e phase : CHECKCATALOG : un contrôle de la cohérence des informations entre les différentes tables de métadonnées système est effectuée.
IV. Facteurs influant sur les performances de la commande DBCC CHECKDB▲
Afin d'optimiser la phase de vérification des bases de données, il est utile de connaître les facteurs ayant un impact sur sa vitesse d'exécution. Celle-ci varie en fonction de différents paramètres physiques et logiques, dont les principaux sont :
La taille de la base de données : un facteur important. En effet plus la taille d'une base de données est importante, plus le processus de vérification sera long, car la totalité des pages d'allocation de la base doit être lue.
La complexité du schéma de bases de données : le schéma est également un facteur important. Plus le schéma de la base de données est complexe, plus la vérification d'intégrité prendra de temps.
Les ressources matérielles : les lectures effectuées par le processus de vérification se font séquentiellement et dans l'ordre physique des pages de la base de données. Le processus de vérification, grâce à son algorithme de fonctionnement, essaie autant que possible d'optimiser et de réduire les éventuels déplacements aléatoires des têtes de lecture des disques de données. Si une concurrence d'accès disque apparaît, celle-ci peut interférer et réduire la bande passante utilisée par le processus de vérification et le temps d'exécution en est alors augmenté. C'est une des raisons pour lesquelles il est conseillé de lancer un tel processus dans les périodes creuses d'activité, dans la mesure du possible. On peut avancer une deuxième explication : lorsque le processus de vérification est lancé, il utilise une capture instantanée interne qui se situe sur le même volume disque que la base de données elle-même afin d'éviter une concurrence d'accès aux données de la base. Il faut savoir qu'une capture instantanée est vide à sa création, mais elle se remplit à chaque mise à jour des données de la base. Cela signifie que pour chaque mise à jour il y aura un déplacement des têtes de lecture de disque qui perturbera inévitablement le processus de vérification. En d'autres termes, le processus risque d'être d'autant plus long que les mises à jour lancées au cours du processus de vérification d'intégrité des bases sont nombreuses.
Les disques et l'utilisation de la base TEMPDB : le processus de vérification utilise principalement des entrées / sorties disques. Sa vitesse d'exécution dépend donc de la bande passante du sous-système disque. La base de données tempdb peut être également utilisée par le processus de vérification en particulier dans le cas des VLDB où la quantité de mémoire allouée est parfois insuffisante. En effet le processus de vérification nécessite le stockage de certaines informations internes au cours de son processus. Il utilise pour cela un espace mémoire de travail, mais, dans le cas des VLDB, cet espace mémoire est très souvent insuffisant et le processus stockera ses informations dans la base Tempdb prévue à cet effet. Dans ce deuxième cas, la vitesse d'exécution du processus de vérification dépend aussi du sous-système disque des fichiers de bases tempdb.
Les options d'exécution de la commande DBCC CHECKDB : en fonction des options d'exécution de la commande DBCC CHECKDB, le processus peut être plus ou moins long, car la vérification s'effectuera partiellement ou totalement sur la base de données.
V. Les problématiques liées aux VLDB▲
Dans la plupart des situations, les options de vérification d'intégrité des bases proposées dans les plans de maintenance suffisent et satisfont aux besoins des entreprises. Pourtant, il n'en va pas de même lorsqu'il s'agit des VLDB où le temps d'exécution du processus de vérification d'intégrité devient très important et peut rapidement atteindre les limites imposées par une fenêtre de maintenance. Il existe une multitude de solutions pour essayer de pallier ce problème.
V-A. Utiliser les options de la commande DBCC CHECKDB▲
La commande DBCC CHECKDB comporte plusieurs options intéressantes qui peuvent réduire considérablement les temps d'exécution de bases de données.
Option PHYSICAL_ONLY: cette option supprime l'étape de vérification logique de la base de données. Seules les structures physiques des pages et des en-têtes ainsi que celles des arbres B-Tree sont vérifiées afin de prévenir tout problème de corruption matérielle (TORN pages, CHECKSUM Failure ?). La suppression de l'étape vérification logique des bases est un bon moyen de diminuer considérablement le temps d'exécution et minimise les ressources monopolisées par le processus.
Option CHECKFILEGROUP : cette option peut être utile si la structure physique de la base de données le permet. Celle-ci doit suivre un schéma de répartition des tables dans plusieurs FILEGROUPS. L'option CHECKFILEGROUP, dans ce cas, peut jouer pleinement son rôle et permet de vérifier l'intégrité de toutes les tables d'une base de données pour un FILEGROUP déterminé. On peut, grâce à cette option, répartir et planifier la vérification de chaque groupe de fichiers sur plusieurs jours afin de réduire le temps et la charge d'exécution du processus de vérification de la base.
V-B. Personnaliser ses propres partitions de vérification▲
Une autre solution consiste à partitionner la base de données en lots et planifier l'exécution des trois phases de la commande DBCC CHECKDB (DCBCC CHECKCATALOG, DBCC CHECKALLOC et DBCC CHECKTABLE) sur ces lots. Pour cela, il faut au préalable repérer les tables les plus importantes selon le nombre de pages, puis les grouper pour former des lots de taille équivalente et planifier pour chaque lot l'exécution les différentes phases de vérification d'intégrité. Le but est de minimiser la charge de travail du processus en répartissant les phases de vérification en fonction d'un planning déterminé.
La vérification du catalogue des métadonnées et de la cohérence d'allocation des pages s'effectuera deux fois par semaine. La vérification d'intégrité des pages et des structures physiques se fera tout au long de la semaine et sur chaque lot distinct. On minimise ainsi l'impact et la durée du processus de vérification. L'avantage de cette solution est de réduire considérablement le temps d'exécution de la vérification d'intégrité ainsi que les ressources monopolisées. Elle convient bien dans un scénario où la fenêtre de maintenance est extrêmement réduite et où l'on doit effectuer d'autres opérations par la suite telle qu'une réindexation ou une sauvegarde de bases de données. Cette méthode présente néanmoins quelques inconvénients : une capture instantanée est créée à chaque fois qu'une opération DBCC est réalisée et, dans ce cas précis, pour l'opération CHECKTABLE. Si l'activité de mise à jour est importante à ce moment-là, comme nous l'avons vu plus haut, le processus de vérification risque d'être perturbé et le temps d'exécution allongé. Il faudra alors adapter le nombre de lots en fonction de la durée imposée dans la fenêtre de maintenance. Elle nécessite également plus de travail de préparation pour constituer les lots de table.
V-C. Utiliser un système dédié▲
Il s'agit dans ce cas de déporter l'opération de vérification des bases sur un système dédié. Il suffit de remonter une sauvegarde et de lancer une opération de vérification sur les bases de données restaurées. Comme on peut le deviner, le principal avantage est que les ressources ne sont plus monopolisées sur l'environnement de production. Mais il y a tout de même quelques inconvénients : d'une part cela exige d'avoir un système dédié et d'autre part de posséder une place disque aussi importante qu'en environnement de production.
V-D. Optimiser tempdb▲
Comme nous l'avons vu, la bande passante offerte par le sous-système disque est en relation directe avec la vitesse d'exécution du processus de vérification des bases de données. Il faut choisir un sous-système de disque rapide pour tempdb (RAID 1 + 0 par exemple), car le processus de vérification a de très grandes chances de l'utiliser si la base est une VLDB. Il faut également créer autant de fichiers que l'on a de processeurs pour profiter pleinement de l'algorithme de répartition des données utilisé par SQL Server.
VI. Mise en application▲
VI-A. Le contexte▲
Voici une mise en application réelle chez un client pour lequel je travaille.
Le serveur de bases de données du client héberge une application critique. Celle-ci doit est être disponible 24H/24 et 7j/7. Les performances et la rapidité de l'application doivent être altérées le moins possible, et ceci même pendant les différentes fenêtres de maintenance.
Le client rencontrait un problème de durée d'exécution pour l'un de ses plans de maintenance. Une des tâches de ce plan consistait à vérifier l'intégrité des bases de données de l'application. Le temps d'exécution global de cette tâche était de 8H, ce qui correspondait à la durée totale permise par la fenêtre de maintenance. L'exécution des autres tâches, comme la sauvegarde ou les opérations de maintenance des index se faisaient donc hors de cette fenêtre de maintenance. Le temps passé par le processus de vérification d'intégrité concerne principalement deux bases de données (sur une vingtaine au total) et qui ont les caractéristiques suivantes :
Le client avait désactivé cette tâche pour les deux bases concernées. Mon travail a consisté à optimiser et réintégrer cette tâche essentielle. Pour illustrer cet article, je ne prendrai qu'une seule base de données nommée BASE1.
Plusieurs solutions ont été étudiées :
- la mise en place d'un serveur dédié n'a pas été retenue même si le coût en espace disque est devenu raisonnable. Le seul fait de restaurer une base de données et d'opérer une vérification d'intégrité est une solution prenant beaucoup trop de temps (une demi-journée) ;
- la base de données n'est pas répartie dans plusieurs groupes de fichiers. Le découpage de la base de données en plusieurs groupes de fichiers n'est pas envisageable à court terme. L'option CHECKFILEGROUP n'a donc pas été retenue.
Nous nous sommes donc orienté vers deux solutions : l'utilisation de l'option PHYSICAL_ONLY et la définition de partitions personnalisées pour diminuer et répartir le temps et la charge du processus de vérification.
VI-B. Résultats▲
Comme on le constate dans le meilleur des cas, le gain de temps est de 73 % pour cette seule base de données avec l'application du partitionnement personnalisé.
VII. Conclusion▲
La vérification d'intégrité des bases de données dans le cas des VLDB n'est pas simple à mettre en œuvre. Le choix d'une méthode doit s'effectuer en adéquation avec l'architecture, les contraintes techniques et fonctionnelles de l'environnement dans lequel l'on se trouve. Il faut garder également à l'esprit qu'une méthode mise en place est amenée à être revue en fonction des modifications que subissent les bases de données. Il est donc important d'implémenter en parallèle un système de surveillance permettant d'anticiper et d'indiquer vers quelles méthodes il faudra s'orienter, ce qui fera l'objet d'un autre article…
Bonne vérification !!
VIII. Références▲
IX. Remerciements▲
Je remercie Fadace et Patricia pour la relecture de cet article.