Estamos lutando contra um problema de pressão estranha de memória em um servidor específico há alguns meses. Veja como foi o último incidente no SentryOne:
Memoria do sistema
Memória do SQL Server
Configuração de memória:
- Memória Total do Servidor - 96 GB
- Memória máxima do servidor - 84 GB
A razão pela qual isso parece estranho é que, se fosse pressão de memória externa, eu esperaria que a categoria Outros na Memória do Sistema crescesse durante isso, o que não acontece.
Parte do que vemos acontecer nesse período é que as consultas acabam gerando planos ruins e acabam causando problemas de desempenho para o aplicativo. Historicamente, executar o DBCC FreeProcCache durante essa situação aliviou a pressão, mas ainda não sabemos a causa. Eu acho que os planos de um plano ruim são um sintoma e não uma causa desse problema, mas posso estar errado.
Coisas que fizemos para tentar resolver esse problema:
- Removida uma junção no que pensávamos ser o sp problemático
- Registros duplicados excluídos no banco de dados
- Maior memória no servidor (acho que adicionamos 16-32 gb)
- Bloqueio de páginas na memória ativado
Estou completamente perdido quanto ao que olhar a seguir. Nosso arquiteto acha que talvez precisemos mexer em algumas configurações de VM com memória, mas ainda não chegamos lá.
O que posso ver para corrigir esse problema estranho de pressão de memória?
Há um artigo legal de Jonathan Kehayias no SQL SQLSkills.com com o título Wow… Uma calculadora online para configurar incorretamente a memória do SQL Server! .
Em seu artigo Jonathan escreve:
Se você continuar com este exemplo, talvez seja melhor configurar seu SQL Server para ser executado com uma configuração max_memory de 81 GB.
Você pode criar um Excel para produzir um pequeno gráfico agradável das configurações de memória máxima do SQL Server com as seguintes fórmulas e dados.
A planilha do Excel começa com uma coluna HW Memory (A2):
A fórmula do Excel para a segunda coluna SO reservado (B2) é:
A coluna da Memória SQL (C2) é então:
Isso produz o seguinte gráfico:
Solução possível
Como você pode ver, se você tiver 96 GB de RAM, deverá reservar 15 GB para o sistema operacional e definir a memória SQL (max_memory) para 81 GB.
Jonathan continua explicando em seu artigo que ...
Seu sistema operacional pode não ter memória suficiente e está retirando a memória do sistema operacional SQL Server.
Reduzir ligeiramente a configuração max_memory para 81 GB permitirá que o sistema operacional e outros componentes do SQL Server que não são executados dentro da configuração max_memory tenham RAM suficiente.
Sua milhagem pode variar
Posso estar errado sobre isso, mas vamos tentar de qualquer maneira. Talvez seja útil para você.
Na captura de tela que você fornece, parece que os tanques de expectativa de vida da página, quando você começa a ter problemas de desempenho. Presumo que você veja consultas com longas esperas PAGEIOLATCH_* nesse momento? Você também menciona que planos ruins estão sendo gerados e executar DBCC FREEPROCCACHE corrige o problema temporariamente. Para mim, soa como sintomas típicos de sniffing de parâmetros. Eu acho que você tem uma consulta, que, quando compilada com parâmetros errados, faz a varredura da tabela em vez de buscar, usa o índice errado ou a forma do plano de consulta muda. Geralmente, essa é uma consulta que requer muita E/S e satura o subsistema de armazenamento, de modo que até mesmo consultas simples que fazem pesquisas de chave ficam lentas. Eu tentaria identificar qual é e confirmar removendo apenas o plano dele, quando tiver problemas de desempenho.
Para identificar a consulta problemática, eu apenas observaria aquelas que têm longas esperas PAGEIOLATCH e comparar seus planos de execução quando forem rápidos.
Para corrigi-lo, você precisa ser criativo. Você pode tentar adicionar dicas de consulta, forçar a recompilação em cada execução, usar guias de plano. Também tive sucesso ao alterar índices nas tabelas subjacentes e reescrever consultas problemáticas. É difícil aconselhar algo específico, pois seu problema agora é identificar o que está causando seus problemas.
Eu vi problemas semelhantes ao executar trabalhos noturnos do SSIS, às vezes o uso da memória do SSIS é invisível para o Sentryone por qualquer motivo. Eu daria uma olhada no calendário de eventos no S1 para ver quais trabalhos estão sendo executados no momento.