Checkpoint di database (SQL Server)Database Checkpoints (SQL Server)

Un checkpoint crea un punto valido noto da cui Motore di database di SQL ServerSQL Server Database Engine può iniziare ad applicare le modifiche contenute nel log durante il recupero successivo a un arresto anomalo del sistema.A checkpoint creates a known good point from which the Motore di database di SQL ServerSQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

Panoramica Overview

Per motivi legati alle prestazioni, tramite il Motore di databaseDatabase Engine vengono apportate modifiche alle pagine del database in memoria, nella cache buffer, ma queste pagine non vengono scritte su disco dopo ogni modifica.For performance reasons, the Motore di databaseDatabase Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. Il Motore di databaseDatabase Engine pubblica periodicamente un checkpoint su ogni database.Rather, the Motore di databaseDatabase Engine periodically issues a checkpoint on each 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.A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.

Il Motore di databaseDatabase Engine supporta molti tipi di checkpoint: automatici, indiretti, manuali e interni.The Motore di databaseDatabase Engine supports several types of checkpoints: automatic, indirect, manual, and internal. Nella tabella seguente vengono riepilogati i tipi di checkpointThe following table summarizes the types of checkpoints:

NomeName Transact-SQLTransact-SQL Interfaccia Interface DescrizioneDescription
AutomaticAutomatic EXEC sp_configure 'intervallo di recupero','secondi'EXEC sp_configure 'recovery interval','seconds' Emesso automaticamente in background per rispettare il limite di tempo superiore suggerito dall'opzione di configurazione del server intervallo di recupero .Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. I checkpoint automatici vengono eseguiti fino al completamento.Automatic checkpoints run to completion. I checkpoint automatici sono limitati in base al numero di scritture in sospeso e al fatto che il Motore di databaseDatabase Engine rilevi o meno un aumento della latenza di scrittura superiore ai 50 millisecondi.Automatic checkpoints are throttled based on the number of outstanding writes and whether the Motore di databaseDatabase Engine detects an increase in write latency above 50 milliseconds.

Per altre informazioni, vedere Configurare l'opzione di configurazione del server intervallo di recupero.For more information, see Configure the recovery interval Server Configuration Option.
IndirettoIndirect ALTER DATABASE …ALTER DATABASE … SET TARGET_RECOVERY_TIME =target_recovery_time { SECONDI | MINUTI }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.Issued in the background to meet a user-specified target recovery time for a given database. A partire da SQL Server 2016SQL Server 2016, il valore predefinito è 1 minuto.Beginning with SQL Server 2016SQL Server 2016, the default value is 1 minute. 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.The default is 0 for older versions, which indicates that the database will use automatic checkpoints, whose frequency depends on the recovery interval setting of the server instance.

Per altre informazioni, vedere Modificare il tempo di recupero di riferimento di un database (SQL Server).For more information, see Change the Target Recovery Time of a Database (SQL Server).
ManualManual CHECKPOINT [ checkpoint_duration ]CHECKPOINT [ checkpoint_duration ] Emesso quando si esegue un comando CHECKPOINT Transact-SQLTransact-SQL .Issued when you execute a Transact-SQLTransact-SQL CHECKPOINT command. Il checkpoint manuale si verifica nel database corrente per la connessione.The manual checkpoint occurs in the current database for your connection. Per impostazione predefinita, i checkpoint manuali vengono eseguiti fino al completamento.By default, manual checkpoints run to completion. La limitazione funziona come per i checkpoint automatici.Throttling works the same way as for automatic checkpoints. Facoltativamente, il parametro checkpoint_duration specifica una quantità di tempo richiesta, in secondi, per il completamento del checkpoint.Optionally, the checkpoint_duration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete.

Per altre informazioni, vedere CHECKPOINT (Transact-SQL).For more information, see CHECKPOINT (Transact-SQL).
InternoInternal nessuna.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.Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.
Nota

