[SQL Server] Gestion de fichiers de données : vérification de l’intégrité des données

Nous allons parler, ici, d’un élément-clé de la gestion de fichiers de données: la vérification de l’intégrité des données au sein des fichiers de données d’une base. Cette opération est importante dans la mesure où elle permet de prévenir une anomalie au niveau des pages d’un fichier de données.

Les différents types de détection de problèmes d’intégrité

SQL Server propose, pour chaque base de données, un mode de détection d’anomalies dans les pages de données et d’indexes :

  • CHECKSUM (par défaut, à partir de SQL Server 2005) : calcul d’une somme de contrôle (checksum) pour la page entière puis stockage dans son en-tête avant toute écriture sur le disque. Puis, par la suite, lorsqu’une page est lue depuis le disque, un checksum est recalculé, puis le résultat est comparé à celui écrit dans l’en-tête de la page. Si le résultat ne correspondent pas, la page est corrompue (erreur 824). Opération gourmande en CPU par rapport aux autres types de vérification.
  • TORN_PAGE_DETECTION : écriture d’un bit spécifique à chaque secteur de 512 octets d’une page de 8 Ko (soit 16 secteurs) dans l’en-tête de la page. De ce fait, à chaque lecture d’une page depuis le disque, ces bits sont comparés aux informations réelles de chaque secteur du disque. Si les informations diffèrent, alors il y a corruption de la page. Opération moins gourmande en CPU.
  • NONE (à bannir) : permet de ne pas faire de vérification de pages.
Il est recommandé d’opter pour le mode par défaut CHECKSUM qui offre une meilleure vérification de l’intégrité des pages en la balayant entièrement. Sauf si vous avez de graves problèmes de performances en I/O qui sont toujours irrésolus malgré un tuning et une optimisation approfondis des performances (i.e., amélioration des requêtes, meilleure répartition des fichiers, etc…) et que, dans ce cas, le TORN_PAGE_DETECTION devient le seul mode adéquat.

Fonctionnement de la commande DBCC CHECKDB

La commande DBCC CHECKDB[1] effectue une vérification des pages de toute une base de données. C’est cette commande qui est utilisée, par défaut, par SQL Server dans un plan de maintenance, lors de la vérification de l’intégrité d’une base de données. Vous pouvez utiliser l’option NO_INFOMSGS si vous ne souhaitez pas être spammé de messages d’information.

La commande DBCC CHECKDB fonctionne comme suit :

  • Etape 1 : vérification de la cohérence des informations des pages d’allocation (GAM, SGAM, IAM, PFS…) pour chaque extent de données. Il s’agit de la commande DBCC CHECKALLOC.
  • Etape 2 : vérification (logique et physique) de l’intégrité des pages des différentes structures de données de la base traitée, qu’il s’agisse des tables, des indexes ou des vues indexées (pour ces dernières, le contenu est reproduit, en reprenant sa requête de création, puis comparé par rapport aux lignes de données réelles de cette même vue indexée afin d’en vérifier la cohérence). Il s’agit de la commande DBCC CHECKTABLE.
  • Etape 3 : vérification de la cohérence du catalogue de la base de données. Il s’agit d’un dictionnaire de données fournissant des informations sur les métadonnées qui décrivent les attributs d’un système La commande utilisée est DBCC CHECKCATALOG, qui permet notamment de contrôler la cohérence des informations dans et entre les tables systèmes.

La syntaxe de rédaction d’une commande de vérification de l’intégrité d’une base MaBase est la suivante :

  • Pour l’ensemble d’une base de données :
DBCC CHECKDB(MaBase)
GO
  • Pour un objet d’une base de données (supposons, la table T1) :
USE MaBase
GO
DBCC CHECKTABLE ('dbo.T1')
GO

Notons que par défaut, DBCC CHECKTABLE contrôle également tous les indexes de la table passée en paramètre.

  • Pour un index spécifique d’une base de données (supposons, un index spécifique de la table T1 qu’on appellerait IDX_T1) :
USE MaBase
GO
DECLARE @indid int;
SET @indid =(SELECT index_id
             FROM sys.indexes
             WHERE object_id=OBJECT_ID('dbo.T1') AND name ='IDX_T1');
