Combien de temps dois-je m'attendre à ce que CheckDB fonctionne avec REPAIR_ALLOW_DATA_LOSS ?

Ha - ma question (non) préférée que l'on me pose (alors que j'ai écrit DBCC CHECKDB).

Voilà :

Il n'y a qu'un seul moment où vous devriez essayer de calculer la durée d'un CHECKDB : lorsque vous planifiez la maintenance régulière de votre base de données. Si vous êtes confronté à une base de données corrompue (ou soupçonnée de l'être) et que vous ne faites que commencer à réfléchir à la durée d'un CHECKDB, vous avez commis une erreur en planifiant votre stratégie de reprise après sinistre. Vous devez toujours savoir combien de temps prend (en moyenne) l'exécution de CHECKDB pour votre base de données donc :

• Vous pouvez savoir si une exécution particulière de CHECKDB prend plus de temps que d'habitude - un signe qu'il a trouvé une corruption.
• Vous savez combien de temps il faudra pour obtenir des résultats dans une situation de reprise après sinistre.

À chaque conférence à laquelle je me rends, quelqu'un me demande combien de temps prendra l'exécution de CHECKDB sur sa base de données. Je peux répondre à cette question de plusieurs façons :

• La réponse inutile - Je n'en ai aucune idée.
• La réponse presque utile : combien de temps a-t-il fallu pour courir la dernière fois et les conditions sont-elles exactement les mêmes ?
• La réponse que je donne habituellement est : cela dépend.

De nombreuses personnes considéreraient la troisième réponse comme équivalente à la première, c'est-à-dire inutile. Le problème est qu'il existe de nombreux facteurs qui influencent la durée d'exécution de CHECKDB. Laissez-moi vous expliquer les dix facteurs les plus importants afin que vous compreniez pourquoi cette réponse est en fait utile. Ces facteurs ne sont pas classés par ordre d'importance.

