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

  • Artigo

Aplica-se ao: SQL Server Banco de Dados SQL do Azure

Um ponto de verificação cria um ponto válido conhecido com base no qual o Mecanismo de Banco de Dados do SQL Server pode começar a aplicar as alterações contidas no log durante a recuperação após uma pane ou um desligamento inesperado.

Visão geral

Por razões de desempenho, o Mecanismo de Banco de Dados 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. Em vez disso, o Mecanismo de Banco de Dados emite um ponto de verificação periodicamente em cada banco de dados. 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, além de registrar as informações no log de transações.

O Mecanismo de Banco de Dados dá suporte a vários tipos de pontos de verificação: automáticos, indiretos, manuais e internos. A tabela a seguir resume os tipos de pontos de verificação.

Nome Interface do Transact-SQL Descrição
Automático 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 . Pontos de verificação automáticos executados até a conclusão. Os pontos de verificação automáticos são limitados com base no número de gravações pendentes e no fato de o Mecanismo de Banco de Dados detectar um aumento na latência de gravação acima de 50 milissegundos.

Para obter mais informações, consulte Configure the recovery interval Server Configuration Option.
Indireto 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. Começando com o SQL Server 2016 (13.x), o valor padrão é 1 minuto. 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.

Para obter mais informações, veja Alterar o tempo de recuperação alvo de um banco de dados (SQL Server).
Manual CHECKPOINT [ checkpoint_duration ] Emitido quando um comando CHECKPOINT Transact-SQL é executado. O ponto de verificação manual ocorre no banco de dados atual para sua conexão. Por padrão, pontos de verificação manuais são executados até a conclusão. A aceleração funciona da mesma forma que para pontos de verificação automáticos. Opcionalmente, o parâmetro checkpoint_duration especifica a quantidade de tempo solicitada, em segundos, para a conclusão do ponto de verificação.

Para obter mais informações, confira CHECKPOINT (Transact-SQL).
Interna Nenhum 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.

A opção de configuração avançada -k do SQL Server permite que um administrador de banco de dados limite o comportamento de E/S do ponto de verificação com base na taxa de transferência do subsistema de E/S para alguns tipos de pontos de verificação. 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.

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. Isso reduz o tempo necessário para recuperar um banco de dados.

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.

Interação entre o TARGET_RECOVERY_TIME e as opções 'recovery interval'

A tabela a seguir resume a interação entre a configuração sp_configure 'recovery interval' de todo o servidor e a configuração ALTER DATABASE ... TARGET_RECOVERY_TIME específica do banco de dados.

target_recovery_time 'recovery interval' Tipo de ponto de verificação usado
0 0 pontos de verificação automáticos cujo intervalo de recuperação de destino é 1 minuto.
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'.
> 0 Não aplicável Pontos de verificação indiretos cuja hora de recuperação de destino é determinada pela configuração de TARGET_RECOVERY_TIME, expressa em segundos.

Pontos de verificação automáticos

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 Dados estima que pode processar durante o tempo especificado na opção de configuração do servidor recovery interval. Para obter mais informações, consulte 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 Dados gera pontos de verificação automáticos. 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. O Mecanismo de Banco de Dados estima o número máximo de registros de log que pode processar no intervalo de recuperação. Quando um banco de dados que usa pontos de verificação automáticos atinge esse número máximo de registros de log, o Mecanismo de Banco de Dados emite um ponto de verificação no banco de dados.

O intervalo de tempo entre pontos de verificação automáticos pode ser altamente variável. 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. 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.

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. 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 o truncamento de log. Para obter mais informações, confira O log de transações (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. Isso significa que a configuração de recovery interval não é confiável. Ela não pode determinar uma duração de recuperação precisa. Além disso, quando um ponto de verificação automático está em andamento, a atividade de E/S geral para os dados aumenta de maneira considerável e imprevisível.

Efeito do intervalo de recuperação no desempenho de recuperação

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. No entanto, a opção recovery interval não afeta o tempo necessário para desfazer uma transação de longa duração. 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.

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. Para obter mais informações sobre o impacto de uma transação de longa duração em um tempo de recuperação, confira O log de transações (SQL Server). Saiba mais sobre a recuperação de banco de dados em Visão geral da restauração e recuperação (SQL Server).

Normalmente, os valores padrão oferecem um desempenho de recuperação ideal. No entanto, a alteração do intervalo de recuperação pode melhorar o desempenho nas circunstâncias seguintes:

  • Se, em geral, a recuperação demorar consideravelmente mais que 1 minuto quando as transações de longa execução não estiverem sendo revertidas.

  • Se você notar que os pontos de verificação frequentes estão prejudicando o desempenho em um banco de dados.

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

Pontos de verificação indiretos

Os pontos de verificação indiretos, apresentados no SQL Server 2012 (11.x), fornecem uma alternativa de nível de banco de dados configurável aos pontos de verificação automáticos. Isso pode ser configurado especificando a opção de configuração de banco de dados tempo de recuperação de destino. Para obter mais informações, veja Alterar o tempo de recuperação alvo de um banco de dados (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.

Os pontos de verificação indiretos oferecem as seguintes vantagens:

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

    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. 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. Isso poderá 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.

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

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

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

Importante

O ponto de verificação indireto é o comportamento padrão dos bancos de dados criados no SQL Server 2016 (13.x), incluindo os bancos de dados model e tempdb.

Os bancos de dados atualizados in-loco ou restaurados de uma versão anterior do SQL Server usarão o comportamento anterior do ponto de verificação automático, a menos que tenham sido explicitamente alterados para usar o ponto de verificação indireto.

Escalabilidade de ponto de verificação indireto aprimorada

Antes do SQL Server 2019 (15.x), você podia encontrar erros de agendador sem resposta quando havia um banco de dados que gerava um grande número de páginas sujas, como tempdb. O SQL Server 2019 (15.x) apresenta uma melhor escalabilidade para o ponto de verificação indireto, o que deve ajudar a evitar esses erros em bancos de dados que têm uma carga de trabalho pesada de UPDATE/INSERT.

Pontos de verificação internos

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. Os pontos de verificação internos são gerados em resposta aos seguintes eventos:

  • Os arquivos de banco de dados foram adicionados ou removidos usando ALTER DATABASE.

  • É realizado um backup de banco de dados.

  • Um instantâneo do banco de dados é criado, explicitamente ou internamente para DBCC CHECKDB.

  • É executada uma atividade que requer um desligamento de banco de dados. 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.

  • Uma instância do SQL Server é interrompida pela interrupção do serviço do SQL Server (MSSQLSERVER). Essa ação causa um ponto de verificação em cada banco de dados na instância do SQL Server.

  • Colocação de uma FCI (instância de cluster de failover) do SQL Server offline.

Próximas etapas

Confira também