Pontos de verificação de banco de dados (SQL Server)Database Checkpoints (SQL Server)

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Um ponto de verificação cria um bom ponto conhecido a partir do qual o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine pode começar a aplicar as alterações contidas no log durante a recuperação após um desligamento ou uma falha inesperada.A checkpoint creates a known good point from which the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

Visão geralOverview

Por razões de desempenho, o Mecanismo de Banco de DadosDatabase Engine executa modificações nas páginas de banco de dados, no cache do buffer e não grava essas páginas em disco após cada alteração.For performance reasons, the Mecanismo de Banco de DadosDatabase Engine performs modifications to database pages in memory-in the buffer cache-and does not write these pages to disk after every change. Em vez disso, o Mecanismo de Banco de DadosDatabase Engine emite um ponto de verificação periodicamente em cada banco de dados.Rather, the Mecanismo de Banco de DadosDatabase Engine periodically issues a checkpoint on each database. Um ponto de verificação grava as páginas atuais modificadas na memória (conhecidas como páginas sujas) e as informações do log de transações da memória para o disco e, além disso, registra informações no log de transações.A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also records the information in the transaction log.

O Mecanismo de Banco de DadosDatabase Engine oferece suporte para vários tipos de pontos de verificação: automáticos, indiretos, manuais e internos.The Mecanismo de Banco de DadosDatabase Engine supports several types of checkpoints: automatic, indirect, manual, and internal. A seguinte tabela resume os tipos de pontos de verificação:The following table summarizes the types of checkpoints:

NomeName Transact-SQLTransact-SQL InterfaceInterface DescriçãoDescription
AutomaticAutomatic EXEC sp_configure ' recovery interval ',' seconds 'EXEC sp_configure ' recovery interval ','seconds' Emitido automaticamente em segundo plano para seguir de tempo superior sugerido pela opção de configuração de servidor recovery interval .Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. Pontos de verificação automáticos executados até a conclusão.Automatic checkpoints run to completion. Os pontos de verificação automáticos são limitados com base no número de gravações pendentes e se o Mecanismo de Banco de DadosDatabase Engine detectar um aumento na latência de gravação acima de 50 milissegundos.Automatic checkpoints are throttled based on the number of outstanding writes and whether the Mecanismo de Banco de DadosDatabase Engine detects an increase in write latency above 50 milliseconds.

Para obter mais informações, consulte Configure the recovery interval Server Configuration Option.For more information, see Configure the recovery interval Server Configuration Option.
Indireto.Indirect ALTER DATABASE ... SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }ALTER DATABASE ... SET TARGET_RECOVERY_TIME =target_recovery_time { SECONDS | MINUTES } Emitido em segundo plano para cumprir um horário de recuperação de destino especificado pelo usuário para um determinado banco de dados.Issued in the background to meet a user-specified target recovery time for a given database. A partir do SQL Server 2016 (13.x)SQL Server 2016 (13.x), o valor padrão é de 1 minuto.Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the default value is 1 minute. O padrão é 0 para versões mais antigas, o que indica que o banco de dados usará pontos de verificação automáticos cuja frequência depende da configuração do intervalo de recuperação da instância de servidor.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.

Para obter mais informações, consulte Alterar o tempo de recuperação de destino de um banco de dados (SQL Server).For more information, see Change the Target Recovery Time of a Database (SQL Server).
ManualManual CHECKPOINT [checkpoint_duration]CHECKPOINT [checkpoint_duration] Emitido quando você executa um comando Transact-SQLTransact-SQL CHECKPOINT.Issued when you execute a Transact-SQLTransact-SQL CHECKPOINT command. O ponto de verificação manual ocorre no banco de dados atual para sua conexão.The manual checkpoint occurs in the current database for your connection. Por padrão, pontos de verificação manuais são executados até a conclusão.By default, manual checkpoints run to completion. A aceleração funciona da mesma forma que para pontos de verificação automáticos.Throttling works the same way as for automatic checkpoints. Opcionalmente, o parâmetro checkpoint_duration especifica a quantidade de tempo solicitada, em segundos, para a conclusão do ponto de verificação.Optionally, the checkpoint_duration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete.

