Tabela com índice clusterizado consumindo memória máxima causando alertas PLE baixos

Em primeiro lugar, o SQL Server in picture é SQL Server 2008 R2 RTMque não é suportado pela Microsoft de forma alguma, aplique o SQL Server 2008 R2 SP3 para pelo menos obter suporte estendido

Eu analisei e descobri que um dos DB A do usuário estava usando 8 GB de RAM

Eu não acho que haja nenhum problema por causa disso, isso é totalmente normal olhando para isso você não pode tirar nenhuma conclusão.

A havia uma tabela com um índice clusterizado consumindo em média 6-6,5 GB de memória

Eu acho que esta tabela é a maior tabela de banco de dados que está consumindo a maior parte do buffer pool. Não posso dizer antecipadamente por que a tabela está mantendo tanta memória novamente you should first apply SP3 to rule out any possibility of leaks and then move with troubleshooting.

Para um dos meus servidores com a configuração abaixo, estou continuamente recebendo alertas de baixo PLE, que varia entre 20-400 e uma média de 90-100 em um dia:

Para um sistema com 12 G RAM, o PLE deve estar em torno de 900. Mas como você diz que está sempre em torno de 300-400, o que me faz pensar que o SQL Server tem que trabalhar duro e mover páginas frequentemente da memória para o disco devido à pressão da memória. Para confirmar a pressão de memória, você precisa confiar em outros contadores, não apenas no PLE, você pode abrir o perfmon e adicionar os seguintes contadores. Seria melhor criar um conjunto de coletores de dados para monitorar contadores de desempenho

1. SQLServer:memory Manager--Target Server Memory: Esta é a quantidade de memória que o SQL Server está tentando adquirir.

2. SQLServer:memory Manager--Total Server memory Esta é a memória atual que o SQL Server adquiriu.

   (Idealmente, o valor alvo deve ser menor ou igual ao Total)

3. Leituras de página/s – Número de leituras de páginas físicas do banco de dados que são emitidas por segundo. Essa estatística exibe o número total de leituras de páginas físicas em todos os bancos de dados. Como a E/S física é cara, você pode minimizar o custo usando um cache de dados maior, índices inteligentes e consultas mais eficientes ou alterando o design do banco de dados

4. Páginas Livres – Número total de páginas em todas as listas livres (as listas livres rastreiam todas as páginas no buffer pool que não estão atualmente alocadas para uma página de dados e, portanto, estão disponíveis para uso imediatamente). Sem dúvida esse valor deve ser alto

5. Page Life Expectancy – Número de segundos que uma página permanecerá no buffer pool sem referências> se você tiver o sistema NUMA, analise o PLE para cada nó, conforme mencionado neste artigo

6. Free List Stalls/sec – Número de solicitações por segundo que tiveram que esperar por uma página livre. Idealmente, as paradas devem ser tão zero ou mínimas quanto possível

7. SQLServer:Gerenciador de Memória--Concessões de Memória Pendentes: Se você vir concessões de memória pendentes no pool de buffers, seu servidor está enfrentando problemas de memória do SQL Server e aumentar a memória seria uma boa ideia. Para concessões de memória, leia este artigo : Se você vir um valor diferente de zero de concessão de memória pendente com PLE baixo e paradas de lista livre alta, você definitivamente tem uma pressão de memória e deve considerar o fornecimento de mais RAM.

Observação: o valor dos contadores acima deve ser coletado por pelo menos 3 a 4 horas e quando a carga no sistema for relativamente alta . Se possível, antes de coletar os valores, limpe o cache do buffer, embora eu não esteja dizendo para você fazer isso, sei que para o sistema Prod não seria possível. Se você produzisse os valores eu poderia analisar para você.

Não tenho ideia sobre a carga de trabalho do sistema e apenas lhe dou a opção de como proceder. Você também deve analisar consultas caras. Procure um que envolva junções e classificações de hash.

Uma consulta incorreta com muitas junções e varreduras em uma tabela grande tem toda a capacidade de trazer contadores como PLE, concessões de memória, paradas de lista gratuita para valor que fazem você acreditar na pressão de memória, sim, mas a causa é uma consulta ruim e índices ausentes e junção incorreta. Você também deve considerar isso.

Editar:

Da saída do coletor de dados de desempenho conforme solicitado para os contadores acima

Abaixo estão alguns pontos notáveis

1. A memória do servidor de destino e a memória total do servidor permaneceram constantes there value were equaldurante todo o processo de coleta de dados (das 10h da manhã às 15h da tarde). o que aponta para o fato de que o mecanismo de banco de dados do SQL Server estava satisfeito com seu requisito de memória atual

2. As concessões de memória pendentes eram always zero.

3. Free List stalls/Sec era always zero. O que significa que nenhum pedido teve que esperar por páginas gratuitas

4. Houve uma queda considerável no PLE das 14h15 às 15h18 e ao mesmo tempo as páginas lidas/seg estavam muito altas o que me faz acreditar que alguma consulta/processo começou depois das 14h00 o que exigia muitas páginas na memória e portanto, causando turbulência de atividade no buffer pool.

Você precisa descobrir qual consulta está sendo executada depois das 14h e ainda estava em execução, então isso me faz pensar que há process.job começando às 14h. Este processo/trabalho/consulta está tentando ler muitas páginas, o que está fazendo com que o lazywriter libere muitas páginas porque a consulta está solicitando espaço para essas páginas

Pode ser que essa consulta esteja faltando índice, pode ser que esteja criando um plano ruim devido a estatísticas distorcidas, pode ser que a consulta precise ser escrita para obter/ler apenas um subconjunto de dados, não dados inteiros.

5. seria incorreto dizer que é uma pressão de memória, é apenas um processo específico que está criando um cenário que parece ser uma pressão de memória.