La diagnostica di SQL Server rileva problemi di I/O non segnalati a causa di letture non aggiornati o scritture perse

Articolo
12/14/2024

Questo articolo illustra in che modo la diagnostica di SQL Server consente di rilevare problemi di input o output non segnalati che si verificano a causa di letture non aggiornati o scritture perse.

Versione originale del prodotto: SQL Server
Numero KB originale: 826433

Sintomi

Se i problemi del sistema operativo, del driver o dell'hardware causano condizioni di lettura in scrittura o non aggiornate nel percorso di I/O, è possibile che vengano visualizzati messaggi di errore correlati all'integrità dei dati, ad esempio errori 605, 823, 3448 e 3456 in SQL Server. È possibile che vengano visualizzati messaggi di errore simili agli esempi seguenti:

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.

Nuove funzionalità di diagnostica di I/O in SQL Server

SQL Server ha introdotto nuove funzionalità di diagnostica di I/O a partire da SQL Server 2000 Service Pack 4 e queste funzionalità di diagnostica fanno parte del prodotto da allora. Queste funzionalità sono progettate per consentire di rilevare problemi di I/O esterni e di risolvere i messaggi di errore descritti nella sezione Sintomi .

Se si riceve uno dei messaggi di errore elencati nella sezione Sintomi e non sono spiegati da un evento come un errore di unità fisica, esaminare eventuali problemi noti con SQL Server, il sistema operativo, i driver e l'hardware. La diagnostica tenta di fornire informazioni sulle due condizioni seguenti:

Scrittura persa: una chiamata riuscita all'API WriteFile, ma il sistema operativo, un driver o il controller di memorizzazione nella cache non scarica correttamente i dati nei supporti fisici anche se SQL Server viene informato che la scrittura è riuscita.
Lettura non aggiornata: una chiamata riuscita all'API ReadFile, ma il sistema operativo, un driver o il controller di memorizzazione nella cache restituisce erroneamente una versione precedente dei dati.

Per illustrare, Microsoft ha confermato scenari in cui una chiamata API WriteFile restituisce uno stato di esito positivo, ma una lettura immediata dello stesso blocco di dati restituisce dati meno recenti, inclusi i dati probabilmente archiviati in una cache di lettura hardware. In alcuni casi, questo problema si verifica a causa di un problema di lettura della cache. In altri casi, i dati di scrittura non vengono mai scritti nel disco fisico.

Come abilitare la diagnostica

In SQL Server 2017 e versioni successive questa funzionalità di diagnostica è abilitata per impostazione predefinita. In SQL Server 2016 e versioni precedenti queste funzionalità di diagnostica possono essere abilitate solo usando il flag di traccia 818. È possibile specificare il flag di traccia 818 come parametro di avvio, -T818, per l'istanza di SQL Server oppure eseguire l'istruzione T-SQL seguente per abilitarle in fase di esecuzione:

DBCC TRACEON(818, -1)

Il flag di traccia 818 abilita un buffer circolare in memoria usato per tenere traccia delle ultime 2.048 operazioni di scrittura riuscite eseguite dal computer che esegue SQL Server, senza includere operazioni di ordinamento e I/O del file di lavoro. Quando si verificano errori come 605, 823 o 3448, il valore LSN (Log Sequence Number) del buffer in ingresso viene confrontato con l'elenco di scrittura recente. Se l'LSN recuperato durante l'operazione di lettura è precedente a quello usato nell'operazione di scrittura, viene registrato un nuovo messaggio di errore nel log degli errori di SQL Server. La maggior parte delle operazioni di scrittura di SQL Server si verifica come checkpoint o come scritture lazy (una scrittura differita è un'attività in background che usa operazioni di I/O asincrone). L'implementazione del buffer circolare è leggera e l'effetto sulle prestazioni sul sistema è trascurabile.

Dettagli sul messaggio nel log degli errori

Il messaggio seguente non mostra errori espliciti dall'API WriteFile o dall'API ReadFile chiama sql Server. Mostra invece un errore di I/O logico che ha generato quando è stato esaminato l'LSN e il relativo valore previsto non è corretto:

A partire da SQL Server 2005, il messaggio di errore visualizzato è:

SQL Server ha rilevato un errore di I/O basato sulla coerenza logica: lettura non aggiornata. Si è verificato durante una <Read/Write> pagina <PAGEID> nell'ID <DBID> del database in corrispondenza dell'offset <PHYSICAL OFFSET> nel file <FILE NAME>. Per informazioni più dettagliate, vedere i messaggi aggiuntivi nel log degli errori di SQL Server e nel registro eventi di sistema. Si tratta di una condizione di errore grave che può compromettere l'integrità del database e che deve essere corretta immediatamente. Eseguire una verifica di coerenza del database (DBCC CHECKDB). Questo errore può essere causato da molti fattori. Per ulteriori informazioni, vedere la documentazione online di SQL Server.

Per altre informazioni sull'errore 824, vedere MSSQLSERVER_824.

Al momento o segnalando questo errore, la cache di lettura contiene una versione precedente della pagina o i dati non sono stati scritti correttamente nel disco fisico. In entrambi i casi (scrittura persa o lettura non aggiornata), SQL Server segnala un problema esterno con il sistema operativo, il driver o i livelli hardware.

Se si verifica l'errore 3448 quando si tenta di eseguire il rollback di una transazione con errore 605 o 823, l'istanza di SQL Server chiude automaticamente il database e tenta di aprirla e ripristinarla. La prima pagina in cui si verifica l'errore 605 o 823 è considerata una pagina non valida e l'ID pagina viene mantenuto dal computer che esegue SQL Server. Durante il ripristino (prima della fase di rollforward) quando viene letto l'ID pagina non valido, i dettagli principali relativi all'intestazione della pagina vengono registrati nel log degli errori di SQL Server. Questa azione è importante perché consente di distinguere tra scenari di lettura persi e lettura non aggiornati.

Comportamento osservato con letture non aggiornati e scritture perse

È possibile che vengano visualizzati i due comportamenti comuni seguenti in scenari di lettura non aggiornati:

Se i file di database vengono chiusi e quindi aperti, i dati scritti e corretti vengono restituiti durante il ripristino.
Quando si rilascia un checkpoint ed si esegue l'istruzione DBCC DROPCLEANBUFFERS (per rimuovere tutte le pagine di database dalla memoria) e quindi si esegue l'istruzione DBCC CHECKDB nel database, vengono restituiti i dati scritti più di recente.

I comportamenti indicati nel paragrafo precedente indicano un problema di memorizzazione nella cache di lettura e vengono spesso risolti disabilitando la cache di lettura. Le azioni descritte nel paragrafo precedente forzano in genere un invalidamento della cache e le letture riuscite che si verificano mostrano che il supporto fisico viene aggiornato correttamente. Il comportamento di scrittura perso si verifica quando la pagina che viene rilette è ancora la versione precedente dei dati, anche dopo uno scaricamento forzato dei meccanismi di memorizzazione nella cache.

In alcuni casi, il problema potrebbe non essere specifico di una cache hardware. Potrebbe trattarsi di un problema con un driver di filtro. In questi casi, esaminare il software, incluse le utilità di backup e il software antivirus, e quindi verificare se si verificano problemi con il driver di filtro.

Descrizione di vari scenari di lettura non aggiornati e scrittura persi

Microsoft ha anche notato condizioni che non soddisfano i criteri per l'errore 605 o 823, ma sono causate dalla stessa attività di lettura o di scrittura non aggiornata. In alcuni casi, una pagina sembra essere aggiornata due volte, ma con lo stesso valore LSN. Questo comportamento può verificarsi se l'ID oggetto e l'ID pagina sono corretti (pagina già allocata all'oggetto) e viene apportata una modifica alla pagina e scaricata sul disco. Il recupero della pagina successiva restituisce un'immagine precedente e quindi viene apportata una seconda modifica. Il log delle transazioni di SQL Server indica che la pagina è stata aggiornata due volte con lo stesso valore LSN. Questa azione diventa un problema quando si tenta di ripristinare una sequenza di log delle transazioni o con problemi di coerenza dei dati, ad esempio errori di chiave esterna o voci di dati mancanti. Il messaggio di errore seguente illustra un esempio di questa condizione:

