Nota
L'accesso a questa pagina richiede l'autorizzazione. È possibile provare ad accedere o modificare le directory.
L'accesso a questa pagina richiede l'autorizzazione. È possibile provare a modificare le directory.
Si applica a:
SQL ServerDatabase SQL di Azure
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
Per motivi correlati alle prestazioni, il motore di database esegue modifiche alle pagine del database in memoria, nella cache buffer, ma queste pagine non vengono scritte su disco dopo ogni modifica. Al contrario, il motore di database emette periodicamente un checkpoint su ogni database. Un checkpoint scrive dalla memoria al disco le pagine modificate correnti in memoria (note come dirty pages) e le informazioni del log delle transazioni, e registra anche queste informazioni nel 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 'intervallo di recupero', 'seconds' | Emesso automaticamente in background per rispettare il limite di tempo superiore suggerito dall'opzione di configurazione del server intervallo di recupero . I checkpoint automatici vengono eseguiti fino al termine. I checkpoint automatici sono limitati in base al numero di scritture in sospeso e al fatto che il motore di database rilevi o meno un aumento della latenza di scrittura superiore ai 50 millisecondi. Per altre informazioni, vedere Configurare l'opzione di configurazione del server recovery interval. |
| Indiretto | ALTER DATABASE ... SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES } | Emesso in background per rispettare un tempo di recupero di destinazione specificato dall'utente per un determinato database. A partire da SQL Server 2016 (13.x), il valore predefinito è 1 minuto. Il valore predefinito è 0 per le versioni precedenti, a indicare che il database userà checkpoint automatici la cui frequenza dipende dall'impostazione dell'intervallo di recupero dell'istanza del server. Per altre informazioni, vedere Modificare il tempo di recupero di riferimento di un database (SQL Server). |
| Manualee | CHECKPOINT [ checkpoint_duration ] | Emesso quando si esegue un comando CHECKPOINT Transact-SQL. Il checkpoint manuale avviene nel database corrente della connessione. Per impostazione predefinita, i checkpoint manuali vengono eseguiti fino alla fine. 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 | None | 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. |
L'opzione di impostazione avanzata -k di SQL Server consente all'amministratore del database di limitare il comportamento di I/O del checkpoint in base alla velocità effettiva del sottosistema di I/O per alcuni tipi di checkpoint. L'opzione di configurazione -k si applica ai checkpoint automatici e a qualsiasi checkpoint manuale e interno altrimenti non soggetto a limitazione.
Nel caso di checkpoint automatici, manuali e interni, durante il recupero del database è necessario applicare il rollforward solo alle modifiche apportate dopo il checkpoint più recente. 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 'recovery interval'
La tabella seguente riepiloga l'interazione tra l'impostazione sp_configure 'recovery interval' a livello di server e l'impostazione ALTER DATABASE ... TARGET_RECOVERY_TIME specifica del database.
| tempo di ripristino previsto | 'intervallo di recupero' | Tipo di checkpoint usato |
|---|---|---|
| 0 | 0 | checkpoint automatici il cui intervallo di recupero previsto è pari a 1 minuto. |
| 0 | > 0 | Checkpoint automatici il cui intervallo di recupero previsto è specificato dall'impostazione dell'opzione sp_configure 'recovery interval' definita dall'utente. |
| > 0 | Non applicabile | Checkpoint indiretti il cui tempo di ripristino previsto è determinato dall'impostazione TARGET_RECOVERY_TIME, espresso in secondi. |
Punti di controllo automatici
Un checkpoint automatico si verifica ogni volta che il numero di record di log raggiunge il numero che il motore di database stima di poter elaborare durante l'intervallo di tempo specificato nell'opzione di configurazione server recovery interval. Per altre informazioni, vedere Configurare l'opzione di configurazione del server recovery interval.
In ogni database senza un tempo di ripristino previsto definito dall'utente, il Motore di database genera checkpoint automatici. La frequenza dipende dall'opzione di configurazione del server avanzata intervallo di recupero che specifica il tempo massimo che un'istanza del server deve usare per recuperare un database durante un 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 i checkpoint automatici raggiunge il numero massimo specificato di record di log, il motore di database emette un checkpoint sul 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 usato principalmente per le operazioni in sola lettura. Nel modello di recupero semplice, viene inoltre accodato un checkpoint automatico se il log raggiunge il 70% della capacità.
Nel modello di recupero semplice, a meno che qualche fattore non ritardi 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 intervallo di recupero non è affidabile. Non è in grado di determinare una durata accurata del recupero. Inoltre, quando è in corso un checkpoint automatico, l'attività generale di I/O sui dati aumenta in modo significativo e imprevedibile.
Effetto dell'intervallo di recupero sulle prestazioni di recupero
In un sistema di elaborazione delle transazioni online (OLTP) che usa transazioni brevi, il tempo identificato da intervallo di recupero costituisce il fattore che influisce maggiormente sul tempo di recupero. L'opzione recovery interval non influisce tuttavia sul tempo necessario per annullare una transazione con esecuzione prolungata. Il recupero di un database con una transazione con esecuzione prolungata può richiedere molto più tempo rispetto al valore indicato nell'impostazione Intervallo di recupero.
Ad esempio, se una transazione di lunga durata ha impiegato due ore per eseguire gli aggiornamenti prima che l'istanza del server venisse disabilitata, il recupero effettivo richiederà un tempo considerevolmente superiore al valore di intervallo di recupero per recuperare la transazione di lunga durata. Per informazioni sull'impatto di una transazione con esecuzione prolungata sul tempo di recupero, vedere Log delle transazioni (SQL Server). Per altre informazioni sul processo di recupero, vedere Panoramica del ripristino e del recupero (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 un tempo significativamente superiore a 1 minuto quando non viene eseguito il rollback delle transazioni di lunga durata.
Se si osserva che checkpoint frequenti compromettono le prestazioni di 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é, man mano che aumenta l'impostazione intervallo di recupero, il completamento del ripristino del database richiede un tempo proporzionalmente maggiore. Ad esempio, se si imposta l'intervallo di recupero su 10 minuti, la procedura di recupero richiederà un tempo 10 volte superiore rispetto a quello che richiederebbe se l'intervallo di recupero fosse impostato su 1 minuto.
Checkpoint indiretti
I checkpoint indiretti, introdotti in SQL Server 2012 (11.x), offrono un'alternativa configurabile a livello di database ai checkpoint automatici. Questa impostazione può essere configurata specificando l'opzione di configurazione del database target recovery time. Per altre informazioni, vedere Modificare il tempo di recupero di riferimento di un database (SQL Server). 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:
I checkpoint indiretti assicurano che il numero di pagine dirty sia inferiore a una determinata soglia, in modo che il recupero del database venga completato entro il tempo di recupero di riferimento.
A differenza dei checkpoint indiretti, che usano il numero di pagine dirty, l'opzione di configurazione Intervallo di recupero usa il numero di transazioni per determinare il tempo di recupero. Quando i checkpoint indiretti sono abilitati in un database che riceve un numero elevato di operazioni DML, il processo di scrittura in background può iniziare a scrivere in modo aggressivo su disco i buffer sporchi per garantire che il tempo necessario per eseguire il ripristino rientri nel tempo di recupero target 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 ripristino del database tenendo conto del costo dell'I/O casuale durante la fase di 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 associati ai checkpoint scrivendo continuamente in background le pagine sporche sul disco.
Tuttavia, un carico di lavoro transazionale online su un database configurato per i checkpoint indiretti può 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.
Importante
I checkpoint indiretti rappresentano il comportamento predefinito per i nuovi database creati in SQL Server 2016 (13.x), inclusi i database model e tempdb.
I database aggiornati sul posto o ripristinati da una versione precedente di SQL Server useranno il comportamento precedente basato sui checkpoint automatici, a meno che non vengano modificati in modo esplicito per l'uso dei checkpoint indiretti.
Scalabilità migliorata per il checkpoint indiretto
Prima di SQL Server 2019 (15.x), è possibile che si verifichino errori dello scheduler senza rilascio del controllo quando è presente un database che genera un numero elevato di pagine sporche, ad esempio tempdb. In SQL Server 2019 (15.x) è stata introdotta una scalabilità migliorata per il checkpoint indiretto che consente di evitare questi errori nei database con un carico di lavoro eccessivo di UPDATE/INSERT.
Punti di controllo 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:
I file di database sono stati aggiunti o rimossi tramite ALTER DATABASE.
Viene eseguito un backup del database.
Viene creato uno snapshot del database, in modo esplicito o internamente per DBCC CHECKDB.
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 dall'arresto del servizio SQL Server (MSSQLSERVER). Questa azione causa un checkpoint in ogni database dell'istanza di SQL Server.
Impostazione della modalità offline per un'istanza del cluster di failover di SQL Server.
Passaggi successivi
- Configurare l'opzione di configurazione del server Intervallo di ripristino
- Modificare il tempo di recupero di riferimento di un database (SQL Server)
- CHECKPOINT (Transact-SQL)