Checkpoint di database (SQL Server)

  • Articolo

In questo argomento viene fornita una panoramica dei checkpoint del database di SQL Server. Un checkpoint crea un punto valido noto da cui 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.

Contenuto dell'argomento:

  • Panoramica dei checkpoint

  • Attività correlate

  • Contenuto correlato

Panoramica dei checkpoint

Per motivi legati alle prestazioni, tramite il Motore di database vengono apportate modifiche alle pagine del database in memoria, nella cache buffer, ma queste pagine non vengono scritte su disco dopo ogni modifica. Il Motore di database pubblica periodicamente un checkpoint su ogni database. Un checkpoint scrive le pagine modificate in memoria correnti, note come pagine dirty, e 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: automatici, indiretti, manuali e interni. 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 completamento. 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 20 millisecondi.

Per ulteriori 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. Il tempo di recupero di destinazione predefinito è 0 che comporta l'utilizzo di un approccio euristico dei checkpoint automatici nel database. Se si utilizza ALTER DATABASE per impostare TARGET_RECOVERY_TIME su >0, viene utilizzato questo valore e non l'intervallo di recupero specificato per l'istanza del server.

Per ulteriori informazioni, vedere Modificare 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. Il parametro checkpoint_duration specifica una quantità di tempo richiesta, in secondi, per il completamento del checkpoint.

Per ulteriori informazioni, vedere CHECKPOINT (Transact-SQL).

Interno

Nessuna.

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 di impostazione avanzata -k SQL 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. 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.

Nota importanteImportante

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

[Inizio pagina]

Contenuto della sezione

  • Interazione delle opzioni TARGET_RECOVERY_TIME e 'intervallo di recupero'

  • Checkpoint automatici

  • Checkpoint indiretti

  • Checkpoint interno

Interazione delle opzioni TARGET_RECOVERY_TIME e 'intervallo di recupero'

Nella tabella di riepilogo viene riepilogata l'interazione tra l'impostazione del server sp_configure 'intervallo di recupero' e l'impostazione del database ALTER DATABASE … TARGET_RECOVERY_TIME.

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

Si verifica un checkpoint automatico ogni volta che il numero di record di log raggiunge il numero elaborabile dal Motore di database nel tempo specificato nell'opzione di configurazione del server intervallo di recupero. In ogni database senza un tempo di recupero di destinazione definito dall'utente, il Motore di database genera checkpoint automatici. La frequenza di checkpoint automatici dipende dall'opzione di configurazione del server avanzata intervallo di recupero che specifica il tempo massimo che un'istanza del server deve utilizzare per recuperare un database durante un riavvio del sistema. Il Motore di database valuta il numero massimo di record di log che può elaborare nell'intervallo di recupero. Quando un database che utilizza i checkpoint automatici raggiunge il numero massimo specificato di record di log, il Motore di database pubblica 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 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 ulteriori 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à di I/O generale per i dati aumenta in modo significativo e imprevedibile.

[Inizio pagina]

Impatto dell'intervallo di recupero sulle prestazioni di recupero

In un sistema di elaborazione delle transazioni online (OLTP) che utilizza transazioni brevi, il tempo identificato da intervallo di recupero costituisce il fattore che influisce maggiormente sul tempo di recupero. L'opzione intervallo di recupero 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 all'opzione intervallo di recupero. Ad esempio, se prima dell'interruzione dell'istanza del server sono stati eseguiti aggiornamenti tramite 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. 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é man mano che l'impostazione intervallo di recupero viene aumentata, il recupero del database richiederà una quantità di tempo equivalente a tale impostazione. Ad esempio, se si imposta un intervallo di recupero pari a 10, la procedura di recupero richiederà un tempo 10 volte superiore rispetto a quello che richiederebbe se intervallo di recupero fosse impostato su zero.

[Inizio pagina]

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:

  • I checkpoint indiretti possono ridurre il tempo di recupero complessivo del database.

  • 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.

[Inizio pagina]

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.

  • Viene arrestata un'istanza di SQL Server in seguito all'arresto del 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.

[Inizio pagina]

Attività correlate

Per modificare l'intervallo di recupero su un'istanza del server

Per configurare checkpoint indiretti su un database

Per emettere un checkpoint manuale su un database

[Inizio pagina]

Contenuto correlato

[Inizio pagina]

Vedere anche

Concetti

Log delle transazioni (SQL Server)