DBCC CHECKTABLE ('dbo.T1', @indid)
GO
  • Pour les pages d’allocation et le catalogue d’une base de données :
DBCC CHECKALLOC (MaBase)
GO
DBCC CHECKCATALOG (MaBase)
GO

Gardons en tête que suivant le coût de l’opération de vérification de l’intégrité des bases via DBCC CHECKDB, et tout particulièrement dans le cas de VLDBs, tempDB peut être excessivement utilisé. En effet, au fur et à mesure que SQL Server inspecte chaque page de données d’une base de données, il stocke les informations de vérification en mémoire. Mais toutefois, quand la quantité d’informations (généralement proportionnelle à la volumétrie d’une base traitée) est trop volumineuse, tempDB est donc utilisé, provoquant ainsi sa croissance. Vous pouvez estimer la taille idéale de tempDB en utilisant DBCC CHECKDB (‘LaPlusGrosseBaseDeLInstance’) WITH ESTIMATEONLY. Cela peut également servir à mieux adapter la volumétrie de la partition dédiée à tempDB, ainsi qu’à lui pré-allouer la bonne taille.

Notons que contrairement à des idées reçues, DBCC CHECKDB est tout sauf une opération bloquante (hormis avant SQL Server 2000, à moins d’utiliser délibérément l’option TABLOCK, ce qui est déconseillé car, en l’occurrence, c’est un lock de base de données (car l’ensemble des tables à traiter sera temporairement verrouillé) qui sera fait dans ce cas-là d’autant plus qu’il empêche la vérification du catalogue des données).

En effet, dans le cas de SQL Server 2000, cette opération utilise le journal de transactions de chaque base de données traitée afin d’en obtenir une vue consistante de ces dernières (d’où l’impossibilité de tronquer les logs durant le traitement, ce qui, par conséquent, fait grossir les journaux de logs) tandis que depuis SQL Server 2005 (et une revue de l’algorithme de l’opération par Paul Randal), une image (snapshot) cachée de chaque base de données traitée est utilisée sur la même partition que les fichiers de données des bases concernées.

Mise-en-œuvre de différentes stratégies de vérification de l’intégrité des données selon divers scénarii

          Principaux facteurs pouvant impacter la durée de DBCC CHECKDB

Le lancement d’un plan de vérification de l’intégrité d’une base de données est une opération relativement simple, mais différents facteurs peuvent pénaliser grandement la durée d’exécution d’une opération de vérification complète de l’intégrité d’une base de données via DBCC CHECKDB :

  • Ressources matérielles ou contexte inadaptés : SQL Server traite la vérification de l’intégrité d’une base de données de manière séquentielle et dans l’ordre physique des pages de données. Si, au sein d’un ou plusieurs LUNs où sont situés les fichiers de données à traiter, il y a une très forte activité I/O, il est possible que des interférences au niveau des accès disques perturbent les traitements séquentiels des pages de données réduisant ainsi la bande-passante du sous-système disque utilisé par SQL Server et donc la durée des traitements. C’est pour cette raison qu’il est recommandé de lancer les traitements de vérification de l’intégrité des pages d’une base de données dans des périodes creuses.
  • Volumétrie de la base de données : plus la taille des fichiers de données est élevée, plus la durée de vérification de l’intégrité des pages de données devient longue.
  • Degré de complexité des schémas de la base de données : plus les schémas sont structurellement complexes, plus le processus de vérification de l’intégrité de la base concernée devient long.
  • Utilisation intensive de tempDB :
    SQL Server peut être amené à utiliser tempDB pour stocker des jeux de résultats (ou espaces de travail) générés par l’opération de vérification de l’intégrité de la base quand il constate que son cache mémoire n’est pas assez dimensionné pour les recevoir. Cela est notamment vrai dans le cas d’un VLDB (Very Large DataBase) où la quantité d’informations à stocker peut s’avérer trop volumineuse pour le cache de SQL Server. Par conséquent, si tempDB est placé sur un LUN favorisant peu les performances en écriture, cela peut avoir un impact significatif sur la durée d’exécution du processus de vérification de l’intégrité de la base. Il est possible d’optimiser tempDB en créant entre 0,25 et 1 fichier de données tempDB par CPU afin de permettre à SQL Server d’utiliser des threads parallèles (à raison d’un thread par fichier) pour accéder et traiter rapidement les opérations au sein de tempDB.
  • Choix de l’option de vérification de l’intégrité d’une base (DBCC CHECKDB): la durée d’exécution d’un processus de vérification de l’intégrité d’une base peut varier selon l’option de vérification choisie. Plus de détails ici : http://msdn.microsoft.com/fr-fr/library/ms176064.aspx.

          Choix du scénario de vérification de l’intégrité d’une base de données

