Checkpoint di database (SQL Server)

Articolo
10/13/2015

Questo argomento offre una panoramica dei checkpoint di database di SQL Server. Un checkpoint crea un punto valido noto da cui il motore di database di SQL Server può iniziare ad applicare le modifiche contenute nel log durante il recupero successivo a un arresto anomalo del sistema.

Panoramica dei checkpoint

Per motivi di prestazioni, il motore di database esegue modifiche alle pagine del database nella memoria nella cache del buffer e non scrive queste pagine su disco dopo ogni modifica. Al contrario, il motore di database emette periodicamente un checkpoint su ogni database. Un checkpoint scrive le pagine modificate in memoria correnti, note come pagine dirtye le informazioni sul log delle transazioni dalla memoria sul disco e registra le informazioni sul log delle transazioni.

Il motore di database supporta molti tipi di checkpoint: automatico, indiretto, manuale e interno. Nella tabella seguente vengono riepilogati i tipi di checkpoint.

Nome	Interfaccia Transact-SQL	Descrizione
Automatico	EXEC sp_configure *'`recovery interval`'`seconds`,*	Rilasciato automaticamente in background per soddisfare il limite massimo di tempo suggerito dall'opzione di configurazione del `recovery interval` server. I checkpoint automatici vengono eseguiti fino al completamento. I checkpoint automatici vengono limitati in base al numero di scritture in sospeso e se il motore di database rileva un aumento della latenza di scrittura superiore a 20 millisecondi. Per altre informazioni, vedere Configurare l'opzione di configurazione del server recovery interval.
Indiretto	ALTER DATABASE ... SET TARGET_RECOVERY_TIME target_recovery_time= { SECONDI \| MINUTI }	Emesso in background per rispettare un tempo di recupero di destinazione specificato dall'utente per un determinato database. Il tempo di recupero di destinazione predefinito è 0 che comporta l'utilizzo di un approccio euristico dei checkpoint automatici nel database. Se è stato usato ALTER DATABASE per impostare TARGET_RECOVERY_TIME su >0, questo valore viene usato anziché l'intervallo di ripristino specificato per l'istanza del server. Per altre informazioni, vedere Cambiare il tempo di recupero di riferimento di un database (SQL Server).
Manuale	CHECKPOINT [ checkpoint_duration ]	Emesso quando si esegue un comando CHECKPOINT Transact-SQL. Il checkpoint manuale si verifica nel database corrente per la connessione. Per impostazione predefinita, i checkpoint manuali vengono eseguiti fino al completamento. La limitazione funziona come per i checkpoint automatici. Facoltativamente, il parametro checkpoint_duration specifica una quantità di tempo richiesta, in secondi, per il completamento del checkpoint. Per altre informazioni, vedere CHECKPOINT (Transact-SQL).
Interno	No.	Emesso da varie operazioni del server quali backup e creazione dello snapshot del database per garantire che le immagini del disco corrispondano allo stato corrente del log.

Nota

L'opzione -kdi configurazione avanzata SQL Server consente a un amministratore del database di limitare il comportamento di I/O del checkpoint in base alla velocità effettiva del sottosistema I/O per alcuni tipi di checkpoint. L'opzione di impostazione -k riguarda i checkpoint automatici e i checkpoint interni e manuali senza limitazione.

Per i checkpoint automatici, manuali e interni, solo le modifiche apportate dopo l'ultimo checkpoint devono essere sottoposte a rollforward durante il recupero del database. Ne consegue una riduzione del tempo necessario per recuperare un database.

Importante

Le transazioni di cui non è stato eseguito il commit con esecuzione prolungata aumentano il tempo di recupero per tutti i tipi di checkpoint.

Interazione delle opzioni TARGET_RECOVERY_TIME e 'intervallo di recupero'

Nella tabella seguente viene riepilogato l'interazione tra l'impostazione sp_configure a livello di server e l'impostazionerecovery interval ALTER DATABASE specifica del database... TARGET_RECOVERY_TIME impostazione.

target_recovery_time	'intervallo di recupero'	Tipo di checkpoint utilizzato
0	0	checkpoint automatici il cui intervallo di recupero di destinazione è pari a 1 minuto.
0	>0	Checkpoint automatici il cui intervallo di recupero di destinazione è specificato dall'impostazione definita dall'utente dell'opzione sp_configure intervallo di recupero.
>0	Non applicabile.	Checkpoint indiretti il cui tempo di recupero di destinazione è determinato dall'impostazione TARGET_RECOVERY_TIME, espresso in secondi.

Checkpoint automatici

Un checkpoint automatico si verifica ogni volta che il numero di record di log raggiunge il numero stimato dal motore di database che può elaborare durante l'ora specificata nell'opzione di configurazione del recovery interval server. In ogni database senza un tempo di recupero di destinazione definito dall'utente, il motore di database genera checkpoint automatici. La frequenza dei checkpoint automatici dipende dall'opzione di configurazione del server avanzata, che specifica il tempo massimo usato da un'istanza recovery interval del server specificata per ripristinare un database durante il riavvio del sistema. Il motore di database stima il numero massimo di record di log che può elaborare nell'intervallo di recupero. Quando un database che usa checkpoint automatici raggiunge questo numero massimo di record di log, il motore di database genera un checkpoint nel database. L'intervallo di tempo tra checkpoint automatici può essere estremamente variabile. In un database con un considerevole carico di lavoro di transazioni si verificheranno checkpoint più frequenti rispetto a un database utilizzato principalmente per le operazioni in sola lettura.

Inoltre, in base al modello di recupero con registrazione minima, viene messo in coda un checkpoint automatico se il log è pieno al 70 percento.