L'opzione di impostazione avanzata -k SQL ServerSQL Server consente a un 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.The -k SQL ServerSQL Server advanced setup option enables a database administrator to throttle checkpoint I/O behavior based on the throughput of the I/O subsystem for some types of checkpoints. L'opzione di impostazione -k riguarda i checkpoint automatici e i checkpoint interni e manuali senza limitazione.The -k setup option applies to automatic checkpoints and any otherwise unthrottled manual and internal checkpoints.

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.For automatic, manual, and internal checkpoints, only modifications made after the latest checkpoint need to be rolled forward during database recovery. Ne consegue una riduzione del tempo necessario per recuperare un database.This reduces the time required to recover a 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.Long-running, uncommitted transactions increase recovery time for all checkpoint types.

Interazione delle opzioni TARGET_RECOVERY_TIME e 'intervallo di recupero' Interaction of the TARGET_RECOVERY_TIME and 'recovery interval' Options

Nella tabella seguente viene riepilogata l'interazione tra l'impostazione del server sp_configure'intervallo di recupero' e l'impostazione del database ALTER DATABASE …The following table summarizes the interaction between the server-wide sp_configure'recovery interval' setting and the database-specific ALTER DATABASE … impostazione TARGET_RECOVERY_TIME.TARGET_RECOVERY_TIME setting.

target_recovery_timeTARGET_RECOVERY_TIME 'intervallo di recupero''recovery interval' Tipo di checkpoint utilizzatoType of Checkpoint Used
00 00 checkpoint automatici il cui intervallo di recupero di destinazione è pari a 1 minuto.automatic checkpoints whose target recovery interval is 1 minute.
00 >0>0 Checkpoint automatici il cui intervallo di recupero di destinazione è specificato dall'impostazione definita dall'utente dell'opzione sp_configure intervallo di recupero.Automatic checkpoints whose target recovery interval is specified by the user defined setting of the sp_configurerecovery interval option.
>0>0 Non applicabile.Not applicable. Checkpoint indiretti il cui tempo di recupero di destinazione è determinato dall'impostazione TARGET_RECOVERY_TIME, espresso in secondi.Indirect checkpoints whose target recovery time is determined by the TARGET_RECOVERY_TIME setting, expressed in seconds.

Checkpoint automatici Automatic checkpoints

Si verifica un checkpoint automatico ogni volta che il numero di record di log raggiunge il numero elaborabile dal Motore di databaseDatabase Engine nel tempo specificato nell'opzione di configurazione del server intervallo di recupero .An automatic checkpoint occurs each time the number of log records reaches the number the Motore di databaseDatabase Engine estimates it can process during the time specified in the recovery interval server configuration option.

In ogni database senza un tempo di recupero di destinazione definito dall'utente, il Motore di databaseDatabase Engine genera checkpoint automatici.In every database without a user-defined target recovery time, the Motore di databaseDatabase Engine generates automatic checkpoints. 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.The frequency depends on the recovery interval advanced server configuration option, which specifies the maximum time that a given server instance should use to recover a database during a system restart. Il Motore di databaseDatabase Engine valuta il numero massimo di record di log che può elaborare nell'intervallo di recupero.The Motore di databaseDatabase Engine estimates the maximum number of log records it can process within the recovery interval. Quando un database che usa i checkpoint automatici raggiunge il numero massimo specificato di record di log, il Motore di databaseDatabase Engine pubblica un checkpoint sul database.When a database using automatic checkpoints reaches this maximum number of log records, the Motore di databaseDatabase Engine issues an checkpoint on the database.

