SQL Server diagnostics détecte les problèmes d’E/S non signalés dus à des lectures obsolètes ou à des écritures perdues

Article
06/20/2023

Cet article explique comment SQL Server Diagnostics permet de détecter les problèmes d’entrée ou de sortie non signalés qui se produisent en raison de lectures obsolètes ou d’écritures perdues.

Version du produit d’origine : SQL Server
Numéro de la base de connaissances d’origine : 826433

Symptômes

Si des problèmes de système d’exploitation, de pilote ou de matériel entraînent une perte d’écriture ou des conditions de lecture obsolètes dans le chemin d’E/S, vous pouvez voir des messages d’erreur liés à l’intégrité des données tels que les erreurs 605, 823, 3448 et 3456 dans SQL Server. Vous pouvez recevoir des messages d’erreur similaires aux exemples suivants :

2003-07-24 16:43:04.57 spid63 Getpage: bstat=0x9, sstat=0x800, cache
2003-07-24 16:43:04.57 spid63 pageno is/should be: objid is/should be:
2003-07-24 16:43:04.57 spid63 (1:7040966)/(1:7040966) 2093354622/2039782424
2003-07-24 16:43:04.57 spid63 ... IAM indicates that page is allocated to this object
2003-07-24 16:52:37.67 spid63 Error: 605, Severity: 21, State: 1
2003-07-24 16:52:37.67 spid63 Attempt to fetch logical page (1:7040966) in database 'pubs' belongs to object 'authors', not to object 'titles'..
2003-07-24 16:52:40.99 spid63 Error: 3448, Severity: 21, State: 1
2003-07-24 16:52:40.99 spid63 Could not undo log record (63361:16876:181), for transaction ID (0:159696956), on page (1:7040977), database 'pubs' (database ID 12). Page information: LSN = (63192:958360:10), type = 2. Log information: OpCode = 2, context 1..
2003-07-09 14:31:35.92 spid66 Error: 823, Severity: 24, State: 2
2003-07-09 14:31:35.92 spid66 I/O error (bad page ID) detected during read at offset 0x00000016774000 in file 'h:\sql\MSSQL\data\tempdb.mdf'..
2010-02-06 15:57:24.14 spid17s Error: 3456, Severity: 21, State: 1.
2010-02-06 15:57:24.14 spid17s Could not redo log record (58997:5252:28), for transaction ID (0:109000187), on page (1:480946), database 'MyDatabase' (database ID 17). Page: LSN = (58997:5234:17), type = 3. Log: OpCode = 2, context 5, PrevPageLSN: (58997:5243:17). Restore from a backup of the database, or repair the database.

Nouvelles fonctionnalités de diagnostic d’E/S dans SQL Server

SQL Server introduit de nouvelles fonctionnalités de diagnostic d’E/S à partir de SQL Server Service Pack 4 2000 et ces diagnostics font partie du produit depuis lors. Ces fonctionnalités sont conçues pour vous aider à détecter les problèmes liés aux E/S externes et à résoudre les messages d’erreur décrits dans la section Symptômes .

Si vous recevez l’un des messages d’erreur répertoriés dans la section Symptômes et qu’ils ne sont pas expliqués par un événement tel qu’une défaillance de lecteur physique, passez en revue les problèmes connus avec SQL Server, le système d’exploitation, les pilotes et le matériel. Le diagnostics essayer de fournir des informations sur les deux conditions suivantes :

Écriture perdue : appel réussi à l’API WriteFile, mais le système d’exploitation, un pilote ou le contrôleur de mise en cache ne vide pas correctement les données sur le support physique, même si SQL Server est informé que l’écriture a réussi.
Lecture obsolète : appel réussi à l’API ReadFile, mais le système d’exploitation, un pilote ou le contrôleur de mise en cache retourne une version antérieure des données de manière incorrecte.

À titre d’exemple, Microsoft a confirmé des scénarios où un appel d’API WriteFile retourne une status de réussite, mais une lecture immédiate et réussie du même bloc de données retourne des données plus anciennes, y compris des données probablement stockées dans un cache de lecture matériel. Parfois, ce problème se produit en raison d’un problème de cache de lecture. Dans d’autres cas, les données d’écriture ne sont jamais écrites sur le disque physique.

Comment activer le diagnostics

Dans SQL Server 2017 et versions ultérieures, cette fonctionnalité de diagnostic est activée par défaut. Dans SQL Server 2016 et les versions antérieures, ces diagnostics ne peuvent être activées qu’à l’aide de l’indicateur de trace 818. Vous pouvez spécifier l’indicateur de trace 818 en tant que paramètre de démarrage, -T818, pour le SQL Server instance, ou vous pouvez exécuter l’instruction T-SQL suivante pour les activer au moment de l’exécution :

DBCC TRACEON(818, -1)