Para obter mais informações, consulte CHECKPOINT (Transact-SQL).For more information, see CHECKPOINT (Transact-SQL).
InternalInternal Nenhum.None. Emitido por várias operações de servidor, como backup e criação de instantâneo de banco de dados, para garantir que as imagens de disco coincidam com o estado atual do 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.

Observação

A opção de configuração avançada -k SQL ServerSQL Server permite que um administrador de banco de dados acelere o comportamento de E/S do ponto de verificação com base na taxa de transferência do subsistema de E/S de alguns tipos de pontos de verificação.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. A opção de configuração -k se aplica a pontos de verificação automáticos e a qualquer ponto de verificação manual e interno.The -k setup option applies to automatic checkpoints and any otherwise unthrottled manual and internal checkpoints.

Para pontos de verificação automáticos, manuais e internos, somente as modificações feitas após o último ponto de verificação devem passar por roll forward durante a recuperação de banco de dados.For automatic, manual, and internal checkpoints, only modifications made after the latest checkpoint need to be rolled forward during database recovery. Isso reduz o tempo necessário para recuperar um banco de dados.This reduces the time required to recover a database.

Importante

Transações não confirmadas de execução longa aumentam o tempo de recuperação para todos os tipos de pontos de verificação.Long-running, uncommitted transactions increase recovery time for all checkpoint types.

Interação de TARGET_RECOVERY_TIME e opções 'recovery interval'Interaction of the TARGET_RECOVERY_TIME and 'recovery interval' Options

A tabela a seguir resume a interação entre a configuração do servidor sp_configure ' recovery interval ' e a configuração ALTER DATABASE ... TARGET_RECOVERY_TIME específica do banco de dados.The following table summarizes the interaction between the server-wide sp_configure ' recovery interval ' setting and the database-specific ALTER DATABASE ... TARGET_RECOVERY_TIME setting.

target_recovery_timeTARGET_RECOVERY_TIME 'recovery interval''recovery interval' Tipo de ponto de verificação usadoType of Checkpoint Used
00 00 pontos de verificação automáticos cujo intervalo de recuperação de destino é 1 minuto.automatic checkpoints whose target recovery interval is 1 minute.
00 >0>0 Pontos de verificação automáticos, cujo intervalo de recuperação de destino é especificado pela configuração definida pelo usuário na opção sp_configure 'recovery interval' .Automatic checkpoints whose target recovery interval is specified by the user-defined setting of the sp_configure 'recovery interval' option.
>0>0 Não aplicável.Not applicable. Pontos de verificação indiretos cuja hora de recuperação de destino é determinada pela configuração de TARGET_RECOVERY_TIME, expressa em segundos.Indirect checkpoints whose target recovery time is determined by the TARGET_RECOVERY_TIME setting, expressed in seconds.

Pontos de verificação automáticosAutomatic checkpoints

Um ponto de verificação automático ocorre sempre que o número de registros de log atinge o número que o Mecanismo de Banco de DadosDatabase Engine estima que pode processar durante o tempo especificado na opção de configuração do servidor recovery interval .An automatic checkpoint occurs each time the number of log records reaches the number the Mecanismo de Banco de DadosDatabase Engine estimates it can process during the time specified in the recovery interval server configuration option. Para obter mais informações, consulte Configure the recovery interval Server Configuration Option.For more information, see Configure the recovery interval Server Configuration Option.

Em todo banco de dados sem uma hora de recuperação de destino definida pelo usuário, o Mecanismo de Banco de DadosDatabase Engine gera pontos de verificação automáticos.In every database without a user-defined target recovery time, the Mecanismo de Banco de DadosDatabase Engine generates automatic checkpoints. A frequência depende da opção de configuração de servidor avançada recovery interval , que especifica o tempo máximo que determinada instância de servidor deve usar para recuperar um banco de dados durante uma reinicialização do 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. O Mecanismo de Banco de DadosDatabase Engine estima o número máximo de registros de log que pode processar no intervalo de recuperação.The Mecanismo de Banco de DadosDatabase Engine estimates the maximum number of log records it can process within the recovery interval. Quando um banco de dados que usa alcances de pontos de verificação automáticos atinge o número máximo de registros de log, o Mecanismo de Banco de DadosDatabase Engine emite um ponto de verificação no banco de dados.When a database using automatic checkpoints reaches this maximum number of log records, the Mecanismo de Banco de DadosDatabase Engine issues an checkpoint on the database.