Au regard des principaux facteurs énumérés dans la section précédemment, il convient de bien choisir la stratégie de vérification de l’intégrité d’une base de données si des problèmes de lenteurs sont fréquemment observées durant le lancement d’un processus de vérification.

Supposons que l’on dispose d’une base de données plutôt volumineuse, et dont le traitement de vérification de son intégrité entraîne un dépassement régulier des limites de la fenêtre de maintenance qui lui est dédiée du fait de sa longueur, il existe donc diverses approches pour palier à ce problème :

  • Vérification de l’intégrité des données basée sur le contrôle des filegroups avec l’option CHECKFILEGROUP de DBCC CHECKDB : si la structure de la base de données le permet (i.e., présence de plusieurs filegroups correctement conçus et adaptés, c’est-à-dire, avec un certain nombre de tables et/ou d’indexes placés sur des filegroups dédiés selon leur spécificité, comme leur volumétrie), il est possible de procéder à une vérification de l’intégrité de tous les objets (tables et/ou indexes) d’un filegroup donné durant un ou des jours spécifiques et en faire de même sur les autres filegroups durant d’autres jours. Le but étant de bien répartir la vérification de l’intégrité des objets des différents filegroups sur plusieurs jours.
  • Vérification de l’intégrité physique de la base de données via l’option PHYSICAL_ONLY de DBCC CHECKDB : il s’agit, ici, de gagner du temps en ignorant l’étape de vérification logique de la base de données pour ne se concentrer que sur la vérification des structures physiques des pages de données, d’allocation et d’indexes, ainsi que des en-têtes et détecter tout problème de corruption matérielle.
  • Vérification de l’intégrité complète d’une base de données sur un serveur secondaire dédié, puis, en fin de traitement, restauration d’une copie de cette base sur le serveur principal. Soulignons tout de même que cette solution peut s’avérer coûteuse en temps (si la base est trop longue à restaurer car volumineuse) et en termes économiques (un second serveur dédié devant être déployé…).
  • Vérification des pages d’allocation, des catalogues et des objets (tables, vues, indexes) séparément : cette stratégie peut s’avérer être très utile si aucune des autres stratégies précédemment citées ne sont satisfaites (durée toujours longue, pas de filegroups multiples ou inadaptés, etc…). L’idée est de « scinder » la base de données à traiter en lots, puis de planifier le traitement desdits lots par chacune des 3 phases (DBCC CHECKALLOC, DBCC CHECKTABLE et DBCC CHECKCATALOG) de DBCC CHECKDB séparément.

    Dans notre vocabulaire, un lot fait référence à un ensemble d’objets (tables, indexes, vues,…) d’une base de données. Le choix du contenu des lots dépend bien sûr du modèle de la base de données et, surtout, du profil de ses objets (tables volumineuses, quantité de pages,…).

    Pour se faciliter la tâche durant la définition des lots, il faut répérer les tables les plus importantes en fonction de leur nombre de pages puis les regrouper sous forme de lots de taille équivalente, puis planifier les différentes étapes de vérification de l’intégrité des données par lot durant des périodes de faible activité.

Pour mieux comprendre la logique de cette stratégie, imaginons que l’on dispose d’une base avec seulement 6 tables : T1, T2, T3, T4, T5 et T6 où les 3 premières sont les plus volumineuses en termes de pages à traiter. On peut scinder la base de données en 3 lots chacune dédiée à une table volumineuse différente parmi T1, T2 et T3, plus une table moins volumineuse. Voici un exemple de planification des différentes étapes de vérification de l’intégrité des données par lot :


