Deixe-me começar esta postagem dizendo que alguns eventos estão faltando em meu rastreamento, mas já os adicionei para a próxima vez que isso ocorrer.
Ultimamente temos visto surtos estranhos de tipos de espera HADR_SYNC_COMMIT em nosso ambiente (~40k tran/s). O "incidente" de hoje ocorreu às 4h58:
Antes de continuar, devo acrescentar que estávamos fazendo manutenção de índice ONLINE em uma grande tabela de auditoria (auditoria no sentido de que uma tabela OLTP aciona registros para esta tabela de auditoria em um volume alto) e que a reconstrução do índice foi bloqueada por aproximadamente 22 segundos. Claramente, isso desempenha um papel neste caso específico, mas não tenho certeza de como isso se relaciona com HADR_SYNC_COMMIT. Além disso, temos visto isso ocorrer durante o dia, quando não fazemos manutenção do índice.
Olhando para o rastreamento, aqui está o que vejo no primário:
...e tudo no secundário:
... e finalmente de volta ao primário:
Um problema semelhante aconteceu novamente em 01/12/2023 por volta das 4h11 e acredito ter visto o que está acontecendo. Infelizmente, não tenho eventos estendidos para este cenário, mas tenho alguns registros que podem mostrar uma imagem mais clara. A partir de 01/12/2023 04:10:18.5430090, a manutenção do índice de Ola registrou um horário de início para um índice no banco de dados em questão. Ola relatou um tempo de conclusão de 2023-12-01 04:11:13.9431563, mas acredito que o REBUILD WITH ONLINE = ON real foi concluído muito antes disso.
Ao revisar o DPA, percebi que as esperas de pagelatch_sh e pagelatch_ex aumentaram entre 4h11:03 e 4h11:04:
Imediatamente após essas esperas, a mesma consulta começou a ver HADR_SYNC_COMMIT e essa mesma espera caiu completamente às 4:11:13-4:11:14 da manhã, que é exatamente quando Ola relatou o término do índice. Minha suposição é que o índice REBUILD foi confirmado às 4h11min03s (cerca de 45 segundos de trabalho), o que fez com que as consultas INSERT não relacionadas que estavam no mesmo banco de dados simplesmente esperassem que todos esses blocos de log fossem reforçados no secundário . Assim que o índice foi concluído, os blocos de log restantes foram reforçados instantaneamente, pois eram apenas pequenos INSERTs.
Eu queria parabenizar por mergulhar e investigar o uso do XE, essa é definitivamente uma etapa correta na solução final. Dito isto, é improvável que consigamos ajudar diretamente de alguma forma significativa. Deixe-me explicar.
As esperas HAD_SYNC_COMMIT são quase como qualquer outra espera, pois são do tipo espera, mas na verdade são redefinidas a cada ~ 3 segundos (detalhe da implementação), que é onde
hadr_db_commit_mgr_harden_still_waitingentra e será acionado por quantas rodadas de verificação forem necessárias até que o bloco de log seja marcado endurecido. Fora dos detalhes de implementação, todo o resto é como outras esperas e os mesmos problemas ocorrerão - lembre-se de que o SQL Server usa agendamento cooperativo e se você entrar em detalhes é orientado a eventos, de modo que muitos eventos sendo sinalizados (como uma espera são concluídos e a tarefa pode avançar) pode causar problemas de tipo de comboio estourado (todos os threads estão esperando, então todos os threads são "acordados", o que causa uma corrida de CPU/agendamento, que então causa o comboio novamente).Ao investigar esses tipos de espera, infelizmente você precisará de uma grande quantidade de dados, incluindo uma captura de pacotes dos hosts locais e remotos, XE para HADR, arquivo/disco, agendamento e rastreamentos ETW no Windows para disco, filtro, CPU. É uma grande quantidade de dados e não estou sugerindo que você faça isso aqui porque o problema está nas informações que você forneceu.
Quaisquer transações grandes e longas ( especialmente atividades relacionadas a índices ) não são uma boa opção para a forma como os Grupos de Disponibilidade são implementados. As reconstruções de índice offline são concluídas como uma única transação gigante, ONLINE são melhores porque são transações menores em vários pontos, mas ambas as operações podem realmente impulsionar CPU e IO - o que não é necessariamente uma coisa ruim . Se o seu servidor tiver capacidade de desempenho para gerar uma grande quantidade de log em um curto espaço de tempoperíodo de tempo em que a geração é mais rápida que a latência da rede + latência do disco, o banco de dados (e a instância) não conseguirá acompanhar a geração de log pendente para enviar às outras réplicas. Você começará a atingir esperas de confirmação de sincronização em réplicas síncronas (já que isso se aplica apenas a parceiros de confirmação síncrona).
Os grupos de disponibilidade enviam dados por meio de blocos de log, que são a menor unidade de agrupamento lógico de itens no nível de log e têm um tamanho mínimo de 512 bytes (costumava ter o tamanho de 1 setor, que mudou desde então, considerando 4k, 8k, 16k e até 32k tamanhos de setor) e máximo de 60k, embora seu tamanho real dependa se um commit termina o bloco ou se o bloco atinge o tamanho máximo. A questão é que a transação em si não éa unidade de transferência e múltiplas transações de múltiplas sessões diferentes podem e serão misturadas em um bloco de log. Portanto, se você estiver gerando uma grande quantidade de blocos de log e as transações estiverem entrelaçadas entre eles, qualquer transação confirmada precisará que o bloco de log pendente para sua própria transação seja reforçado antes de continuar, o que provavelmente fará parte de outro bloco de log que pode também estará muito mais adiante, à medida que as coisas começarem a retroceder.
Outro ponto de discórdia é a rede, que novamente é um detalhe de implementação como parte do UCS no SQL Server. Fora dos AGs distribuídos no SQL Server 2022+ ( que apresenta alguns problemas no momento ), uma única conexão é usada para transmitir dados por TCP para cada réplica. Isso significa que, mesmo com uma rede de grande largura de banda, você estará limitado pela latência, pois isso determinará o número aproximado de solicitações pendentes que podem ou não atingir o limite de limitação no SQL Server (esse limite foi aumentado em 2022). Isso significa que você nunca será capaz de levar a sériopreencha um grande canal de largura de banda com uma única instância do SQL Server usando grupos de disponibilidade e essa latência é muito mais importante - portanto, uma réplica de confirmação de sincronização do outro lado do mundo com uma latência de 150 ms fará backup severo e grandes cargas de trabalho de geração de log - ou seja reconstruções de índice.
Mencionei a necessidade de agendamento e dados da CPU, isso se deve ao fato da geração do log acontecer, mas precisa ser empacotado e enviado pela rede. Tudo isso acontece de forma assíncrona em vários threads e filas. Se a instância estiver sob pressão de CPU em uma única CPU, isso poderá afetar o empacotamento (compactação, criptografia, serialização, informações UCS [vagões], informações de mensagens) e o envio (thread de envio, thread de recebimento) junto com as réplicas secundárias que estão fazendo o mesma coisa na ordem oposta. Isto é especialmente verdadeiro em réplicas secundárias legíveis, onde a carga de trabalho de leitura pode prejudicar ou reduzir a capacidade do AG de se proteger em tempo hábil. Observe que o endurecimento requer uma gravação no disco (que é onde os dados de E/S entram) e não precisa ser refeito.
Tudo isso quer dizer que, ao reconstruir índices, isso é esperado e eu não perderia tempo investigando mais isso.
Pelo que posso ver, isso está acontecendo porque seu AG AlwaysOn está refazendo de forma síncrona a reconstrução do índice no secundário e isso leva tempo. Essas esperas descrevem exatamente essa situação.
Se você estiver usando um disco SSD rápido como armazenamento, simplesmente não poderá executar a reconstrução do índice. Para desempenho, basta atualizar as estatísticas.
Se você quiser explorar mais esse assunto, sugiro avaliar os eventos estendidos descritos aqui:
https://techcommunity.microsoft.com/t5/sql-server-blog/troubleshooting-high-hadr-sync-commit-wait-type-with-always-on/ba-p/385369