Perfmon: Monitorando o Buffer Manager

O gerenciador de memória de Buffer de dados, também conhecido por Data Cache ou Database Cache, fornece as melhores informações sobre a utilização de memória do banco de dados.

Inicialmente analisamos os contadores:

• Page life expectancy
• Lazy writes/sec
• Free list stalls/sec

Segue aqui alguns exemplos de gráfico do Page Life Expectancy (PLE). Particularmente, gosto de utilizar validar se o PLE está acima de 5000 ao invés de usar o tradicional valor limite de 300. Nos casos abaixo, todos os servidores apresentam carga ao longo do dia, embora apenas o terceiro esteja com aparente falta de memória.

Podemos complementar a análise olhando o contador Free List Stalls/sec, que deve ser sempre zero absoluto – parece que existe um novo Latch que deixa claro sua interferência, que se chama Lazy Writer Helper.

Em seguida, podemos olhar a distribuição de memória usando os contadores:

• Database pages
• Free pages
• Stolen pages
• Target pages
• Total pages

Os servidores apresentam: 1) distribuição constante e normal; 2) folga e sobra de memória; 3) alto consumo de memória não-buffer (stolen). Em nenhum dos gráficos temos uma situação que podemos chamar de crítica, que seria quando o Stolen Memory chega a 75% do total de memória (pressão interna) ou quando o Total Pages e Target Pages começam a diminuir ao longo do tempo (pressão externa).

Por fim, confirmamos que as variações do PLE são causados por Table/Index Scan no servidor usando os contadores Page Reads/sec e Readahead pages/sec. Adicionalmente, você pode acompanhar o contador Page lookups/sec para ter uma ideia da carga.

• Page lookups/sec
• Page reads/sec
• Readahead pages/sec

Seguem os gráficos correspondentes: notamos que a quantidade de Reads (aleatório + sequencial) é praticamente igual a Readahead (sequencial). Assim, o primeiro servidor apresenta picos de utilização, que causam grandes variações no PLE . No segundo e terceiro servidor, está ocorrendo uma operação de Scan realizando a leitura de 10-30 mil págnas por segundo (equivalente a 80-240MB/seg!!!).

Aqui conseguimos concluir:

• Servidor 1 possui memória suficiente para aguendar a carga. A variação do PLE ocorre por conta de picos de consumo (ver gráfico do readahead), mas não há indícios de falta de memória.
• Servidor 2 possui memória de sobra (ver o gráfico de Free Memory) e recebe uma carga bastante pesada (ver gráfico de readahead)
• Servidor 3 recebe uma carga semelhante ao Servidor 2 (ver gráfico de readahead), entretanto o PLE indica que o nível de memória está constantemente baixo. A distribuição de memória revela que grande parte está reservado como Stolen Memory (provavelmente são Memory Grants). A carga desse servidor é alta e falta memória.

Não há dúvida que o Performance Monitor é a melhor ferramenta para conduzir uma análise sobre a utilização de memória do SQL Server.

Comments

• Anonymous
  February 22, 2016
  The comment has been removed

• Anonymous
  February 24, 2016
  Muito útil. Com certeza usarei no meu dia-a-dia. Obrigado por compartilhar. Abrassss

• Anonymous
  February 24, 2016
  Obrigado @Reginaldo e @Rodrigo!

• Anonymous
  February 24, 2016
  Comentário sobre o desafio anterior: No último post, coloquei o servidor 1 - está ok! Servidor 2 é um servidor que está muito tranquilo. Servidor 3 está debaixo de carga. Esse sim está sofrendo!

• Anonymous
  March 01, 2016
  Olá Fabricio, Quero compartilhar um cenário do ambiente que trabalho, com base em sua análise acho que se aproxima muito do terceiro cenário que você compartilhou, mas tenho algumas questões para fazer Em meu ambiente o horário que o PLE esta com um número legal é no horário de backup Full, antes do backup full começar ele chega a 0 e sobe aproximadamente á 2300 e quando a rotina de backup vai acabando ele vai descendo. Durante o dia fica em uma média de 394,7 com alguns picos á zero. Já me adiantando, temos um número razoável de table scan em tabelas temporária em um processo conhecido, e alguns casos de index scan em pk em outras queries. Uma dúvida o PLE tem alguma relação com o backup full, que beneficia esse contador ? Se for do seu interesse posso compartilhar o meu gráfico.

• Anonymous
  March 01, 2016
  The comment has been removed

• Anonymous
  March 01, 2016
  Olá Marcelo! O ideal seria você compartilhar os gráficos. Mas adiantando: Buffer distribution vai ajudar a determinar qual o tamanho da memória usada, enquanto que o Readahead pode revelar a causa do problema. Backup ao mesmo tempo? Ele pode distorcer um pouco os números, porque o backup full aciona o checkpoint. Na forma como o SQL Server trabalha, o checkpoint e o lazywriter andam lado a lado e, por isso, as escritas de checkpoints podem ser consideradas lazy writes também. Antes de dizer se isso é um problema sério, dá uma olhada no Page Stalls per Second. Se esse contador for maior que zero, então há impacto no servidor. Obrigado pelos comentários!