"De 0 a 60": Mudando para checkpoints indiretos

Em uma dica recente, descrevi um cenário em que uma instância do SQL Server 2016 parecia estar sofrendo com os tempos de checkpoint. O log de erros estava repleto de um número alarmante de entradas FlushCache como esta:

FlushCache: cleaned up 394031 bufs with 282252 writes in 65544 ms (avoided 21 new dirty bufs) for db 19:0 average writes per second: 4306.30 writes/sec average throughput: 46.96 MB/sec, I/O saturation: 53644, context switches 101117 last target outstanding: 639360, avgWriteLatency 1

Fiquei um pouco perplexo com esse problema, já que o sistema certamente não deixava a desejar — muitos núcleos, 3TB de memória e armazenamento XtremIO. E nenhuma dessas mensagens de FlushCache vinha acompanhada dos clássicos avisos de I/O de 15 segundos no log de erros. Ainda assim, se você empilhar um monte de bancos de dados com alto volume de transações ali, o processamento de checkpoints pode ficar bem arrastado. Não tanto pelo I/O direto, mas mais pela reconciliação que precisa ser feita com um número massivo de páginas sujas (dirty pages — e não apenas de transações commitadas) espalhadas por uma quantidade tão grande de memória, e potencialmente esperando pelo lazywriter (já que existe apenas um para toda a instância).  

Fiz uma rápida leitura de "atualização" de alguns posts muito valiosos:

• Como funcionam os checkpoints e o que é registrado (log)

• Checkpoints de Banco de Dados (SQL Server)

• O que o checkpoint faz pelo tempdb?

• Um mito de DBA SQL Server por dia: (15/30) o checkpoint grava apenas páginas de transações commitadas

• Mensagens de FlushCache podem não ser um travamento real de IO

• Checkpoint indireto e tempdb – o bom, o mau e o scheduler que não cede (non-yielding)

• Alterar o Tempo de Recuperação de Destino (Target Recovery Time) de um Banco de Dados

• Como funciona: Quando a mensagem FlushCache é adicionada ao Log de Erros do SQL Server?

• Mudanças no comportamento de Checkpoint no SQL Server 2016

• Intervalo de Recuperação de Destino e Checkpoint Indireto – Novo Padrão de 60 Segundos no SQL Server 2016

• SQL 2016 – Simplesmente Roda Mais Rápido: Padrão de Checkpoint Indireto

• SQL Server: muita RAM e Checkpointing de BD

Monitorando os Checkpoints

Decidi rapidamente que queria monitorar as durações dos checkpoints para alguns desses bancos de dados mais problemáticos, antes e depois de alterar o intervalo de recuperação de destino de 0 (o método antigo) para 60 segundos (o método novo). Veja uma sessão de Extended Events:

CREATE EVENT SESSION CheckpointTracking ON SERVER 
ADD EVENT sqlserver.checkpoint_begin
(
  WHERE 
  (
       sqlserver.database_id = 19 -- db4
    OR sqlserver.database_id = 78 -- db2
    -- ...
  )
)
, ADD EVENT sqlserver.checkpoint_end
(
  WHERE 
  (
       sqlserver.database_id = 19 -- db4
    OR sqlserver.database_id = 78 -- db2
    -- ...
  )
)
ADD TARGET package0.event_file
(
  SET filename = N'L:\SQL\CP\CheckPointTracking.xel',
      max_file_size = 50, -- MB
      max_rollover_files = 50
)
WITH 
(
  MAX_MEMORY = 4096 KB,
  MAX_DISPATCH_LATENCY = 30 SECONDS, 
  TRACK_CAUSALITY = ON,
  STARTUP_STATE = ON
);
GO

ALTER EVENT SESSION CheckpointTracking ON SERVER 
  STATE = START;

Anotei o momento em que alterei cada banco de dados e, em seguida, analisei os resultados dos dados do Extended Events usando uma consulta publicada na dica original. Os resultados mostraram que, após a mudança para checkpoints indiretos, a média dos checkpoints de cada banco de dados caiu de 30 segundos para menos de um décimo de segundo (e com muito menos checkpoints na maioria dos casos, também). Há muito o que analisar nesse gráfico, mas estes são os dados brutos que usei para apresentar meu argumento:  

Para reduzir a frequência dos erros FlushCache registrados no Error Log, decidimos habilitar os Indirect Checkpoints nos bancos de dados que apresentavam mais problemas.

Os resultados foram impressionantes. Abaixo estão a frequência e a duração dos checkpoints antes e depois da alteração do Target Recovery Time de 0 para 60 segundos.

Não monitoramos o db4 com Extended Events (XE) até 16 de janeiro.

Nem todos os checkpoints estavam sendo registrados; apenas os eventos FlushCache obtidos a partir do Error Log.

Isso demonstra dois pontos importantes sobre não utilizar o valor 0 (padrão) para o Recovery Time:

Os checkpoints tornam-se muito mais rápidos quando passam a ser baseados na quantidade de páginas modificadas (dirty pages) em vez da quantidade de transações (e isso também se refletirá no processo de recuperação após uma falha). Os checkpoints tornam-se muito menos frequentes em cargas de trabalho com alto volume de transações que modificam poucas páginas, como: operações com muitas atualizações; inserções incrementais em grande volume;

cenários com alta reutilização de páginas.

Recomendação

Alterar todos os bancos de dados para Target Recovery Time = 60 segundos (com exceção dos bancos model e tempdb).

Implementação em Larga Escala

Uma vez que comprovei meu caso nesses bancos de dados problemáticos, recebi o sinal verde para implementar isso em todos os nossos bancos de dados de usuários em todo o ambiente. Primeiro em dev, e depois em produção, executei o seguinte por meio de uma consulta CMS (Central Management Server) para ter uma noção de quantos bancos de dados estávamos falando:

SQL

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += CASE
  WHEN (ag.role = N'PRIMARY' AND ag.ag_status = N'READ_WRITE') OR ag.role IS NULL THEN N'
    ALTER DATABASE ' + QUOTENAME(d.name) + N' SET TARGET_RECOVERY_TIME = 60 SECONDS;' 
  ELSE N'
    PRINT N''-- corrigir ' + QUOTENAME(d.name) + N' no Primário.'';' 
  END
FROM sys.databases AS d 
OUTER APPLY
(
  SELECT role = s.role_desc, 
    ag_status = DATABASEPROPERTYEX(c.database_name, N'Updateability')
    FROM sys.dm_hadr_availability_replica_states AS s
    INNER JOIN sys.availability_databases_cluster AS c
       ON s.group_id = c.group_id 
       AND d.name = c.database_name
    WHERE s.is_local = 1
) AS ag
WHERE d.target_recovery_time_in_seconds <> 60
  AND d.database_id > 4 
  AND d.[state] = 0 
  AND d.is_in_standby = 0 
  AND d.is_read_only = 0;

SELECT DatabaseCount = @@ROWCOUNT, Version = @@VERSION, cmd = @sql;

--EXEC sys.sp_executesql @sql;

Algumas observações sobre a consulta:

• database_id > 4 Eu não queria mexer no master de forma alguma, e ainda não queria alterar o tempdb porque não estamos na CU (Atualização Cumulativa) mais recente do SQL Server 2017 (veja o KB #4497928 para entender um dos motivos pelos quais esse detalhe é importante). Isso exclui o model também, porque alterar o model afetaria o tempdb no próximo failover/reinicialização. Eu poderia ter alterado o msdb, e posso voltar para fazer isso em algum momento, mas meu foco aqui eram os bancos de dados de usuários.

• [state] / is_read_only / is_in_standby Precisamos garantir que os bancos de dados que estamos tentando alterar estejam online e não sejam apenas leitura (read-only). Eu esbarrei em um que estava configurado como somente leitura e terei que voltar a ele mais tarde.

• OUTER APPLY (...) Queremos restringir nossas ações a bancos de dados que sejam o primário em um AG (Availability Group) ou que não estejam em um AG de forma alguma (e também temos que levar em conta os AGs distribuídos, onde podemos ser primários e locais, mas ainda assim não ter permissão de escrita). Se por acaso você rodar a verificação em um secundário, não poderá corrigir o problema lá, mas ainda deve receber um aviso sobre isso. Obrigado a Erik Darling por ajudar com essa lógica, e Taylor Martell por motivar melhorias.

Se você tiver instâncias executando versões mais antigas, como o SQL Server 2008 R2 (eu encontrei uma!), terá que ajustar isso um pouco, já que a coluna target_recovery_time_in_seconds não existe lá. Tive que usar SQL dinâmico para contornar isso em um caso, mas você também pode mover temporariamente ou remover onde essas instâncias se encontram na sua hierarquia do CMS. Você também pode não ser preguiçoso como eu e rodar o código no PowerShell em vez de uma janela de consulta do CMS, onde você poderia facilmente filtrar os bancos de dados por qualquer número de propriedades antes mesmo de encontrar problemas em tempo de compilação.

Resultados em Produção

Na produção, havia 102 instâncias (cerca da metade) e um total de 1.590 bancos de dados usando a configuração antiga. Tudo estava no SQL Server 2017, então por que essa configuração era tão predominante? Porque eles foram criados antes que os checkpoints indiretos se tornassem o padrão no SQL Server 2016.

Em seguida, rodei a consulta do CMS novamente, desta vez com o sys.sp_executesql descomentado. Levou cerca de 12 minutos para executar isso em todos os 1.590 bancos de dados. Em menos de uma hora, já estava recebendo relatos de pessoas observando uma queda significativa na CPU em algumas das instâncias mais movimentadas.

Ainda tenho mais a fazer. Por exemplo, preciso testar o impacto potencial no tempdb e verificar se as histórias de terror que ouvi têm algum peso no nosso caso de uso. E precisamos garantir que a configuração de 60 segundos faça parte da nossa automação e de todas as solicitações de criação de banco de dados, especialmente as que são roteirizadas (scripted) ou restauradas de backups.