1. La taille de la base de données Plutôt évident... CHECKDB doit lire chaque page allouée dans la base de données, donc plus elle est grande, plus il lui faudra du temps pour lire toutes les pages.
2. Charge d'E/S simultanée sur le serveur Au niveau le plus simple, que va faire CHECKDB ? Il lit chaque page allouée dans la base de données. Cela représente beaucoup d'entrées-sorties. CHECKDB s'efforce de réaliser l'E/S la plus efficace possible et de lire les pages de la base de données dans leur ordre physique, avec une avance de lecture suffisante pour que les têtes de disques se déplacent en douceur sur les disques (plutôt que de sauter aléatoirement et de provoquer des retards dans la recherche des têtes de disques). S'il n'y a pas de charge d'E/S simultanée sur le serveur, les E/S seront aussi efficaces que CHECKDB peut le faire. Cependant, l'introduction d'entrées-sorties supplémentaires à partir du serveur SQL signifie que les têtes de disques vont se déplacer et ralentir les entrées-sorties de CHECKDB. Si le sous-système d'E/S est déjà à pleine capacité en raison des demandes d'E/S de CHECKDB, toute E/S supplémentaire réduira la bande passante d'E/S disponible pour CHECKDB, ce qui le ralentira.
3. Activité simultanée des CPU sur le serveur Au niveau de simplicité suivant, CHECKDB va traiter chaque page qu'il lit d'une manière ou d'une autre. Selon les différentes options que vous avez spécifiées et le schéma de la base de données (détails ci-dessous), cela va utiliser beaucoup de CPU - il est possible que le serveur soit bloqué à 100% du CPU lorsque CHECKDB est en cours d'exécution. S'il y a une charge de travail supplémentaire sur le serveur, cela va prendre des cycles CPU à CHECKDB et le ralentir. En gros, ce que les points 2 et 3 disent, c'est que CHECKDB est très gourmand en ressources ! C'est probablement l'une des choses les plus gourmandes en ressources que vous pouvez demander à SQL Server de faire et c'est pourquoi il est généralement préférable de ne pas l'exécuter pendant les périodes de pointe, car non seulement CHECKDB prendra plus de temps à s'exécuter, mais vous ralentirez la charge de travail simultanée, peut-être de manière inacceptable.
4. Activité de mise à jour simultanée sur la base de données Ceci est pertinent pour SQL 2000 et SQL 2005, mais pour des raisons différentes. En SQL 2000, CHECKDB obtient une vue cohérente de la base de données à partir de l'analyse du journal des transactions des transactions DML concurrentes (voir ici pour plus de détails). Plus il y a de DML simultanés pendant que CHECKDB est en cours d'exécution, plus le journal des transactions sera généré - et donc plus il faudra de temps à CHECKDB pour analyser ce journal des transactions. Il est possible que sur une grande machine multi-CPU avec une tonne de DML simultanés et CHECKDB limité à un seul CPU, cette phase de CHECKDB puisse prendre plusieurs fois plus de temps que la lecture et le traitement des pages de la base de données ! (J'ai vu cela plusieurs fois dans la vie réelle). Dans SQL 2005, CHECKDB obtient sa vue cohérente de la base de données à partir d'un instantané de la base de données, qui est stocké sur les mêmes volumes de disque que la base de données elle-même. Si de nombreux changements sont apportés à la base de données pendant que CHECKDB est en cours d'exécution, les pages modifiées sont poussées vers le snapshot afin qu'il reste cohérent. Comme les fichiers d'instantanés sont stockés au même endroit que les fichiers de la base de données, chaque fois qu'une page est poussée vers l'instantané, la tête de disque doit se déplacer, ce qui interrompt l'entrée-sortie efficace décrite au point 2. De plus, chaque fois que CHECKDB lit une page et qu'il doit lire la page à partir des fichiers d'instantanés au lieu des fichiers de la base de données, il y a un autre déplacement de la tête de disque, et une autre interruption de l'E/S efficace. Plus il y a de modifications simultanées de la base de données, plus il y a d'interruptions de l'E/S efficace et plus CHECKDB est lent.
5. Capacités de débit du sous-système IO Celui-là est simple. CHECKDB va effectuer un grand nombre d'entrées-sorties et pourrait même finir par être lié aux entrées-sorties (ce qui signifie que les processeurs sont périodiquement inactifs en attendant que les entrées-sorties se terminent) selon les options spécifiées et le schéma de la base de données. Cela signifie que le débit du sous-système d'E/S va avoir un effet direct sur le temps d'exécution de CHECKDB. Ainsi, si vous avez une base de données de 1TB et que le sous-système d'E/S ne peut gérer que 100MB/sec, la lecture de la base de données va prendre presque 3 heures (1TB / 100MB / 3600 sec) et il n'y a rien que vous puissiez faire pour accélérer cela, sauf mettre à jour le sous-système d'E/S. J'ai perdu le compte du nombre de fois où j'ai entendu des clients se plaindre que CHECKDB (ou des reconstructions d'index ou d'autres opérations lourdes en E/S) tournait lentement pour découvrir que les files d'attente sur le disque étaient énormes et que le sous-système d'E/S n'était absolument pas adapté au serveur et à la charge de travail.
6. Le nombre de CPU (cœurs de traitement) sur le boîtier Cela englobe également l'édition de SQL Server qui est exécutée. Dans l'édition Enterprise, CHECKDB peut s'exécuter en parallèle sur tous les processeurs de la boîte (ou autant que le processeur de requête décide de paralléliser lorsque les requêtes internes CHECKDB sont compilées). L'exécution en parallèle peut améliorer considérablement les performances de CHECKDB et réduire les temps d'exécution, à condition que la base de données soit également répartie sur plusieurs fichiers (afin que les entrées-sorties puissent être parallélisées). Il existe un algorithme astucieux qui permet à CHECKDB de fonctionner en parallèle et que j'expliquerai en détail dans un prochain article. D'un autre côté, le fait que CHECKDB puisse fonctionner en parallèle dans Enterprise Edition peut être mauvais pour certains scénarios, et certains DBA ont donc choisi de forcer CHECKDB à être single-threaded. SAP recommande généralement cette solution afin d'améliorer la prévisibilité des requêtes des utilisateurs. Pour ce faire, il suffit d'activer l'indicateur de trace documenté 2528.
7. La vitesse des disques où tempdb est placé L'exécution de CHECKDB contre une VLDB utilise beaucoup de mémoire pour l'état interne et, pour les VLDB, les besoins en mémoire dépassent généralement la quantité de mémoire disponible pour SQL Server. Dans ce cas, l'état est spoilé vers tempdb et donc la performance de tempdb peut être un facteur critique dans la performance de CHECKDB. Voir cet article pour plus de détails à ce sujet et comment CHECKDB peut manquer d'espace disque si tempdb est trop petit.
8. La complexité du schéma de la base de données Cela peut avoir un impact très important sur la durée d'exécution de CHECKDB car cela a un impact sur la quantité de CPU que CHECKDB requiert. Par exemple, les vérifications les plus coûteuses effectuées par CHECKDB concernent les index non groupés. Il doit vérifier que chaque ligne d'un index non clusterisé correspond à exactement une ligne de l'index heap ou clusterisé de la table, et que chaque ligne de l'index heap/clustered a exactement une ligne correspondante dans chaque index non clusterisé. Bien qu'il existe un algorithme très efficace pour réaliser cette opération, elle prend tout de même environ 30% du total du CPU utilisé par CHECKDB ! Il existe un grand nombre d'autres vérifications qui ne sont effectuées que si les fonctionnalités ont été utilisées dans la base de données - par exemple, l'évaluation des colonnes calculées, les liens entre les valeurs LOB hors ligne, Service Broker, les index XML, les vues indexées - vous pouvez donc constater que les facteurs empiriques ne suffisent pas à déterminer le temps d'exécution.
9. Quelles options sont spécifiées C'est presque la même chose que le point 7, car en spécifiant diverses options, vous limitez les contrôles que CHECKDB effectue réellement. Par exemple, l'utilisation de l'option WITH NOINDEX désactivera les vérifications d'index non groupés que j'ai décrites au point 7 et l'utilisation de l'option WITH PHYSICAL_ONLY désactivera toutes les vérifications logiques, réduisant considérablement le temps d'exécution de CHECKDB et le rendant presque toujours lié aux entrées/sorties plutôt qu'au processeur (en fait, c'est l'option la plus courante que les DBA des VLDB utilisent pour rendre le temps d'exécution de CHECKDB gérable). Une chose dont il faut être conscient : si vous spécifiez des options de réparation, CHECKDB s'exécute toujours en mode monofil, même sur une machine multiprocesseur sous Enterprise Edition.
10. Le nombre et le type de corruptions qui existent dans la base de données. Encore une fois, ceci est similaire aux points 7 et 8. S'il y a des corruptions, des vérifications supplémentaires peuvent être déclenchées pour essayer de comprendre plus de détails sur les corruptions. Par exemple, pour les vérifications d'index non groupés, l'algorithme est réglé de manière très stricte pour le cas où il n'y a pas de corruption (la grande majorité des cas, compte tenu des millions de fois où CHECKDB est exécuté chaque jour dans le monde). Lorsqu'une corruption d'index non groupé est détectée, un algorithme plus approfondi doit être utilisé pour déterminer exactement où se trouve la corruption, ce qui implique une nouvelle analyse d'un grand nombre de données et prend donc beaucoup plus de temps. Il existe quelques autres algorithmes de ce type.

Une autre chose à garder à l'esprit est que l'utilisation de REPAIR_ALLOW_DATA_LOSS fait que les contrôles sont exécutés en mode monofil, de sorte que les réparations sont ordonnées correctement, ce qui allonge la durée de l'opération. Jetez un coup d'œil au message 5268 dans le journal des erreurs de 2005 SP2+ - il indique une plongée profonde, comme je l'ai mentionné ci-dessus.

Résumé Vous voyez donc qu'il n'y a pas de réponse simple. J'espère que cela vous aidera !

PS J'ai oublié de dire qu'en SQL 2005, j'ai ajouté le rapport de progression à DBCC CHECKDB. Vous pouvez interroger le sys.dm_exec_requests DMV et recherchez le percent_complete colonne.