Meu servidor está executando o SQL Server 2016. O ambiente é de carga de trabalho bastante alta com muitas transações de gravação e leitura dos dados ao longo do dia. Eu tenho um palpite de que o servidor não está provisionado com memória suficiente e gostaria de investigar e ver se é assim. Quais são as melhores maneiras de determinar se e quanta contenção está sendo gerada pela quantidade de memória disponível no servidor?
Dei uma olhada no DMV sys.dm_os_wait_stats e, quando classificado por wait_tasks_count desc, os dois principais tipos de espera são "MEMORY_ALLOCATION_EXT" e "RESERVED_MEMORY_ALLOCATION_EXT" por uma ordem de magnitude maior do que qualquer outra contagem de tarefas do tipo de espera. Existem outros lugares que eu possa verificar também quanto à pressão ou contenção da memória?
Edit: O tamanho total de todos os bancos de dados neste servidor é de 3 TB, o banco de dados principal com a maioria das transações é de 2 TB e a quantidade total de RAM no servidor é de 32 GB .
Edit 2: Aqui estão os resultados do contador Lazy writes / second perfmon ao longo de um dia:
Aqui estão vários contadores de perfmon que você pode usar como iniciante.
Memory Grants Pending - Este é um contador que informa se há alguma consulta aguardando uma concessão de memória (alocação de memória). Na verdade, deve ser 0. Se for consistentemente mais do que isso, você tem um problema.
Expectativa de vida da página - Esta é a quantidade estimada de tempo em segundos que uma página permanece na memória. Quanto maior, melhor, mas existe uma fórmula para calcular qual deve ser o valor mínimo para o seu seridor. Costumava ser no mínimo 300 segundos, mas esse é um cálculo antigo e hoje deve ser de 100 segundos por GB. Recebi isso de Richard Douglas durante uma sessão de sábado de SQL, então créditos a ele. Ele trabalha para o Sentinela. Menos que esse valor indica que há muito pouca memória atribuída. Use também este contador em conjunto com as páginas de Checkpoint/s. Esteja ciente de que cada nó NUMA tem seu próprio valor PLE (se você tiver vários nós NUMA em seu SQL Server). O SQL Server começa a dividir recursos em nós NUMA (soft) acima de 8 núcleos atribuídos.
Gravações lentas/s - O processo de gravação lenta limpa as páginas antigas do cache quando o SQL Server sofre pressão de memória. Constantemente acima de um valor de 20 é um problema (peguei isso de Richard Douglas também). No entanto, use-o em conjunto com a expectativa de vida da página. Se você vir um PLE alto junto com um pico nas gravações lentas/s, então algo está fazendo com que o SQL remova páginas de seu cache e insira novas páginas. Veja a captura de tela abaixo para ver um exemplo do meu laboratório doméstico.
Tenho certeza de que alguns especialistas aqui sabem um pouco mais sobre os funcionários de memória, ainda na minha lista para mergulhar mais fundo, então talvez alguém tenha alguma informação adicional para você (na qual também estou muito interessado :-) ).
Edit: você também pode usar sys.dm_os_performance_counters para obtê-los rapidamente com uma consulta, se desejar.
Editar 24 de junho de 2020:
@JD sobre seu comentário de 23 de junho; Também me aprofundei um pouco mais na pressão da memória, por causa dos comentários de @Dominique Boucher e deste artigo: https://www.brentozar.com/archive/2020/06/page-life-expectancy-doesnt-mean- jack-e-você-deveria-parar-de-olhar/. Eu estava rindo quando recebi isso na minha caixa de correio; talvez ele tenha visto este post. :-) O artigo está nos dizendo para parar de olhar para ele. Bem, embora Brent seja certamente muito mais experiente do que eu, acho que não posso concordar totalmente com sua declaração de nunca olhar para isso. Eu entendo seu ponto no contexto de seu sp_BlitzFirst, uma única consulta usando no máximo 25% do cache do buffer, é um indicador de atraso, etc. Se eu quiser determinar se um servidor tem pressão de memória ao longo do tempo, isso é o que eu usaria em combinação com as concessões de memória em espera. Além disso, as ferramentas de monitoramento da RedGate e da Quest ainda usam isso. Agora @Dominique Boucher diz para olhar para as esperas RESOURCE_SEMAPHORE, com as quais eu concordo, mas isso provavelmente estará de acordo com o número de concessões de memória pendentes (que você pode registrar facilmente com perfmon). Se você tem uma fila constante de concessões de memória (funciona com uma fila FIFO), então você tem pressão de memória.
Como referência, este é o Lazy Writer para um determinado sistema neste universo (32 GB de memória, 1 TB de banco de dados, embora o tipo de carga de trabalho também seja muito importante). Amarelo é pedidos em lote p/s, 10 indica 1000, então você pode ver que definitivamente não está ocioso.
aqui está também um instantâneo do meu laboratório doméstico sobre concessões de memória versus esperas de RESOUCE_SEMPAHORE (e também vejo que destaquei as gravações de tempdb, era disso que david estava falando, muito pouco mem, então derrame para tempdb):
Agora, olhando para seus contadores de desempenho, acho que você definitivamente pode ter um problema de memória. Quero dizer; algo está constantemente forçando o SQL Server a remover páginas do cache de buffer. Se isso fosse uma única vez, ok, mas parece que está constantemente ocupado fazendo isso... No entanto, eu gostaria de vê-los comparados ao PLE. Isso pode ser uma indicação clara de que há pressão na memória (com o tempo, ainda acho que sim). Em segundo lugar, você também quer ver as concessões de memória pendentes. Agora, eu não disse isso antes, mas olhando para trás eu acho que você quer olhar para as estatísticas de espera como Brent e Dominque estão dizendo. No entanto, isso é um pouco mais difícil ao longo do tempo. As estatísticas de espera são reunidas cumulativamente, então você precisa limpá-las primeiro (o que eu não prefiro) e depois ver se o RESOURCE_SEMAPHORE está somando.
Para limpar as estatísticas de espera: https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-sqlperf-transact-sql?view=sql-server-ver15
Você também pode usar sp_BlitzFirst para monitorá-lo, mas isso é apenas um instantâneo do momento em que você o está executando. sp_BlitzFirst (ou sp_Blitz eu não me lembro) tem uma opção para gravar isso periodicamente em uma tabela, então você pode ver isso também. Ou apenas consultar dm_os_wait_stats de alguma outra maneira também funciona. Em geral, minha preferência pessoal é coletar dados ao longo do tempo para analisar isso. Eu faço isso usando o Database Health Monitor de Steve Stedman: databasehealth.com. Captura de tela do meu laboratório doméstico:
Dessa forma, você pode monitorar as estatísticas de espera muito melhor, embora isso custe um pouco de recursos.
Se você tiver o SQL Server 2017 ou superior (ainda não temos isso em nosso env.), você também poderá usar o Query Store. A partir do SQL Server 2017 Query Store também registra estatísticas de espera (é uma opção configurável). Tenha cuidado, porém, eu li histórias de Query Store trazendo servidores muito ocupados (você pode monitorar isso com estatísticas de espera :-P ). Você deve sempre testar antes de implementar a funcionalidade no prod, é claro. Nós o usamos e funciona muito bem, mas temos 2016, então perdemos a opção de estatísticas de espera :-(.
A propósito; minha tática seria coletar informações (perfomon, esperar estatísticas se possível), se você acha que há pressão de memória, atualize a RAM (se for uma VM que seria bastante fácil), colete indicadores de desempenho posteriormente e verifique se eles melhoraram. Pouco acéfalo, mas o último é muitas vezes esquecido ou não é muito bem feito.