Esta é uma instância CU2 do SQL Server 2016 com 15 GB configurados como uso máximo de RAM. O MDOP está em 4. Os bancos de dados nesta instância são consultados esporadicamente pelos usuários e esta é uma réplica secundária somente leitura.
O problema: semanalmente todas as consultas entram no estado RESOURCE_SEMAPHORE devido ao esgotamento da memória, mesmo quando nenhuma outra consulta está sendo executada simultaneamente neste servidor. Após a reinicialização da instância, as consultas voltam a funcionar. Pelas imagens abaixo, você notará que falta memória máxima do espaço de trabalho e essas consultas são MUITO ruins (custos de consulta astronômicos).
Antes da reinicialização: apenas 1,8 GB de memória máxima do espaço de trabalho disponível e TODAS as consultas incorretas entrando no estado RESOURCE_SEMAPHORE.
Após a reinicialização da instância: bastante memória do espaço de trabalho (~ 11 GB) e nenhum RESOURCE_SEMAPHORE mais, mesmo para consultas terríveis.
Como você pode ver, é uma consulta repulsiva que pede cerca de 2 GB de RAM. Nesta imagem, o SSMS mostra que a memória necessária foi realmente concedida – porque a instância foi reiniciada. Antes da reinicialização, a concessão de memória permanece NULL e a consulta permanece no estado de espera RESOURCE_SEMAPHORE.
Agora, o que gostaríamos de saber é: por que a memória máxima do espaço de trabalho diminui com o tempo e não é liberada quando não há consultas em execução?
Responder1
Parece que de um total de memória alocada de 15.728.640.000 bytes, a memória roubada do servidor ocupa 12.828.864.000 bytes, aparentemente não deixando o suficiente para operações normais.
A documentação diz em SQL Server, objeto gerenciador de memória:
Memória de servidor roubada (KB)
Especifica a quantidade de memória que o servidor está usando para outras finalidades além das páginas do banco de dados.
Umresponderdiz mais:
Memória roubada descreve buffers que estão em uso para classificação ou para operações de hash (consulta de memória do espaço de trabalho) ou para aqueles buffers que estão sendo usados como armazenamento de memória genérico para alocações para armazenar estruturas de dados internas, como bloqueios, contexto de transação e informações de conexão . O processo lazywriter não tem permissão para liberar buffers roubados do pool de buffers.
A consulta inútil aparentemente fez uma enorme operação de classificação e o SQL Server não libera a memória alocada. A memória é retirada do Buffer Pool e não pode ser usada para mais nada.
Você não tem muitas opções:
- Melhore essa consulta para que ela não classifique grandes quantidades de dados
- Adicione mais memória ao SQL Server
- Reinicie o SQL Server periodicamente ou especialmente após executar esta consulta.