(Originalmente postado em DBA.StackExchange.com, mas fechado, espero que seja mais relevante aqui.)
Alexander e os terríveis, horríveis, nada bons, muito ruins... backups.
A configuração:
Eu tenho um localSQL Server 2016 Edição Padrãoinstância em execução em ummáquina virtualda VMWare.
@@Versão:
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) 19 de março de 2021 19:41:38 Copyright (c) Microsoft Corporation Standard Edition (64 bits) no Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hipervisor)
O próprio servidor está atualmente alocado8 processadores virtuais, tem32 GB de memória, e todos osdiscos são NVMesque se locomovem1 GB/seg de E/S. Os próprios bancos de dados residem na unidade G: e os backups são armazenados separadamente na unidade P:. O tamanho total de todos os bancos de dados é de cerca de 500 GB (antes de serem compactados nos próprios arquivos de backup).
O plano de manutenção é executado uma vez por noite (por volta das 22h30) para fazer um backup completo em todos os bancos de dados do servidor. Nada mais fora do comum está sendo executado no servidor, nem nada mais está sendo executado naquele momento em particular. O plano de energia desligado do servidor está definido como "Balanceado" (e "Desligar o disco rígido após" está definido como 0 minutos, também conhecido como nunca desligá-lo).
O que aconteceu:
Durante o último ano, o tempo total de execução do trabalho do plano de manutenção demorou cerca de 15minutostotal para concluir. Desde a semana passada, disparou para cerca de 40 vezes mais tempo, cerca de 15horascompletar.
A única coisa que estou ciente de que mudou no mesmo dia em que o plano de manutenção ficou lento foi que as seguintes atualizações do Windows foram instaladas na máquina antes da execução do plano de manutenção:
Também temos outra instância do SQL Server provisionada de forma semelhante em outra VM que passou pelas mesmas atualizações do Windows e, posteriormente, teve backups mais lentos. Pensando que as atualizações do Windows eram a causa direta, nós as revertemos completamente e o plano de manutenção de backups ainda funciona extremamente lento. Estranhamente, a restauração dos backups de um determinado banco de dados acontece muito rapidamente e usa quase 1 GB/s de E/S total nos NVMes.
Coisas que tentei:
Ao usar sp_whoisactive do Adam Mechanic, identifiquei que os tipos de última espera dos processos de backup são sempre indicativos de um problema de desempenho do disco. Sempre vejo BACKUPBUFFERe BACKUPIOespero tipos, além de ASYNC_IO_COMPLETION:
Ao observar o Monitor de recursos no próprio servidor, durante os backups, a seção E/S de disco mostra que a E/S total utilizada é de apenas cerca de 14 MB/s (o máximo que já vi desde que esse problema ocorreu é 30 MB/seg):
Depois de tropeçar neste útilArtigo de Brent Ozar sobre como usar o DiskSpd, tentei executá-lo sozinho com parâmetros semelhantes (reduzindo apenas o número de threads para 8, pois tenho 8 processadores virtuais no servidor e definindo as gravações para 50%). Este é o comando exato diskspd.exe -b2M -d60 -o32 -h -L -t8 -W -w50 "C:\Users\...\Desktop\Microsoft DiskSpd\Test\LargeFile.txt". Usei um arquivo de texto gerado manualmente com pouco menos de 1 GB. Acredito que a E/S medida parece boa, mas as latências do disco estavam mostrando alguns números ridículos:
Os resultados do DiskSpd parecem literalmente inacreditáveis. Após ler mais, me deparei com uma consulta de Paul Randall que retorna métricas de latência de disco por banco de dados. Estes foram os resultados:
A pior latência de gravação foi de 63 milissegundos e a pior latência de leitura foi de 6 milissegundos, então isso parece ser uma grande variação do DiskSpd e não parece terrível o suficiente para ser a causa raiz do meu problema. Verificando ainda mais as coisas, executei alguns contadores PerfMon no próprio servidor, poreste artigo da Microsoft, e estes foram os resultados:
Nada de extraordinário aqui, o valor máximo de todos os contadores que medi foi 0,007 (que acredito ser milissegundos?). Por fim, pedi à minha equipe de infraestrutura para verificar as métricas de latência de disco que o VMWare estava registrando durante o trabalho de backup e estes foram os resultados:
Parece que, na pior das hipóteses, houve um pico de latência de cerca de 200 milissegundos por volta da meia-noite, e a E/S mais alta foi de 600 KB/s (o que eu realmente não entendo, já que o Monitor de Recursos está mostrando que os backups estão pelo menos usando cerca de 14 MB/s de E/S).
Outras coisas que tentei:
Acabei de tentar restaurar um dos bancos de dados maiores (tem cerca de 250 GB) e demorou apenas cerca de 8 minutos para restaurar. Então tentei executá DBCC CHECKDB-lo e demorou um total de 16 minutos para ser executado (não tenho certeza se isso é normal), mas o Resource Monitor mostrou problemas de E/S semelhantes (o máximo de E/S que ele já utilizou foi de 100 MB/s), com nada mais em execução:
Aqui estão os resultados de sp_whoisactive quando executei pela primeira vez DBCC CHECKDBe depois de 5% concluído, observe que o tempo restante estimado aumentou cerca de 5 minutos, mesmo depois de já ter 5% concluído.
Começar:
5% Concluído:
Suponho que isso seja normal, sendo apenas uma estimativa, e 16 minutos não parece tão ruim para um banco de dados de 250 GB (embora não tenha certeza se isso é normal), mas novamente a E/S estava apenas no máximo em cerca de 10% dos recursos da unidade, sem mais nada em execução no servidor ou na instância SQL.
Estes são os resultadosde DBCC CHECKDB, nenhum erro relatado.
Também tenho enfrentado problemas estranhos de lentidão com o SHRINKcomando. Acabei de tentar SHRINKo banco de dados que tinha 5% de espaço para liberar (cerca de 14 GB). Demorou apenas cerca de 1 minuto para completar 90% do SHRINK:
Cerca de 5 minutos depois, ele ainda está preso na mesma porcentagem concluída, e meus backups do log de transações (que geralmente terminam em 1 a 2 segundos) estão em disputa há cerca de 30 segundos:
15 minutos depois e SHRINKtudo termina, enquanto os backups do log de transações ainda estão em contenção por cerca de 6 minutos e apenas 50% concluídos. Eu acredito que eles terminaram imediatamente logo depois disso, desde o SHRINKfinal. O tempo todo o Monitor de Recursos mostrou que a E/S ainda estava sugando:
Então recebi um erro com o SHRINKcomando quando terminou:
Tentei SHRINKnovamente e resultou exatamente no mesmo resultado acima.
Então tentei criar scripts manuais de um backup T-SQL para um arquivo na unidade P: e isso ficou lento, assim como a tarefa de backup do plano de manutenção:
Acabei cancelando depois de cerca de 3 minutos e ele imediatamente foi revertido.
Resumo:
Coincidentemente, o trabalho do plano de manutenção de backups ficou cerca de 40x mais lento (de 15 minutos a 15 horas) todas as noites, logo após a instalação das atualizações do Windows. Reverter as atualizações do Windows não resolveu o problema. SQL Server Wait Types, Resource Monitor e Microsoft DiskSpd indicam um problema de disco (E/S em particular), mas todas as outras medidas da consulta de Paul Randall, PerfMon e VMWare Logs não relatam nenhum problema com os discos. A restauração dos backups de um banco de dados específico é rápida e usa quase 1 GB/s de E/S completa. Estou coçando a cabeça...
Responder1
Neste caso, realmente tivemos um problema de disco e não foi um problema interno do SQL Server, para esta VM específica. Na verdade, acabou sendo um caso de bug que encontramos na Veeam e no VMWare.
Para resumir minha compreensão do que aconteceu, aparentemente nossos backups da Veeam não foram reconhecidos como concluídos pela VMWare. Então, todos os dias, quando chegava a hora de fazer backup do servidor, a VMWare instruía a Veeam a fazer novamente o backup no dia anterior, o que se transformou nesse problema crescente e cumulativo ao longo de duas semanas. (Tenho certeza de que destruí essa explicação, mas isso é praticamente tudo o que sei.)
A Veeam/VMWare teve que excluir cada arquivo de snapshot, cujo arquivo de cada dia era maior que o anterior, então o suporte de nível 3 demorou cerca de 26 horas para ser concluído. Depois disso, a VM voltou a funcionar bem. Aparentemente, este não é um problema incomum de acordo com o suporte técnico.
Desculpe, este foi um problema muito específico e provavelmente não ajudará muitos outros por aí, mas espero que ajude.