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

Articolo
06/20/2023

Questo articolo illustra come SQL Server Diagnostica consente di rilevare i problemi di input o output non segnalati che si verificano a causa di letture non aggiornate o scritture perse.

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

Sintomi

Se problemi di sistema operativo, driver o hardware causano condizioni di scrittura o lettura non aggiornate nel percorso di I/O, è possibile che vengano visualizzati messaggi di errore correlati all'integrità dei dati, ad esempio gli 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 introdotto nuove funzionalità di diagnostica di I/O a partire da SQL Server 2000 Service Pack 4 e da allora queste funzionalità di diagnostica fanno parte del prodotto. Queste funzionalità sono progettate per rilevare problemi esterni correlati a I/O e per risolvere i messaggi di errore descritti nella sezione Sintomi .

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

Scrittura persa: chiamata riuscita all'API WriteFile, ma il sistema operativo, un driver o il controller di memorizzazione nella cache non scarica correttamente i dati nel supporto fisico, anche se SQL Server viene informato che la scrittura ha avuto esito positivo.
Lettura non aggiornata: 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 e corretta 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 cache di lettura. 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 questa diagnostica può essere abilitata 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 abilitarli 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 eseguite dal computer che esegue SQL Server, senza includere I/O di ordinamento e 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 SQL Server. La maggior parte SQL Server operazioni di scrittura si verificano come checkpoint o come scritture lazy (una scrittura differita è un'attività in background che usa I/O asincrono). 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 dalle chiamate api ReadFile che SQL Server. Viene invece visualizzato un errore di I/O logico che ha generato quando il LSN è stato esaminato e il valore previsto non era corretto:

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

SQL Server 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>. Altri messaggi nel registro errori SQL Server o nel registro eventi di sistema possono fornire maggiori dettagli. Si tratta di una grave condizione di errore che minaccia l'integrità del database e deve essere corretta immediatamente. Completare una verifica di coerenza completa del database (DBCC CHECKDB). Questo errore può essere causato da molti fattori. Per altre informazioni, vedere SQL Server libri online.

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 oppure 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 aprirlo e ripristinarlo. La prima pagina che presenta 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 valida, i dettagli principali sull'intestazione di pagina vengono registrati nel log degli errori SQL Server. Questa azione è importante perché consente di distinguere tra scenari di scrittura persa e lettura non aggiornata.

Comportamento osservato con letture non aggiornate 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, durante il ripristino vengono restituiti i dati scritti corretti e più di recente.
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 in lettura e vengono spesso risolti disabilitando la cache di lettura. Le azioni descritte nel paragrafo precedente in genere forzano l'invalidamento della cache e le letture completate indicano che il supporto fisico è aggiornato correttamente. Il comportamento di scrittura perso si verifica quando la pagina di cui viene eseguita la lettura è 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 aggiornata e scrittura persa

Microsoft ha anche notato condizioni che non soddisfano i criteri per l'errore 605 o 823, ma sono causate dalla stessa attività di lettura non aggiornata o di scrittura persa. 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 SQL Server mostra 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 Impossibile ripetere il record di log (276666:1664:19), per ID transazione (0:825853240), a pagina (1:1787100), database 'authors' (7). Pagina: LSN = (276658:4501:9), type = 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

sort SQL Server operatori 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 tentare di 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 di 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 un'archiviazione dei dati non prevista, è possibile che si verifichi un'ampia gamma di comportamenti. Può apparire come 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 indice %d, database '%.*ls'.

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

Alcuni clienti hanno segnalato righe mancanti dopo l'esecuzione delle attività di conteggio delle righe. Questo problema si verifica a causa di una scrittura persa. È possibile che la pagina sia collegata alla catena di pagine dell'indice cluster. Se la scrittura è stata fisicamente persa, anche i dati vengono persi.

Importante

Se si verifica uno dei comportamenti o 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 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 lettura non aggiornata o di scrittura persa, contattare il fornitore dell'hardware per il completamento immediato e il test con l'utilità SQLIOSim .

SQL Server richiede sistemi che supportano la distribuzione garantita a supporti stabili, come descritto nei requisiti del programma di affidabilità I/O SQL Server. Per altre informazioni sui requisiti di input e output per il motore di database SQL Server, vedere requisiti di input/output motore di database di Microsoft SQL Server.