Descrizione degli algoritmi di registrazione e archiviazione dei dati che estendono l'affidabilità dei dati in SQL Server

Versione originale del prodotto: SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005
Numero KB originale: 230785

Riepilogo

Questo articolo illustra in che modo gli algoritmi di registrazione e dati di Microsoft SQL Server estendono l'affidabilità e l'integrità dei dati.

Per altre informazioni sui concetti sottostanti dei motori e su Algorithm for Recovery and Isolation Exploiting Semantics (ARIES), vedere il documento ACM Transactions on Database Systems (in Volume 17, Number 1, March 1992):

Collegamento esterno: ARIES: metodo di recupero delle transazioni che supporta il blocco con granularità fine e i rollback parziali tramite la registrazione write-ahead

Il documento illustra le tecniche di SQL Server per estendere l'affidabilità e l'integrità dei dati in relazione agli errori.

È consigliabile leggere gli articoli seguenti nella Microsoft Knowledge Base per ulteriori informazioni sulla memorizzazione nella cache e sulle discussioni sulla modalità di errore alternativa:

• Descrizione della memorizzazione nella cache dei controller disco in SQL Server

• Informazioni sull'uso delle cache delle unità disco con SQL Server che ogni amministratore del database deve conoscere

Termini usati in questo articolo

Prima di iniziare la discussione approfondita, alcuni dei termini usati in questo articolo sono definiti nella tabella seguente.

Protocollo write-ahead logging (WAL)

Il termine protocollo è un modo eccellente per descrivere il WAL. Si tratta di un set specifico e definito di passaggi di implementazione necessari per assicurarsi che i dati vengano archiviati e scambiati correttamente e possano essere ripristinati in uno stato noto in caso di errore. Proprio come una rete contiene un protocollo definito per lo scambio di dati in modo coerente e protetto, allo stesso modo WAL descrive il protocollo per proteggere i dati.

Il documento ARIES definisce il wal come segue:

Il protocollo WAL afferma che i record di log che rappresentano le modifiche ad alcuni dati devono essere già in una risorsa di archiviazione stabile prima che i dati modificati possano sostituire la versione precedente dei dati nell'archiviazione non volatile. Ciò significa che il sistema non è autorizzato a scrivere una pagina aggiornata nella versione di archiviazione non volatile della pagina fino a quando almeno le parti di annullamento dei record di log, che descrivono gli aggiornamenti alla pagina sono state scritte nella risorsa di archiviazione stabile.

Per altre informazioni sulla registrazione write-ahead, vedere l'argomento Write-Ahead Transaction Log nella documentazione online di SQL Server.

SQL Server e WAL

SQL Server usa il protocollo WAL. Per assicurarsi che venga eseguito correttamente il commit di una transazione, tutti i record di log associati alla transazione devono essere protetti in una risorsa di archiviazione stabile.

Per chiarire questa situazione, considerare l'esempio specifico seguente.

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

Suddividere quindi l'attività in passaggi di registrazione semplicistici, come descritto nella tabella seguente.

Non essere confuso con i termini "blocco" e "registrazione". Anche se importante, il blocco e la registrazione sono problemi separati quando si gestisce il wal. Nell'esempio precedente SQL Server contiene in genere il latch nella pagina 150 per il tempo necessario per eseguire le modifiche di inserimento fisico nella pagina, non per l'intero periodo di tempo della transazione. Il tipo di blocco appropriato viene stabilito per proteggere la riga, l'intervallo, la pagina o la tabella in base alle esigenze. Per altre informazioni sui tipi di blocco, vedere le sezioni di blocco online di SQL Server.

Esaminando l'esempio in modo più dettagliato, è possibile chiedere cosa accade quando vengono eseguiti i processi LazyWriter o CheckPoint. SQL Server rilascia tutti gli scaricamenti appropriati per l'archiviazione stabile per i record di log transazionali associati alla pagina dirty e bloccata. In questo modo, la pagina dei dati del protocollo WAL non può mai essere scritta in una risorsa di archiviazione stabile fino a quando i record di log transazionali associati non sono stati scaricati.

SQL Server e archiviazione stabile

SQL Server migliora le operazioni delle pagine di log e dati includendo la conoscenza delle dimensioni del settore del disco (in genere 4.096 byte o 512 byte).