O intervalo de tempo entre pontos de verificação automáticos pode ser altamente variável.The time interval between automatic checkpoints can be highly variable. Um banco de dados com uma carga de trabalho de transações significativa terá mais pontos de verificação frequentes do que um banco de dados usado principalmente para operações somente leitura.A database with a substantial transaction workload will have more frequent checkpoints than a database used primarily for read-only operations. No modelo de recuperação simples, um ponto de verificação automático também será colocado na fila se o log se tornar 70% cheio.Under the simple recovery model, an automatic checkpoint is also queued if the log becomes 70 percent full.

No modelo de recuperação simples, a menos que algum fator esteja atrasando o truncamento do log, um ponto de verificação automático trunca a seção não usada do log de transações.Under the simple recovery model, unless some factor is delaying log truncation, an automatic checkpoint truncates the unused section of the transaction log. Em contrapartida, nos modelos de recuperação completo e bulk-logged, após o estabelecimento de uma cadeia de backup de log, os pontos de verificação automáticos não causam truncamento de 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. Para obter mais informações, consulte O log de transações (SQL Server).For more information, see The Transaction Log (SQL Server).

Depois de uma falha do sistema, o tempo necessário para recuperar determinado banco de dados depende em grande parte da quantidade de E/S aleatória necessária para refazer páginas que estavam sujas no momento da falha.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. Isso significa que a configuração de recovery interval não é confiável.This means that the recovery interval setting is unreliable. Não é possível determinar uma duração de recuperação precisa.It cannot determine an accurate recovery duration. Além disso, quando um ponto de verificação automático está em andamento, a atividade de E/S geral para os dados aumenta significativamente e de forma bastante imprevisível.Furthermore, when an automatic checkpoint is in progress, the general I/O activity for data increases significantly and quite unpredictably.

Impacto do intervalo de recuperação no desempenho de recuperaçãoImpact of recovery interval on recovery performance

Para um sistema de processamento de transações online usando transações curtas (OLTP), o recovery interval é o fator primário que determina o tempo da recuperação.For an online transaction processing (OLTP) system using short transactions, recovery interval is the primary factor determining recovery time. No entanto, a opção recovery interval não afeta o tempo necessário para desfazer uma transação de longa duração.However, the recovery interval option does not affect the time required to undo a long-running transaction. A recuperação de um banco de dados com uma transação de longa duração pode levar mais tempo do que o especificado na configuração recovery interval.Recovery of a database with a long-running transaction can take much longer than the time specified in the recovery interval setting.

Por exemplo, se uma transação demorada levou duas horas para executar atualizações antes que a instância do servidor fosse desabilitada, a recuperação real levará um tempo consideravelmente mais longo do que o valor de recovery interval para recuperar a transação demorada.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. Para obter mais informações sobre o impacto de uma transação de longa duração em um tempo de recuperação, veja O log de transações (SQL Server).For more information about the impact of a long running transaction on recovery time, see The Transaction Log (SQL Server). Saiba mais sobre a recuperação de banco de dados em Visão geral da restauração e recuperação (SQL Server).For more information about the recovery process, see Restore and Recovery Overview (SQL Server).

Normalmente, os valores padrão fornecem um ótimo desempenho de recuperação.Typically, the default values provides optimal recovery performance. No entanto, a alteração do intervalo de recuperação pode melhorar o desempenho nas circunstâncias seguintes:However, changing the recovery interval might improve performance in the following circumstances:

  • Se, em geral, a recuperação demorar significativamente mais que 1 minuto quando transações demoradas não estão sendo revertidas.If recovery routinely takes significantly longer than 1 minute when long-running transactions are not being rolled back.

  • Se você notar que os pontos de verificação frequentes estão prejudicando o desempenho em um banco de dados.If you notice that frequent checkpoints are impairing performance on a database.