L’indicateur de trace 818 active une mémoire tampon en anneau en mémoire utilisée pour suivre les 2 048 dernières opérations d’écriture réussies effectuées par l’ordinateur exécutant SQL Server, sans compter les E/S de tri et de fichier de travail. Lorsque des erreurs telles que 605, 823 ou 3448 se produisent, la valeur du numéro séquentiel de journal (LSN) de la mémoire tampon entrante est comparée à la liste d’écritures récente. Si le LSN récupéré pendant l’opération de lecture est antérieur à celui utilisé dans l’opération d’écriture, un nouveau message d’erreur est enregistré dans le journal des erreurs SQL Server. La plupart des opérations d’écriture SQL Server se produisent sous forme de points de contrôle ou d’écritures différées (une écriture différée est une tâche en arrière-plan qui utilise des E/S asynchrones). L’implémentation de la mémoire tampon en anneau est légère et l’effet sur les performances sur le système est négligeable.

Détails sur le message dans le journal des erreurs

Le message suivant n’affiche aucune erreur explicite de l’API WriteFile ou des appels d’API ReadFile qui SQL Server. Au lieu de cela, il affiche une erreur d’E/S logique qui s’est produite lorsque le LSN a été examiné et que sa valeur attendue n’était pas correcte :

À compter de SQL Server 2005, le message d’erreur affiché est le suivant :

SQL Server détecté une erreur d’E/S basée sur la cohérence logique : Lecture obsolète. Cela s’est produit pendant une <Read/Write> de page <PAGEID> dans l’ID <DBID> de base de données au décalage <PHYSICAL OFFSET> dans le fichier <FILE NAME>. Des messages supplémentaires dans le journal des erreurs SQL Server ou le journal des événements système peuvent fournir plus de détails. Il s’agit d’une condition d’erreur grave qui menace l’intégrité de la base de données et doit être corrigée immédiatement. Effectuez une case activée complète de cohérence de base de données (DBCC CHECKDB). Cette erreur peut être due à de nombreux facteurs. Pour plus d’informations, consultez SQL Server documentation en ligne.

Pour plus d’informations sur l’erreur 824, consultez MSSQLSERVER_824.

Au moment où vous signalez cette erreur, soit le cache de lecture contient une version antérieure de la page, soit les données n’ont pas été correctement écrites sur le disque physique. Dans les deux cas (une écriture perdue ou une lecture obsolète), SQL Server signale un problème externe avec le système d’exploitation, le pilote ou les couches matérielles.

Si l’erreur 3448 se produit lorsque vous essayez de restaurer une transaction avec l’erreur 605 ou 823, le SQL Server instance ferme automatiquement la base de données et tente de l’ouvrir et de la récupérer. La première page qui rencontre l’erreur 605 ou 823 est considérée comme incorrecte, et l’ID de page est conservé par l’ordinateur exécutant SQL Server. Pendant la récupération (avant la phase de restauration) lorsque l’ID de page incorrect est lu, les détails principaux de l’en-tête de page sont enregistrés dans le journal des erreurs SQL Server. Cette action est importante, car elle permet de faire la distinction entre les scénarios d’écriture perdue et de lecture obsolète.

Comportement observé avec des lectures obsolètes et des écritures perdues

Vous pouvez voir les deux comportements courants suivants dans les scénarios de lecture obsolètes :

Si les fichiers de base de données sont fermés, puis ouverts, les données correctes et les plus récemment écrites sont retournées lors de la récupération.
Lorsque vous émettez un point de contrôle et exécutez l’instruction DBCC DROPCLEANBUFFERS (pour supprimer toutes les pages de base de données de la mémoire), puis exécutez l’instruction DBCC CHECKDB sur la base de données, les données écrites les plus récemment sont retournées.

Les comportements mentionnés dans le paragraphe précédent indiquent un problème de mise en cache de lecture et sont fréquemment résolus en désactivant le cache de lecture. Les actions décrites dans le paragraphe précédent forcent généralement une invalidation du cache et les lectures réussies qui se produisent montrent que le média physique est correctement mis à jour. Le comportement d’écriture perdu se produit lorsque la page qui est lue est toujours l’ancienne version des données, même après un vidage forcé des mécanismes de mise en cache.

Parfois, le problème peut ne pas être spécifique à un cache matériel. Il peut s’agir d’un problème avec un pilote de filtre. Dans ce cas, passez en revue vos logiciels, y compris les utilitaires de sauvegarde et les logiciels antivirus, puis vérifiez s’il existe des problèmes avec le pilote de filtre.

Description de divers scénarios de lectures obsolètes et d’écritures perdues

Microsoft a également noté des conditions qui ne répondent pas aux critères de l’erreur 605 ou 823, mais qui sont provoquées par la même activité de lecture obsolète ou d’écriture perdue. Dans certains cas, une page semble être mise à jour deux fois, mais avec la même valeur LSN. Ce comportement peut se produire si l’ID d’objet et l’ID de page sont corrects (page déjà allouée à l’objet) et si une modification est apportée à la page et vidée sur le disque. La récupération de page suivante retourne une image plus ancienne, puis une deuxième modification est apportée. Le journal des transactions SQL Server indique que la page a été mise à jour deux fois avec la même valeur LSN. Cette action devient un problème lorsque vous essayez de restaurer une séquence de journal des transactions ou avec des problèmes de cohérence des données, tels que des échecs de clé étrangère ou des entrées de données manquantes. Le message d’erreur suivant illustre un exemple de cette condition :