Errore: 3456, gravità: 21, stato: 1 Non è stato possibile ripetere il record del log (276666:1664:19), per l'ID transazione (0:825853240), nella pagina (1:1787100), database 'authors' (7). Pagina: LSN = (276658:4501:9), tipo = 1. Log: OpCode = 4, context 2, PrevPageLSN: (275565:3959:31)..

Alcuni scenari sono descritti in modo più dettagliato negli elenchi seguenti:

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

Gli operatori di SQL Server sort eseguono attività di I/O, in genere nel tempdb database. Queste operazioni di I/O sono simili alle operazioni di I/O del buffer; Tuttavia, sono già stati progettati per usare la logica di ripetizione dei tentativi di lettura per provare a risolvere problemi simili. La diagnostica aggiuntiva illustrata in questo articolo non si applica a queste operazioni di I/O.

Microsoft ha notato che la causa radice degli errori di lettura dell'ordinamento seguenti è in genere una lettura non aggiornata o una scrittura persa:

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)

Poiché una lettura non aggiornata o una scrittura persa comporta l'archiviazione dei dati che non è prevista, può verificarsi un'ampia gamma di comportamenti. Potrebbero sembrare dati mancanti, ma alcuni degli effetti più comuni dei dati mancanti vengono visualizzati come danneggiamenti dell'indice, ad esempio l'errore 644 o 625:

Errore 644 Livello di gravità 21 Testo messaggio Impossibile trovare la voce di indice per RID '%.*hs' nella pagina di indice %S_PGID, ID di indice %d, database '%.*ls'.

Errore 625 Livello di gravità 21 Testo messaggio Impossibile recuperare riga dalla pagina %S_PGID da RID perché slotid (%d) non è valido.

Alcuni clienti hanno segnalato righe mancanti dopo aver eseguito attività di conteggio delle righe. Questo problema si verifica a causa di una scrittura persa. Forse, la pagina doveva essere collegata alla catena di pagine dell'indice cluster. Se la scrittura è stata persa fisicamente, i dati andranno persi.

Importante

Se si verifica uno dei comportamenti o se si è sospetti di problemi simili insieme alla disabilitazione dei meccanismi di memorizzazione nella cache, Microsoft consiglia vivamente di ottenere l'aggiornamento più recente per SQL Server. Microsoft incoraggia inoltre vivamente l'utente a eseguire una revisione rigorosa del sistema operativo e delle configurazioni associate.

Si noti che Microsoft ha confermato che in caso di carichi di I/O rari e pesanti, alcune piattaforme hardware possono restituire una lettura non aggiornata. Se la diagnostica estesa indica una possibile condizione di scrittura non aggiornata o persa, contattare il fornitore dell'hardware per il completamento immediato e il test con l'utilità SQLIOSim .

SQL Server richiede che i sistemi supportino il recapito garantito ai supporti stabili, come descritto nei requisiti del programma di affidabilità di I/O di SQL Server. Per altre informazioni sui requisiti di input e output per il motore di database di SQL Server, vedere motore di database di Microsoft SQL Server Requisiti di input/output.

Condividi tramite