Se você decidir aumentar a configuração recovery interval , recomendamos fazer isso gradativamente em pequenos incrementos e avaliar o efeito de cada aumento incremental no desempenho de recuperação.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. Esse método é importante porque, à medida que a configuração recovery interval aumenta, a recuperação de banco de dados demora mais tempo para ser concluída.This approach is important because as the recovery interval setting increases, database recovery takes that many times longer to complete. Por exemplo, se você alterar recovery interval para 10 minutos, a recuperação levará aproximadamente 10 vezes mais tempo para ser concluída do que se recovery interval tivesse sido definido para 1 minuto.For example, if you change recovery interval to 10 minutes, recovery takes approximately 10 times longer to complete than when recovery interval is set to 1 minute.

Pontos de verificação indiretosIndirect checkpoints

Pontos de verificação indiretos, introduzidos no SQL Server 2012 (11.x)SQL Server 2012 (11.x), fornecem uma alternativa de nível de banco de dados configurável para os pontos de verificação automáticos.Indirect checkpoints, introduced in SQL Server 2012 (11.x)SQL Server 2012 (11.x), provide a configurable database-level alternative to automatic checkpoints. Isso pode ser configurado especificando a opção de configuração de banco de dados tempo de recuperação de destino.This can be configured by specifying the target recovery time database configuration option. Para obter mais informações, veja Alterar o tempo de recuperação de destino de um banco de dados (SQL Server).For more information, see Change the Target Recovery Time of a Database (SQL Server). No caso de uma falha de sistema, pontos de verificação indiretos fornecem um tempo de recuperação mais previsível potencialmente mais rápido do que os pontos de verificação automáticos.In the event of a system crash, indirect checkpoints provide potentially faster, more predictable recovery time than automatic checkpoints. Os pontos de verificação indiretos oferecem as seguintes vantagens:Indirect checkpoints offer the following advantages:

  • Uma carga de trabalho transacional online em um banco de dados configurado para pontos de verificação indiretos pode apresentar degradação no desempenho.An online transactional workload on a database configured for indirect checkpoints can experience performance degradation. Pontos de verificação indiretos garantem que o número de páginas sujas fica abaixo de determinado limite, para que a recuperação do banco de dados seja concluída dentro da meta do tempo de recuperação.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 opção de configuração do intervalo recovery interval usa o número de transações para determinar o tempo de recuperação em relação aos pontos de verificação indiretos, que usam o número de páginas sujas.The recovery interval configuration option uses the number of transactions to determine the recovery time, as opposed to indirect checkpoints which makes use of the number of dirty pages. Quando pontos de verificação indiretos são habilitados em um banco de dados que recebe um grande número de operações DML, o gravador em segundo plano pode iniciar eliminação agressiva de buffers sujos no disco para garantir que o tempo necessário para executar a recuperação esteja dentro da meta do tempo de recuperação definido para o banco de dados.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. Isso pode causar atividade adicional de E/S em determinados sistemas, o que pode contribuir para um gargalo de desempenho, se o subsistema do disco estiver operando acima ou próximo do limite de E/S.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.

  • Pontos de verificação indiretos permitem a você controlar o tempo de recuperação de banco de dados de modo confiável, fatorando no custo de E/S aleatória durante REDO.Indirect checkpoints enable you to reliably control database recovery time by factoring in the cost of random I/O during REDO. Isso permite que uma instância de servidor permaneça em um limite superior nos tempos de recuperação para um determinado banco de dados (exceto quando uma transação demorada causa tempos UNDO excessivos).This enables a server instance to stay within an upper-bound limit on recovery times for a given database (except when a long-running transaction causes excessive UNDO times).

  • Pontos de verificação indiretos reduzem os picos de E/S relacionados ao ponto de verificação gravando continuamente páginas sujas em disco em segundo plano.Indirect checkpoints reduce checkpoint-related I/O spiking by continually writing dirty pages to disk in the background.