Erreur : 3456, Gravité : 21, État : 1 Impossible de rétablir l’enregistrement du journal (276666 :1664 :19), pour l’ID de transaction (0 :825853240), à la page (1 :1787100), à la base de données « authors » (7). Page : LSN = (276658 :4501 :9), tapez = 1. Journal : OpCode = 4, context 2, PrevPageLSN : (275565 :3959 :31)..

Certains scénarios sont décrits plus en détail dans les listes suivantes :

LSN SequenceAction
1   Checkpoint
2   Begin Transaction
3   Table created or truncated
4   Inserts (Pages allocated)
5   Newly allocated page written to disk by Lazy Writer
6   Select from table - Scans IAM chain, newly allocated page read back from disk (LRU | HASHED = 0x9 in getpage message), encounters Error 605 - Invalid Object ID
7   Rollback of transaction initiated

LSN SequenceAction
1   Checkpoint
2   Begin Transaction
3   Page Modification
4   Page written to disk by Lazy Writer
5   Page read in for another modification (stale image returned)
6   Page Modified for a second time but because of stale image does not see first modification 
7   Rollback - Fails - Transaction Log shows two different log records with the same PREV LSN for the page

sort SQL Server opérateurs effectuent des activités d’E/S, généralement dans la tempdb base de données. Ces opérations d’E/S sont similaires aux opérations d’E/S de la mémoire tampon ; Toutefois, ils ont déjà été conçus pour utiliser la logique de nouvelle tentative de lecture pour essayer de résoudre des problèmes similaires. Les diagnostics supplémentaires expliquées dans cet article ne s’appliquent pas à ces opérations d’E/S.

Microsoft a noté que la cause racine des échecs de lecture de tri suivants est généralement une lecture obsolète ou une écriture perdue :

2003-04-01 20:13:31.38 spid122 SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447 Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
2003-03-29 09:51:41.12 spid57 Sort read failure (bad page ID). pageid = (0x1:0x13e9), dbid = 2, file = e:\program files\Microsoft SQL Server\mssql\data\tempdb.mdf. Retrying.
2003-03-29 09:51:41.13 spid57 Error: 823, Severity: 24, State: 7
2003-03-29 09:51:41.13 spid57 I/O error (bad page ID) detected during read at offset 0x000000027d2000 in file 'e:\program files\Microsoft SQL Server\mssql\data\tempdb.mdf'..
* 00931097 Module(sqlservr+00531097) (utassert_fail+000002E3)
* 005B1DA8 Module(sqlservr+001B1DA8) (RecBase::Resize+00000091)
* 00407EE7 Module(sqlservr+00007EE7) (RecBase::LocateColumn+00000012)
* 00852520 Module(sqlservr+00452520) (mergerow+000000A4)
* 008522B3 Module(sqlservr+004522B3) (merge_getnext+00000285)
* 0085207D Module(sqlservr+0045207D) (mergenext+0000000D)
* 004FC5FB Module(sqlservr+000FC5FB) (getsorted+00000021)

Étant donné qu’une lecture obsolète ou une perte d’écriture entraîne un stockage de données qui n’est pas attendu, un large éventail de comportements peut se produire. Il peut apparaître comme des données manquantes, mais certains des effets les plus courants des données manquantes apparaissent comme des altérations d’index, telles que l’erreur 644 ou 625 :

Erreur 644 Niveau de gravité 21 Texte du message Impossible de trouver l’entrée d’index pour RID '%.*hs' dans la page d’index %S_PGID, ID d’index %d, base de données '%.*ls'.

Erreur 625 Niveau de gravité 21 Texte du message Impossible de récupérer la ligne de la page %S_PGID par RID, car le slotid (%d) n’est pas valide.

Certains clients ont signalé des lignes manquantes après avoir effectué des activités de comptage de lignes. Ce problème se produit en raison d’une écriture perdue. Peut-être que la page était censée être liée à la chaîne de pages d’index cluster. Si l’écriture a été physiquement perdue, les données sont également perdues.

Importante

Si vous rencontrez l’un des comportements ou si vous vous méfiez de problèmes similaires liés à la désactivation des mécanismes de mise en cache, Microsoft vous recommande vivement d’obtenir la dernière mise à jour pour SQL Server. Microsoft vous encourage également vivement à effectuer une révision stricte de votre système d’exploitation et de ses configurations associées.

Notez que Microsoft a confirmé qu’en cas de charges d’E/S rares et lourdes, certaines plateformes matérielles peuvent retourner une lecture obsolète. Si le diagnostics étendu indique une éventuelle condition de lecture obsolète ou de perte d’écriture, contactez votre fournisseur de matériel pour un suivi et un test immédiats avec l’utilitaire SQLIOSim.

SQL Server exige que les systèmes prennent en charge la livraison garantie aux médias stables, comme indiqué dans les exigences du programme de fiabilité d’E/S SQL Server. Pour plus d’informations sur les exigences d’entrée et de sortie pour le moteur de base de données SQL Server, consultez Moteur de base de données Microsoft SQL Server Exigences d’entrée/sortie.