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.
Questo argomento offre una panoramica dei checkpoint del 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 ripristino dopo un arresto imprevisto o un arresto anomalo.
Panoramica dei punti di controllo
Per motivi di prestazioni, il motore di database esegue modifiche alle pagine del database nella cache del buffer e non scrive queste pagine su disco dopo ogni modifica. Il motore di database genera invece periodicamente un checkpoint in ogni database. Un checkpoint scrive le pagine modificate attualmente in memoria (note come pagine sporche) e trasferisce le informazioni del registro delle transazioni dalla memoria al disco, registrando anche ulteriori informazioni sul registro delle transazioni.
Il motore del database supporta diversi 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 di tempo massimo 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 pendenti e quando il motore del database rileva un aumento della latenza di scrittura superiore a 20 millisecondi.Per ulteriori informazioni, vedere Configura l'opzione di configurazione del server per l'intervallo di recupero. |
Indiretto | ALTER DATABASE ... SET TARGET_RECOVERY_TIME =target_recovery_time { SECONDS | MINUTES } | Rilasciato in background per soddisfare un obiettivo di tempo di recupero specificato dall'utente per un database specifico. Il tempo di recupero obiettivo predefinito è 0, il che provoca l'utilizzo di euristiche di checkpoint automatiche nel database. Se è stato usato ALTER DATABASE per impostare TARGET_RECOVERY_TIME su >0, questo valore viene utilizzato anziché l'intervallo di recupero specificato per l'istanza del server. Per altre informazioni, vedere Modificare il tempo di recupero di riferimento di un database (SQL Server). |
Manuale | CHECKPOINT [ checkpoint_duration ] | Generato quando si esegue un comando Transact-SQL CHECKPOINT. Il checkpoint manuale si verifica nel database corrente della tua connessione. Per impostazione predefinita, i checkpoint manuali vengono eseguiti fino al completamento. La limitazione funziona allo stesso modo di per i checkpoint automatici. Facoltativamente, il parametro checkpoint_duration specifica un periodo di tempo richiesto, in secondi, per il completamento del checkpoint. Per altre informazioni, vedere CHECKPOINT (Transact-SQL). |
Interno | Nessuno. | Rilasciato da varie operazioni del server, ad esempio la creazione di backup e snapshot del database, per garantire che le immagini del disco corrispondano allo stato corrente del log. |
Annotazioni
L'opzione -k
di installazione avanzata di 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 -k
di installazione si applica ai checkpoint automatici e a eventuali checkpoint manuali e interni altrimenti non scombustiati.
Per i checkpoint automatici, manuali e interni, è necessario eseguire il rollforward solo delle modifiche apportate dopo il checkpoint più recente durante il ripristino del database. In questo modo si riduce il tempo necessario per ripristinare un database.
Importante
Le transazioni a esecuzione prolungata non confermate aumentano il tempo di recupero per tutti i tipi di checkpoint.
Interazione delle opzioni TARGET_RECOVERY_TIME e 'intervallo di recupero'
La tabella seguente riepiloga l'interazione tra l'impostazione server-wide sp_configurerecovery interval
e l'impostazione specifica del database ALTER DATABASE ... TARGET_RECOVERY_TIME.
TEMPO_DI_RECUPERO_OBIETTIVO | 'intervallo di recupero' | Tipo di checkpoint usato |
---|---|---|
0 | 0 | checkpoint automatici il cui intervallo di recupero obiettivo è di 1 minuto. |
0 | >0 | Checkpoints automatici il cui intervallo di recupero obiettivo viene specificato dall'impostazione definita dall'utente dell'opzione sp_configurerecovery interval. |
>0 | Non applicabile. | Checkpoint indiretti il cui tempo di recupero previsto è determinato dall'impostazione TARGET_RECOVERY_TIME, espresso in secondi. |
Checkpoint automatici
Si verifica un checkpoint automatico ogni volta in cui il numero di record di log raggiunge il numero che il motore di database stima di poter elaborare durante il tempo specificato nell'opzione di configurazione del recovery interval
server. In ogni database senza un tempo di recupero obiettivo definito dall'utente, il Motore di Database genera checkpoint automatici. La frequenza dei checkpoint automatici dipende dall'opzione di configurazione avanzata del recovery interval
server, che specifica il tempo massimo che deve essere usato da una determinata istanza del server per ripristinare un database durante il riavvio del sistema. Il motore di database stima il numero massimo di record di log che può elaborare entro l'intervallo di recupero. Quando un database che utilizza checkpoint automatici raggiunge questo numero massimo di record di log, il motore di database genera un checkpoint per il database. L'intervallo di tempo tra i checkpoint automatici può essere altamente variabile. Un database con un carico di lavoro di transazioni sostanziale avrà checkpoint più frequenti rispetto a un database usato principalmente per le operazioni di sola lettura.
Inoltre, nel modello di recupero semplice, viene accodato un checkpoint automatico se il log diventa pieno al 70%.
Nel modello di recupero semplice, a meno che un fattore non ritardi il troncamento del log, un checkpoint automatico tronca la sezione non utilizzata del log delle transazioni. Al contrario, nei modelli di recupero con registrazione completa e bulk-logged, una volta stabilita 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, il tempo necessario per recuperare un determinato database dipende in gran parte dalla quantità di operazioni di I/O casuali necessarie per ripetere le pagine sporche al momento dell'arresto anomalo. Ciò significa che l'impostazione recovery interval
non è affidabile. Non è in grado di determinare una durata di recupero accurata. Inoltre, quando è in corso un checkpoint automatico, l'attività di I/O generale per i dati aumenta significativamente e in modo piuttosto imprevedibile.
Impatto dell'intervallo di recupero sulle prestazioni di ripristino
Per un sistema OLTP (Online Transaction Processing) che utilizza transazioni brevi, recovery interval
è il fattore principale che determina il tempo di recupero. Tuttavia, l'opzione recovery interval
non influisce sul tempo necessario per annullare una transazione a esecuzione prolungata. Il recupero di un database con una transazione a esecuzione prolungata può richiedere molto più tempo rispetto a quello specificato nell'opzione recovery interval
. Ad esempio, se una transazione con esecuzione prolungata richiede due ore per eseguire aggiornamenti prima che l'istanza del server sia disabilitata, il recupero effettivo richiede molto più tempo del recovery interval
valore per recuperare la transazione lunga. Per altre informazioni sull'impatto di una transazione a esecuzione prolungata sul tempo di ripristino, vedere Log delle transazioni (SQL Server).
In genere, i valori predefiniti offrono prestazioni di ripristino ottimali. Tuttavia, la modifica dell'intervallo di recupero potrebbe migliorare le prestazioni nelle circostanze seguenti:
Nel caso in cui il ripristino richieda regolarmente considerevolmente più di 1 minuto, quando non viene eseguito il rollback delle transazioni a lungo termine.
Se si nota che i 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 delle prestazioni di ripristino. Questo approccio è importante perché, man mano che aumenta il livello dell'impostazione recovery interval
, il ripristino del database richiede un tempo di recupero molte volte più lungo. Ad esempio, se si modifica recovery interval
10, il completamento del ripristino richiede circa 10 volte più tempo rispetto a quando recovery interval
è impostato su zero.
Checkpoint indiretti
I checkpoint indiretti, novità di SQL Server 2012, offrono un'alternativa a livello di database configurabile ai checkpoint automatici. In caso di arresto anomalo del sistema, i checkpoint indiretti offrono tempi di ripristino potenzialmente più veloci e prevedibili rispetto ai checkpoint automatici. I checkpoint indiretti offrono i vantaggi seguenti:
Un carico di lavoro transazionale online su un database configurato per i checkpoint indiretti potrebbe subire una riduzione del livello 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 gestire con precisione il tempo di ripristino del database tenendo conto del costo dell'I/O casuale durante il REDO. Ciò consente a un'istanza del server di rimanere entro un limite superiore nei tempi di ripristino per un determinato database (tranne quando una transazione con esecuzione prolungata causa un numero eccessivo di volte UNDO).
I checkpoint indiretti riducono lo spiking di I/O dei checkpoint scrivendo continuamente pagine sporche su disco in background.
Tuttavia, un carico di lavoro transazionale online in un database configurato per i checkpoint indiretti potrebbe subire una riduzione delle prestazioni. Ciò è dovuto al fatto che il writer in background usato dal checkpoint indiretto talvolta aumenta il carico totale di scrittura per un'istanza del server.
Punti di controllo interni
I checkpoint interni vengono generati da vari componenti server per garantire che le immagini del disco corrispondano allo stato corrente del log. I checkpoint interni vengono generati in risposta ai seguenti eventi:
I file di database sono stati aggiunti o rimossi tramite ALTER DATABASE.
Viene eseguito un backup del database.
Viene creato uno snapshot del database, sia in modo esplicito che interno per DBCC CHECK.
Viene eseguita un'attività che richiede un arresto del database. Ad esempio, AUTO_CLOSE è attivato e l'ultima connessione utente al database viene chiusa oppure viene apportata una modifica dell'opzione di database che richiede un riavvio del database.
Per arrestare un'istanza di SQL Server, è necessario interrompere il servizio SQL Server (MSSQLSERVER). Entrambe le azioni provocano un checkpoint in ciascun database nell'istanza di SQL Server.
Portare offline un'istanza del cluster di failover di SQL Server.
Attività correlate
Per modificare l'intervallo di recupero in un'istanza del server
Per configurare i checkpoint indiretti su un database
Per eseguire un checkpoint manuale in un database
Contenuto correlato
- Architettura fisica del log delle transazioni (nella documentazione Oline di SQL Server 2008 R2)