Per mantenere le proprietà ACID di una transazione, SQL Server deve tenere conto dei punti di errore. Durante un errore, molte specifiche dell'unità disco garantiscono solo un numero limitato di operazioni di scrittura del settore. La maggior parte delle specifiche garantisce il completamento di un singolo settore di scrittura quando si verifica un errore.

SQL Server usa pagine di dati da 8 KB e il log (se scaricato) su più dimensioni del settore. La maggior parte delle unità disco usa 512 byte come dimensione del settore predefinita. Se si verifica un errore, SQL Server può tenere conto delle operazioni di scrittura più grandi di un settore usando tecniche di parità dei log e scrittura strappate.

Rilevamento di pagine strappate

Questa opzione consente a SQL Server di rilevare operazioni di I/O incomplete causate da guasti di alimentazione o altre interruzioni del sistema. Se true, viene eseguito un capovolgimento di bit per ogni settore a 512 byte in una pagina di database di 8 kilobyte (KB) ogni volta che la pagina viene scritta su disco. Se un bit è nello stato errato quando la pagina viene letta in un secondo momento da SQL Server, la pagina è stata scritta in modo non corretto; viene rilevata una pagina strappata. Durante il ripristino vengono rilevate pagine non elaborate perché è probabile che qualsiasi pagina scritta in modo non corretto venga letta dal ripristino.

Sebbene le pagine del database di SQL Server siano di 8 KB, i dischi eseguono operazioni di I/O usando un settore a 512 byte. Pertanto, 16 settori vengono scritti per pagina di database. Una pagina interrotta può verificarsi se il sistema ha esito negativo (ad esempio, a causa di un guasto di alimentazione) tra il tempo in cui il sistema operativo scrive il primo settore a 512 byte su disco e il completamento dell'operazione di I/O da 8 KB. Se il primo settore di una pagina del database viene scritto correttamente prima dell'errore, la pagina del database sul disco verrà visualizzata come aggiornata, anche se potrebbe non essere riuscita.

Usando le cache del controller disco con supporto della batteria, è possibile assicurarsi che i dati vengano scritti correttamente su disco o non scritti affatto. In questa situazione, non impostare il rilevamento di pagine strappate su "true" perché non è necessario.

Parità log

Il controllo della parità dei log è simile al rilevamento delle pagine non troncato. Ogni settore a 512 byte contiene bit di parità. Questi bit di parità vengono sempre scritti con il record di log e valutati quando viene recuperato il record di log. Forzando le scritture di log su un limite di 512 byte, SQL Server può assicurarsi che le operazioni di commit vengano scritte nei settori del disco fisico.

Impatto sulle prestazioni

Tutte le versioni di SQL Server aprono i file di log e di dati usando la funzione CreateFile Win32. Il membro dwFlagsAndAttributes include l'opzione FILE_FLAG_WRITE_THROUGH quando vengono aperti da SQL Server.

FILE_FLAG_WRITE_THROUGH indica al sistema di scrivere tramite qualsiasi cache intermedia e passare direttamente al disco. Il sistema può comunque memorizzare nella cache le operazioni di scrittura, ma non può cancellarle in modo differito.

L'opzione FILE_FLAG_WRITE_THROUGH assicura che quando un'operazione di scrittura restituisce un completamento corretto, i dati vengono archiviati correttamente nell'archiviazione stabile. In questo modo si allinea al protocollo WAL che garantisce i dati.

Molte unità disco (SCSI e IDE) contengono cache di onboarding di 512 KB, 1 MB o superiori. Tuttavia, le cache dell'unità si basano in genere su un capacitàtore e non su una soluzione supportata dalla batteria. Questi meccanismi di memorizzazione nella cache non possono garantire scritture in un ciclo di alimentazione o in un punto di errore simile. Garantiscono solo il completamento delle operazioni di scrittura del settore. Questo è in particolare il motivo per cui il rilevamento della parità di scrittura e log non eseguito è stato integrato in SQL Server 7.0 e versioni successive. Man mano che le unità continuano a crescere di dimensioni, le cache diventano più grandi e possono esporre grandi quantità di dati durante un errore.