In base al modello di recupero con registrazione minima, a meno che alcuni fattori non ritardino il troncamento del log, un checkpoint automatico tronca la sezione inutilizzata del log delle transazioni. Al contrario, in base ai modelli di recupero con registrazione minima delle operazioni bulk e con registrazione completa, dopo avere stabilito una catena di backup del log, i checkpoint automatici non causano il troncamento del log. Per altre informazioni, vedere Log delle transazioni (SQL Server).

Dopo un arresto anomalo del sistema, la quantità di tempo necessaria per recuperare un database dipende in larga misura dalla quantità di I/O casuale necessario per ripristinare le pagine dirty al momento dell'arresto anomalo del sistema. Ciò significa che l'impostazione recovery interval non è affidabile. Non è in grado di determinare una durata accurata del recupero. Inoltre, quando è in corso un checkpoint automatico, l'attività di I/O generale per i dati aumenta in modo significativo e imprevedibile.

Impatto dell'intervallo di recupero sulle prestazioni di recupero

Per un sistema OLTP (Online Transaction Processing) usando transazioni brevi, recovery interval è il fattore principale che determina il tempo di ripristino. Tuttavia, l'opzione recovery interval non influisce sul tempo necessario per annullare una transazione a esecuzione prolungata. Il ripristino di un database con una transazione a esecuzione prolungata può richiedere molto più tempo rispetto all'opzione recovery interval specificata. Ad esempio, se una transazione a esecuzione prolungata richiede due ore per eseguire gli aggiornamenti prima che l'istanza del server sia disabilitata, il ripristino effettivo richiede molto più tempo del recovery interval valore per ripristinare la transazione lunga. Per informazioni sull'impatto di una transazione con esecuzione prolungata sul tempo di recupero, vedere Log delle transazioni (SQL Server).

In genere, i valori predefiniti forniscono prestazioni di recupero ottimali. Tuttavia, la modifica dell'intervallo di recupero potrebbe migliorare le prestazioni nelle circostanze seguenti:

Se il recupero richiede regolarmente più di 1 minuto quando non viene eseguito il rollback delle transazioni con esecuzione prolungata.
Se si nota che checkpoint frequenti riducono le prestazioni su un database.

Se si decide di aumentare l'impostazione recovery interval, è consigliabile aumentarla gradualmente di piccoli incrementi e valutare l'effetto di ogni aumento incrementale sulle prestazioni del recupero. Questo approccio è importante perché l'impostazione aumenta, il ripristino del recovery interval database richiede più volte il completamento. Ad esempio, se si modifica recovery interval 10, il ripristino richiede circa 10 volte più tempo da completare rispetto a quando recovery interval è impostato su zero.

Checkpoint indiretti

I checkpoint indiretti, nuovi in SQL Server 2012, offrono un'alternativa a livello di database configurabile ai checkpoint automatici. In caso di un arresto anomalo del sistema, i checkpoint indiretti consentono un tempo di recupero potenzialmente più veloce e più prevedibile rispetto ai checkpoint automatici. I checkpoint indiretti offrono i vantaggi riportati di seguito:

Un carico di lavoro transazionale online su un database configurato per i checkpoint indiretti potrebbe subire un calo delle prestazioni. I checkpoint indiretti assicurano che il numero di pagine dirty è inferiore a una determinata soglia, in modo che il recupero del database viene completato entro il tempo di recupero di riferimento. A differenza dei checkpoint indiretti che usano il numero di pagine dirty, l'opzione di configurazione recovery interval usa il numero di transazioni per determinare il tempo di recupero. Quando i checkpoint indiretti sono abilitati per un database che riceve un numero elevato di operazioni DML, il writer in background può iniziare a scaricare i buffer dirty su disco in modo intensivo per garantire che il tempo necessario per eseguire il recupero non superi il tempo di recupero di riferimento impostato per il database. Questo può causare in determinati sistemi ulteriore attività di I/O che può comportare un collo di bottiglia delle prestazioni se il sottosistema del disco opera al di sopra o in prossimità della soglia di I/O.
I checkpoint indiretti consentono di controllare in modo affidabile il tempo di recupero del database tramite factoring del costo di operazioni di I/O casuali durante REDO. In questo modo si consente a un'istanza del server di rimanere entro il limite superiore per i tempi di recupero per un determinato database, tranne quando una transazione con esecuzione prolungata causa tempi UNDO eccessivi.
I checkpoint indiretti riducono i picchi di I/O correlati ai checkpoint scrivendo continuamente pagine dirty su disco in background.

Tuttavia, un carico di lavoro transazionale online su un database configurato per i checkpoint indiretti potrebbe subire un calo delle prestazioni. Questo perché il writer in background utilizzato dai checkpoint indiretti aumenta talvolta il carico di scrittura totale di un'istanza del server.

Checkpoint interni

I checkpoint interni vengono generati da vari componenti server per garantire che le immagini sul disco corrispondano allo stato corrente del log. I checkpoint interni vengono generati in risposta agli eventi seguenti:

Vengono aggiunti o rimossi file di database utilizzando l'istruzione ALTER DATABASE.
Viene eseguito un backup del database.
Viene creato uno snapshot del database, in modo esplicito o internamente per CHECK DBCC.
Viene eseguita un'attività che richiede la chiusura di un database. Ad esempio, AUTO_CLOSE è impostata su ON e la connessione al database dell'ultimo utente viene chiusa, oppure viene eseguita una modifica a un'opzione di database che richiede un riavvio del database.
Un'istanza di SQL Server viene arrestata arrestando il servizio SQL Server (MSSQLSERVER). Entrambe le azioni causano un checkpoint in ogni database dell'istanza di SQL Server.
Impostazione della modalità offline per un'istanza del cluster di failover di SQL Server.