No entanto, uma carga de trabalho transacional online em um banco de dados configurado para pontos de verificação indiretos pode apresentar degradação no desempenho.However, an online transactional workload on a database configured for indirect checkpoints can experience performance degradation. Isso ocorre porque o gravador em segundo plano usado pelo ponto de verificação indireto às vezes aumenta a carga de gravação total de uma instância de servidor.This is because the background writer used by indirect checkpoint sometimes increases the total write load for a server instance.

Importante

O ponto de verificação indireto é o comportamento padrão de novos bancos de dados criados no SQL Server 2016 (13.x)SQL Server 2016 (13.x), incluindo os bancos de dados de Modelo de TempDB.Indirect checkpoint is the default behavior for new databases created in SQL Server 2016 (13.x)SQL Server 2016 (13.x), including the Model and TempDB databases.
Os bancos de dados atualizados no local ou restaurados com base em uma versão anterior do SQL ServerSQL Server usarão o comportamento de ponto de verificação automático anterior, a menos que tenham sido explicitamente alterados para usar o ponto de verificação indireto.Databases that 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.

Escalabilidade de ponto de verificação indireto aprimoradaImproved indirect checkpoint scalability

Antes do SQL Server 2019 (15.x)SQL Server 2019 (15.x), você podia encontrar erros de agendador sem resposta quando há um banco de dados que gera um grande número de páginas sujas, assim como tempdb.Prior to SQL Server 2019 (15.x)SQL Server 2019 (15.x), you may experience non-yielding scheduler errors when there is a database that generates a large number of dirty pages, such as tempdb. SQL Server 2019 (15.x)SQL Server 2019 (15.x) apresenta melhor escalabilidade para o ponto de verificação indireto, que deve ajudar a evitar esses erros em bancos de dados que têm uma carga de trabalho de UPDATE/INSERT pesada.introduces improved scalability for indirect checkpoint, which should help avoid these errors on databases that have a heavy UPDATE/INSERT workload.

Pontos de verificação internosInternal checkpoints

Os pontos de verificação internos são gerados por vários componentes de servidor para garantir que as imagens de disco correspondam ao estado atual do log.Internal Checkpoints are generated by various server components to guarantee that disk images match the current state of the log. Pontos de verificação internos são gerados em resposta aos eventos seguintes:Internal checkpoint are generated in response to the following events:

  • Os arquivos de banco de dados foram adicionados ou removidos usando ALTER DATABASE.Database files have been added or removed by using ALTER DATABASE.

  • É realizado um backup de banco de dados.A database backup is taken.

  • Um instantâneo do banco de dados é criado, explicitamente ou internamente para DBCC CHECKDB.A database snapshot is created, whether explicitly or internally for DBCC CHECKDB.

  • É executada uma atividade que requer um desligamento de banco de dados.An activity requiring a database shutdown is performed. Por exemplo, AUTO_CLOSE está ON e a última conexão de usuário com o banco de dados está fechada ou é feita uma modificação de opção de banco de dados que requer o reinício do banco de dados.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.

  • Uma instância do SQL ServerSQL Server é interrompida com a interrupção do serviço SQL ServerSQL Server (MSSQLSERVER).An instance of SQL ServerSQL Server is stopped by stopping the SQL ServerSQL Server (MSSQLSERVER) service . Qualquer ação causa um ponto de verificação em cada banco de dados na instância de SQL ServerSQL Server.Either action causes a checkpoint in each database in the instance of SQL ServerSQL Server.

  • Colocando uma FCI (instância de cluster de failover) SQL ServerSQL Server offline.Bringing a SQL ServerSQL Server failover cluster instance (FCI) offline.

Related tasksRelated tasks

Para alterar o intervalo de recuperação em uma instância de servidorTo change the recovery interval on a server instance

Para configurar pontos de verificação indiretos em um banco de dadosTo configure indirect checkpoints on a database

Para emitir um ponto de verificação manual em um banco de dadosTo issue a manual checkpoint on a database

Confira tambémSee also

O log de transações (SQL Server) The Transaction Log (SQL Server)
Guia de arquitetura e gerenciamento de log de transações do SQL ServerSQL Server Transaction Log Architecture and Management Guide