Molti fornitori di hardware forniscono soluzioni di controller disco con supporto batteria. Queste cache del controller possono mantenere i dati nella cache per diversi giorni e anche consentire l'inserimento dell'hardware di memorizzazione nella cache in un secondo computer. Quando l'alimentazione viene ripristinata correttamente, i dati non scritti vengono scaricati prima che venga consentito l'accesso ai dati aggiuntivi. Molti di essi consentono di stabilire una percentuale di lettura e cache di scrittura per ottenere prestazioni ottimali. Alcuni contengono aree di archiviazione di memoria di grandi dimensioni. Infatti, per un segmento specifico del mercato, alcuni fornitori di hardware forniscono sistemi di controller di memorizzazione nella cache del disco a batteria di fascia alta con 6 GB di cache. Questi possono migliorare significativamente le prestazioni del database.

Le implementazioni avanzate di memorizzazione nella cache gestiranno la FILE_FLAG_WRITE_THROUGH richiesta non disabilitando la cache del controller perché possono fornire funzionalità di riscrittura vere in caso di reimpostazione del sistema, guasto dell'alimentazione o altro punto di guasto.

I trasferimenti di I/O senza l'uso di una cache possono essere più lunghi a causa del tempo meccanico necessario per spostare le teste dell'unità, le frequenze di rotazione e altri fattori di limitazione.

Ordinamento del settore

Una tecnica comune usata per aumentare le prestazioni di I/O è l'ordinamento del settore. Per evitare lo spostamento della testa meccanica, le richieste di lettura/scrittura vengono ordinate, consentendo un movimento più coerente della testa per recuperare o archiviare i dati.

La cache può contenere più richieste di scrittura di log e dati contemporaneamente. Il protocollo WAL e l'implementazione di SQL Server del protocollo WAL richiedono lo scaricamento delle scritture di log in una risorsa di archiviazione stabile prima che sia possibile eseguire la scrittura della pagina. Tuttavia, l'uso della cache potrebbe restituire l'esito positivo di una richiesta di scrittura del log senza che i dati vengano scritti nell'unità effettiva, ovvero scritti in una risorsa di archiviazione stabile. Ciò può causare l'emissione della richiesta di scrittura della pagina dei dati in SQL Server.

Con il coinvolgimento della cache di scrittura, i dati vengono comunque considerati nell'archiviazione volatile. Tuttavia, dalla chiamata WriteFile dell'API Win32, esattamente come SQL Server vede l'attività, è stato ottenuto un codice restituito riuscito. SQL Server o qualsiasi processo che usa la chiamata API WriteFile può determinare solo che i dati hanno ottenuto correttamente l'archiviazione stabile.

Ai fini della discussione, si supponga che tutti i settori della pagina dei dati vengano ordinati per scrivere prima dei settori dei record di log corrispondenti. Ciò viola immediatamente il protocollo WAL. La cache sta scrivendo una pagina di dati prima dei record di log. A meno che la cache non sia completamente supportata dalla batteria, un errore può causare risultati irreversibili.

Quando si valutano i fattori di prestazioni ottimali per un server di database, esistono molti fattori da considerare. L'aspetto più importante è "Il mio sistema consente funzionalità valide FILE_FLAG_WRITE_THROUGH ?"

Il test ha dimostrato che molte configurazioni di unità disco possono contenere la memorizzazione nella cache di scrittura senza il backup della batteria appropriato. Le unità SCSI, IDE ed EIDE sfruttano appieno le cache di scrittura. Per altre informazioni sul funzionamento delle unità SSD con SQL Server, vedere l'articolo di blog sui tecnici di SQL Server CSS seguenti:

SQL Server e UNITÀ SSD - Note di apprendimento di RDORR - Parte 1

In molte configurazioni, l'unico modo per disabilitare correttamente la memorizzazione nella cache di scrittura di un'unità IDE o EIDE consiste nell'usare un'utilità produttore specifica o usando i jumper che si trovano sull'unità stessa. Per assicurarsi che la cache di scrittura sia disabilitata per l'unità stessa, contattare il produttore dell'unità.

Le unità SCSI hanno anche cache di scrittura. Tuttavia, queste cache possono essere comunemente disabilitate dal sistema operativo. In caso di domande, contattare il produttore dell'unità per individuare le utilità appropriate.

Stacking della cache di scrittura

Lo stack di cache di scrittura è simile all'ordinamento del settore. La definizione seguente è stata ricavata direttamente dal sito Web del produttore di unità IDE leader:

In genere, questa modalità è attiva. La modalità cache di scrittura accetta i dati di scrittura dell'host nel buffer fino al completamento del buffer o del trasferimento host.

Un'attività di scrittura su disco inizia a archiviare i dati dell'host su disco. I comandi di scrittura host continuano a essere accettati e i dati trasferiti nel buffer fino a quando lo stack di comandi di scrittura non è pieno o il buffer di dati è pieno. L'unità può riordinare i comandi di scrittura per ottimizzare la velocità effettiva dell'unità.

Riallocazione automatica scrittura (AWR)

Un'altra tecnica comune usata per proteggere i dati consiste nel rilevare i settori danneggiati durante la manipolazione dei dati. La spiegazione seguente deriva dal sito Web del produttore di unità IDE leader:

Questa funzionalità fa parte della cache di scrittura e riduce il rischio di perdita di dati durante le operazioni di scrittura posticipate. Se si verifica un errore del disco durante il processo di scrittura del disco, l'attività del disco si arresta e il settore sospetto viene riallocato a un pool di settori alternativi che si trovano alla fine dell'unità. Dopo la riallocazione, l'attività di scrittura del disco continua fino al completamento.

Questa può essere una funzionalità potente se viene fornito il backup della batteria per la cache. In questo modo viene fornita una modifica appropriata al riavvio. È preferibile rilevare gli errori del disco, ma la sicurezza dei dati del protocollo WAL richiederebbe di nuovo questa operazione in tempo reale e non in modo posticipato. All'interno dei parametri WAL, la tecnica AWR non può tenere conto di una situazione in cui una scrittura del log ha esito negativo a causa di un errore di settore, ma l'unità è piena. Il motore di database deve immediatamente conoscere l'errore in modo che la transazione possa essere interrotta correttamente, l'amministratore può essere avvisato e i passaggi corretti eseguiti per proteggere i dati e correggere la situazione di errore del supporto.

Sicurezza dei dati

Esistono diverse precauzioni che un amministratore di database deve adottare per garantire la sicurezza dei dati.

