Como encontro a causa raiz da falha de pressão de memória em um servidor SQL 2008?

Question

Um dos servidores que tenho monitorado de desempenho começou a lançar os seguintes avisos do Resource-Exhaustion-Detector:

Windows successfully diagnosed a low virtual memory condition. The following programs consumed the most virtual memory: sqlservr.exe (1560) consumed 14960812032 bytes, ReportingServicesService.exe (1936) consumed 506359808 bytes, and w3wp.exe (7376) consumed 273764352 bytes.

SystemCommitLimit 38068215808 SystemCommitCharge 37800669184 ProcessCommitCharge 16727490560 PagedPoolUsage 359088128 PhysicalMemorySize 17098584064 PhysicalMemoryUsage 16881131520 NonPagedPoolUsage 221425664 Processes 48

Este servidor é o Windows Server 2008, executando o MSSQL 2008 R2, tem 16 GB de RAM e 24 processadores. Ele executa o SQL e um serviço da Web que acessa o SQL para dados.

Os números que incluí na cotação são da seção de detalhes do visualizador de eventos. Eu não consegui identificar uma causa raiz. Eu já sei que o SQL precisa de muita memória para funcionar, e ele estava usando muita memória na época, mas eu também tinha o limite definido para 14000MB.

O SQL começou a receber o erro de falta de memória além dos avisos de esgotamento de recursos e detectores.

Qual seria a melhor abordagem para encontrar a causa raiz disso? Eu não vi nada que pareça fora do comum nos logs. Depois de algumas horas desse erro repetidas vezes, a memória finalmente acabou e os serviços começaram a falhar até que o serviço tivesse que ser reiniciado.

O SQL não é inteligente o suficiente para abandonar parte de sua memória quando há pressão? O arquivo de paginação (memória virtual) tinha 20 GB e o SQL usava apenas 16 GB de memória física. O que estava preenchendo o resto da memória virtual? O SQL estava realmente usando todo esse arquivo de página?

Devo procurar um vazamento de memória? Crescimento do arquivo de log?
O .mdf mais usado no servidor cresce cerca de 100mb todos os dias. O arquivo de log cresceu 3gb de cada vez, assim como 40gb.

Normalmente, quando há pressão de memória, nunca chegamos ao ponto em que o servidor simplesmente falha. Normalmente, ele fica dolorosamente lento até que a pressão é eliminada.

Existe uma maneira de efetivamente impedir que esse problema ocorra?

    

Answer 1

Para diagnosticar isso corretamente, precisamos de mais informações.

O SQL Server é como qualquer outro processo do Windows; Seu espaço de endereçamento virtual pode ser muito maior que a RAM física. Ele pode até ser maior que os arquivos de paginação de RAM +, se qualquer parte dele usar arquivos mapeados na memória.

O parâmetro de ajuste no SQL Server é uma maneira de dizer que nunca use mais que 'x' MB. Você tem que olhar para a carga máxima de confirmação de todos os outros serviços na caixa, subtrair isso da sua figura RAM física e, em seguida, dar o restante para o SQL Server. Tanto quanto eu saiba, o limite de memória só se aplica ao RDBMS, não ao zoológico de serviços de servidor SQL relacionados. Eu posso estar errado aqui.

Então, precisaríamos de mais números para os processos restantes. Por exemplo, você tem um processo de trabalho do IIS consumindo 273 MB; existe apenas um processo de trabalho? Você tem antivírus ou software de backup instalado?

Você pode usar o WSRM para analisar o que está acontecendo e, em seguida, considerar a aplicação de limites de memória. Alternativamente, e seria minha recomendação, instale mais RAM.

Para obter uma visão gráfica de onde sua memória está indo, dê uma olhada no utilitário RAMMap da Microsoft SysInternals.

    

Answer 2

Is there a way to effectively stop this issue from occurring?

A resposta simplista seria sugerir que você compre mais memória. Isso pode não resolver o seu problema, mas provavelmente não daria certo.

O SQL Server gosta de memória. O SQL Server gosta de armazenar em cache o seu banco de dados, ou pedaços de seus bancos de dados, na memória, para que eles sejam acessados mais rapidamente. Se você quiser ver o que está em sua memória agora, você pode obter essa informação fora do DMV: link . Um de meus colegas de trabalho recebeu uma recomendação do fornecedor de que o tamanho do banco de dados para o banco de dados de seu produto nunca excedia o tamanho da memória do servidor. Isso é impraticável para a maioria das pessoas, mas se você estiver tentando fornecer um banco de dados de 10 TB altamente consultado com 16 GB de RAM, isso pode ser um problema.

Tente executar o sp_blitz no seu servidor - é um procedimento armazenado que verifica se há problemas no seu servidor. link

Tente também o perfmon: link

Isso deve ajudá-lo a rastrear a causa.

    

Answer 3

Você pode precisar aumentar o tamanho do seu arquivo de paginação para poder lidar com picos intermitentes no tamanho da confirmação de memória. Temos esse problema com frequência no Azure compute, em que o arquivo de paginação é definido como WAY muito baixo por padrão para aplicativos com uso intensivo de memória.

Você pode ler mais aqui: link

Isso não resolverá o problema se sua instância SQL precisar de muito mais memória do que você, mas pode ajudar a superar melhor os picos temporários.