L'intervallo di tempo tra checkpoint automatici può essere estremamente variabile.The time interval between automatic checkpoints can be highly variable. 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.A database with a substantial transaction workload will have more frequent checkpoints than a database used primarily for read-only operations. In base al modello di recupero con registrazione minima, viene messo in coda anche un checkpoint automatico se il log è pieno al 70%.Under the simple recovery model, an automatic checkpoint is also queued if the log becomes 70 percent full.

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.Under the simple recovery model, unless some factor is delaying log truncation, an automatic checkpoint truncates the unused section of the transaction log. 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.By contrast, under the full and bulk-logged recovery models, once a log backup chain has been established, automatic checkpoints do not cause log truncation. Per altre informazioni, vedere Log delle transazioni (SQL Server).For more information, see The Transaction Log (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.After a system crash, the length of time required to recover a given database depends largely on the amount of random I/O needed to redo pages that were dirty at the time of the crash. Ciò significa che l'impostazione intervallo di recupero non è affidabile.This means that the recovery interval setting is unreliable. Non è in grado di determinare una durata accurata del recupero.It cannot determine an accurate recovery duration. Inoltre, quando è in corso un checkpoint automatico, l'attività di I/O generale per i dati aumenta in modo significativo e imprevedibile.Furthermore, when an automatic checkpoint is in progress, the general I/O activity for data increases significantly and quite unpredictably.

Impatto dell'intervallo di recupero sulle prestazioni di recupero Impact of recovery interval on recovery performance

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.For an online transaction processing (OLTP) system using short transactions, recovery interval is the primary factor determining recovery time. L'opzione intervallo di recupero non influisce tuttavia sul tempo necessario per annullare una transazione con esecuzione prolungata.However, the recovery interval option does not affect the time required to undo a long-running transaction. Il recupero di un database con una transazione con esecuzione prolungata può richiedere molto più tempo rispetto all'opzione intervallo di recupero .Recovery of a database with a long-running transaction can take much longer than the specified in the recovery interval option.

Ad esempio, se prima dell'interruzione dell'istanza del server sono stati eseguiti aggiornamenti con una transazione con esecuzione prolungata che hanno richiesto due ore, l'operazione di recupero durerà molto più a lungo rispetto al periodo di tempo specificato in intervallo di recupero per il recupero della transazione.For example, if a long-running transaction took two hours to perform updates before the server instance became disabled, the actual recovery takes considerably longer than the recovery interval value to recover the long transaction. Per informazioni sull'impatto di una transazione con esecuzione prolungata sul tempo di recupero, vedere Log delle transazioni (SQL Server).For more information about the impact of a long running transaction on recovery time, see The Transaction Log (SQL Server).

In genere, i valori predefiniti forniscono prestazioni di recupero ottimali.Typically, the default values provides optimal recovery performance. Tuttavia, la modifica dell'intervallo di recupero potrebbe migliorare le prestazioni nelle circostanze seguenti:However, changing the recovery interval might improve performance in the following circumstances:

  • Se il recupero richiede regolarmente più di 1 minuto quando non viene eseguito il rollback delle transazioni con esecuzione prolungata.If recovery routinely takes significantly longer than 1 minute when long-running transactions are not being rolled back.

  • Se si nota che checkpoint frequenti riducono le prestazioni su un database.If you notice that frequent checkpoints are impairing performance on a 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.If you decide to increase the recovery interval setting, we recommend increasing it gradually by small increments and evaluating the effect of each incremental increase on recovery performance. Questo approccio è importante perché man mano che l'impostazione intervallo di recupero viene aumentata, il recupero del database richiederà una quantità di tempo equivalente a tale impostazione.This approach is important because as the recovery interval setting increases, database recovery takes that many times longer to complete. Ad esempio, se si imposta un intervallo di recupero pari a 10 minuti, la procedura di recupero richiederà un tempo 10 volte superiore rispetto a quello che richiederebbe se intervallo di recupero fosse impostato su 1 minuto.For example, if you change recovery interval 10 minutes, recovery takes approximately 10 times longer to complete than when recovery interval is set to 1 minute.

Checkpoint indiretti Indirect checkpoints

I checkpoint indiretti, nuovi in SQL Server 2012SQL Server 2012, offrono un'alternativa a livello di database configurabile ai checkpoint automatici.Indirect checkpoints, introduced in SQL Server 2012SQL Server 2012, provide a configurable database-level alternative to automatic checkpoints. 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.In the event of a system crash, indirect checkpoints provide potentially faster, more predictable recovery time than automatic checkpoints. I checkpoint indiretti offrono i vantaggi riportati di seguito:Indirect checkpoints offer the following advantages:

  • Un carico di lavoro transazionale online su un database configurato per i checkpoint indiretti può subire un calo delle prestazioni.An online transactional workload on a database configured for indirect checkpoints can experience performance degradation. 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.Indirect checkpoints ensure that the number of dirty pages are below a certain threshold so the database recovery completes within the target recovery time.

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.The recovery interval configuration option uses the number of transactions to determine the recovery time as opposed to indirect checkpoints which makes use of number of dirty pages. 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.When indirect checkpoints are enabled on a database receiving a large number of DML operations, the background writer can start aggressively flushing dirty buffers to disk to ensure that the time required to perform recovery is within the target recovery time set of the 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.This can cause additional I/O activity on certain systems which can contribute to a performance bottleneck if the disk subsystem is operating above or nearing the I/O threshold.

  • 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.Indirect checkpoints enable you to reliably control database recovery time by factoring in the cost of random I/O during 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.This enables a server instance to stay within an upper-bound on recovery times for a given database (except when a long-running transaction causes excessive UNDO times).

  • I checkpoint indiretti riducono i picchi di I/O correlati ai checkpoint scrivendo continuamente pagine dirty su disco in background.Indirect checkpoints reduce checkpoint-related I/O spiking by continually writing dirty pages to disk in the background.

    Tuttavia, un carico di lavoro transazionale online su un database configurato per i checkpoint indiretti può subire un calo delle prestazioni.However, an online transactional workload on a database configured for indirect checkpoints can experience performance degradation. Questo perché il writer in background utilizzato dai checkpoint indiretti aumenta talvolta il carico di scrittura totale di un'istanza del server.This is because the background writer used by indirect checkpoint sometimes increases the total write load for a server instance.

    Importante

    I checkpoint indiretti rappresentano il comportamento predefinito per i nuovi database creati SQL Server 2016SQL Server 2016.Indirect checkpoint is the default behavior for new databases created in SQL Server 2016SQL Server 2016. I database aggiornati sul posto o ripristinati da una versione precedente di SQL ServerSQL Server useranno il comportamento precedente basato sui checkpoint automatici, a meno che non vengano modificati in modo esplicito per l'uso dei checkpoint indiretti.Databases which were upgraded in place or restored from a previous version of SQL ServerSQL Server will use the previous automatic checkpoint behavior unless explicitly altered to use indirect checkpoint.

Checkpoint interni Internal checkpoints

I checkpoint interni vengono generati da vari componenti server per garantire che le immagini sul disco corrispondano allo stato corrente del log.Internal Checkpoints are generated by various server components to guarantee that disk images match the current state of the log. I checkpoint interni vengono generati in risposta agli eventi seguenti:Internal checkpoint are generated in response to the following events:

  • Vengono aggiunti o rimossi file di database utilizzando l'istruzione ALTER DATABASE.Database files have been added or removed by using ALTER DATABASE.

  • Viene eseguito un backup del database.A database backup is taken.

  • Viene creato uno snapshot del database, in modo esplicito o internamente per CHECK DBCC.A database snapshot is created, whether explicitly or internally for DBCC CHECK.

  • Viene eseguita un'attività che richiede la chiusura di un database.An activity requiring a database shutdown is performed. 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.For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

  • Un'istanza di SQL ServerSQL Server viene arrestata in seguito all'arresto del servizio SQL ServerSQL Server (MSSQLSERVER).An instance of SQL ServerSQL Server is stopped by stopping the SQL ServerSQL Server (MSSQLSERVER) service . Entrambe le azioni causano un checkpoint in ogni database dell'istanza di SQL ServerSQL Server.Either action causes a checkpoint in each database in the instance of SQL ServerSQL Server.

  • Impostazione della modalità offline per un'istanza del cluster di failover di SQL ServerSQL Server .Bringing a SQL ServerSQL Server failover cluster instance (FCI) offline.

Per modificare l'intervallo di recupero su un'istanza del serverTo change the recovery interval on a server instance

Vedere ancheSee also