Remarquons que l’utilisation de DBCC CHECKDB pour la vérification complète de la base de données dans notre exemple suppose qu’il y ait une fenêtre de maintenance acceptée un dimanche (jour généralement peu surchargé en activité dans le monde professionnel). Si ce n’est pas le cas, on peut utiliser l’option PHYSICAL_ONLY pour gagner du temps. Mais si l’option ne suffit pas, on peut finalement planifier un autre lot (plus DBCC CHECKALLOC et DBCC CHECKCATALOG).

Résolution de problèmes de corruption

En cas de corruption détectée, 2 possibilités principales existent pour y faire face :

  • Soit, et il s’agit de la solution la plus saine, de restaurer la base de données concernée à partir d’une dernière sauvegarde valide :
    • Restauration point-in-time (en utilisant l’option STOPAT de RESTORE) si la base est en mode de récupération FULL (présence de sauvegardes transactionnelles recommandée).
    • Restauration à partir de la dernière sauvegarde transactionnelle (si en FULL ou en BULK-LOGGED).
    • Restauration à partir de la dernière sauvegarde complète, si la base de données est en SIMPLE.
  • Soit, si pas de sauvegarde valide ou dernière sauvegarde trop vieille, de lancer DBCC
    CHECKDB avec l’option REPAIR_REBUILD sur la base concernée, ce qui va lancer une tentative de réparation rapide des pages concernées, avec une potentielle reconstruction des indexes sans perte de données.

Par exemple, si l’on obtient l’erreur suivante :

Object ID 2834513445, index ID 0, partition ID 25647287010503993, alloc unit ID 3455602053591456 (type In-row data): Page (1:73) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 283451, index ID 0, partition ID 25647287010503993, alloc unit ID 3455602053591456 (type In-row data), page (1: 73). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘MaTable’ (object ID 2834513445).
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘MaBase’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MaBase).

Après avoir identifié la page concernée (ici 73), l’étape suivante sera de savoir s’il s’agit d’une page de données ou bien d’une page d’index (en cluster ou non). Pour cela, il suffira d’utiliser DBCC PAGE :

DBCC TRACEON (3604, -1)
GO
DBCC PAGE('MaBase', 1, 73, 3)
GO

Puis de surveiller la valeur de la colonne IndexId. Ainsi, si valeur supérieure à 1, alors il s’agit d’une page d’index non-cluster, et il suffira simplement de le recréer pour régler le problème. Par contre, s’il s’agit d’un heap (valeur égale à 0) ou d’un index en cluster (valeur égale à 1), il faudra appliquer l’une des 2 solutions citées plus haut :

  • Si le message d’erreur de DBCC CHECKDB précise (et ce n’est guère notre cas, ici) que le niveau de réparation minimal est REPAIR_REBUILD, il suffira simplement de lancer cette ligne de commande T-SQL :
DBCC CHECKDB('MaBase', REPAIR_REBUILD)
GO
  • Si le message d’erreur de DBCC CHECKDB précise (et ceci est notre cas, ici) que le niveau de réparation minimal est REPAIR_ALLOW_DATA_LOSS, il suffira alors, si et seulement s’il n’y a aucune sauvegarde récente valide pour effectuer une restauration de la base de données, de lancer cette ligne de commande T-SQL :
DBCC CHECKDB('MaBase', REPAIR_ALLOW_DATA_LOSS)
GO
Attention aux problèmes d’intégrité référentielle (l’opération « désallouant » la page concernée tout modifiant ses liens). Utilisez, avant, DBCC CHECKCONSTRAINTS afin de prendre pleinement conscience des problèmes potentiels d’intégrité référentielle avant toute décision.

Notons qu’il existe également d’autres possibilités de « réparation » comme l’export de données vers une base de données vide (mais structurellement identique à l’originale), mais cela reste une opération fastidieuse en termes de temps si la base de données contient beaucoup de données à traiter.



[1] Pour connaître le détail des options associées à DBCC CHECKDB, référez-vous au BOL : http://msdn.microsoft.com/fr-fr/library/ms176064.aspx. Utilisez l’option NO_INFOMSGS si vous ne souhaitez pas être spammé de messages d’information.
Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s