• È sempre consigliabile assicurarsi che la strategia di backup sia sufficiente per il ripristino da un errore irreversibile. L'archiviazione fuori sede e altre precauzioni sono appropriate.
• Testare l'operazione di ripristino del database in un database secondario o di test su base frequente.
• Assicurarsi che tutti i dispositivi di memorizzazione nella cache possano gestire tutte le situazioni di errore (interruzione dell'alimentazione, settori danneggiati, unità non dannose, interruzioni del sistema, blocchi, picchi di alimentazione e così via).
• Assicurarsi che il dispositivo di memorizzazione nella cache:
  • Ha integrato il backup della batteria
  • Può eseguire nuovamente le scritture in power-up
  • Può essere completamente disabilitato se necessario
  • Gestisce il mapping del settore non valido in tempo reale
• Abilitare il rilevamento delle pagine non troncato. Questo ha un effetto minimo sulle prestazioni.
• Configurare le unità RAID che consentono lo scambio frequente di un'unità disco non valida, se possibile.
• Usare i controller di memorizzazione nella cache più recenti che consentono di aggiungere più spazio su disco senza riavviare il sistema operativo. Questa può essere una soluzione ideale.

Test delle unità

Per proteggere completamente i dati, è necessario assicurarsi che tutta la memorizzazione nella cache dei dati sia gestita correttamente. In molte situazioni, è necessario disabilitare la memorizzazione nella cache di scrittura dell'unità disco.

Microsoft ha eseguito test su diverse unità SCSI e IDE usando l'utilità SQLIOSim . Questa utilità simula un'attività in lettura/scrittura asincrona pesante in un dispositivo dati simulato e in un dispositivo di log. Le statistiche sulle prestazioni dei test mostrano le operazioni di scrittura medie al secondo tra 50 e 70 per un'unità con memorizzazione nella cache di scrittura disabilitata e un intervallo RPM compreso tra 5.200 e 7.200.

Per altre informazioni sull'utilità, vedere l'articolo SQLIOSim seguente nella Microsoft Knowledge Base:

Come usare l'utilità SQLIOSim per simulare l'attività di SQL Server in un sottosistema del disco

Molti produttori di computer ordinano le unità con la cache di scrittura disabilitata. Tuttavia, il test mostra che questo potrebbe non essere sempre il caso. Pertanto, testare sempre completamente.

Dispositivi dati

In tutte le situazioni, ma non registrate, SQL Server richiederà lo scaricamento solo dei record di log. Quando si eseguono operazioni non registrate, anche le pagine di dati devono essere scaricate in una risorsa di archiviazione stabile; non sono presenti singoli record di log per rigenerare le azioni in caso di errore.

Le pagine di dati possono rimanere nella cache fino a quando il processo LazyWriter o CheckPoint li scarica nell'archiviazione stabile. L'uso del protocollo WAL per assicurarsi che i record di log siano archiviati correttamente garantisce che il ripristino possa ripristinare una pagina di dati in uno stato noto.

Ciò non significa che è consigliabile inserire file di dati in un'unità memorizzata nella cache. Quando SQL Server scarica le pagine di dati in una risorsa di archiviazione stabile, i record di log possono essere troncati dal log delle transazioni. Se le pagine di dati vengono archiviate nella cache volatile, è possibile troncare i record di log che verranno usati per ripristinare una pagina in caso di errore. Assicurarsi che i dati e i dispositivi di log siano in grado di supportare correttamente l'archiviazione stabile.

Miglioramento delle prestazioni

La prima domanda che potrebbe verificarsi è: "Ho un'unità IDE che stava memorizzando nella cache. Ma quando l'ho disabilitata, le mie prestazioni sono diventate meno del previsto. Perché?"

Molte delle unità IDE testate da Microsoft vengono eseguite a 5.200 RPM e le unità SCSI a 7.200 RPM. Quando si disabilita la memorizzazione nella cache di scrittura dell'unità IDE, le prestazioni meccaniche possono diventare un fattore.

Per risolvere la differenza di prestazioni, il metodo da seguire è chiaro: "Risolvere la frequenza delle transazioni".

Molti sistemi OLTP (Online Transaction Processing) richiedono una frequenza di transazioni elevata. Per questi sistemi, è consigliabile usare un controller di memorizzazione nella cache in grado di supportare una cache di scrittura e fornire il miglioramento delle prestazioni desiderato garantendo comunque l'integrità dei dati.

Per osservare modifiche significative delle prestazioni che si verificano in SQL Server in un'unità di memorizzazione nella cache, la frequenza delle transazioni è stata aumentata usando transazioni di piccole dimensioni.

Il test mostra che l'attività di scrittura elevata dei buffer inferiori a 512 KB o superiore a 2 MB può causare prestazioni lente.

Si consideri l'esempio seguente:

CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')

Di seguito sono riportati i risultati dei test di esempio per SQL Server:

SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)

IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds

Il processo di wrapping dell'intera serie di INSERT operazioni in una singola transazione viene eseguito in circa quattro secondi in tutte le configurazioni. Ciò è dovuto al numero di scaricamenti del log necessari. Se non si crea una singola transazione, ogni INSERT transazione viene elaborata come transazione separata. Pertanto, tutti i record di log per la transazione devono essere scaricati. Ogni scaricamento è di 512 byte di dimensioni. Ciò richiede un intervento significativo del drive meccanico.

Quando si usa una singola transazione, è possibile aggregare i record di log per la transazione e un'unica scrittura più grande può essere usata per scaricare i record di log raccolti. Questo riduce significativamente l'intervento meccanico.

Esaminare le istruzioni associate allo scaricamento del log per determinare se il valore Byte log scaricati/sec può essere ridotto. Nell'esempio precedente è stata usata una singola transazione. Tuttavia, in molti scenari, questo può causare un comportamento di blocco indesiderato. Esaminare la progettazione della transazione. È possibile usare codice simile al codice seguente per eseguire batch per ridurre l'attività di scaricamento frequente e piccola del log:

BEGIN TRAN
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
    BEGIN
        INSERT INTO tblTest VALUES ('Test')
  
        if(0 = cast(@@IDENTITY as int) % 10)
        BEGIN
            PRINT 'Commit tran batch'
            COMMIT TRAN
            BEGIN TRAN
        END
    END
GO

COMMIT TRAN
GO

SQL Server richiede che i sistemi supportino la distribuzione garantita a supporti stabili, come descritto nel documento di download dei requisiti di verifica dell'affidabilità di SQL Server I/O. 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.