Monitorando a Memória do SQL Server


Hoje recebi um comentário sobre a utilização de memória no SQL Server e comecei a pensar um pouco mais sobre o assunto. Será que esse é um assunto interessante?

Comentei com um amigo sobre o assunto e ele (que não trabalha em TI) disse que vale a pena. Minha motivação do blog será mostrar um pouco mais sobre os mecanismos de gerenciamento de memória usados no SQL Server.

Para começar a série de artigos, compartilho a imagem de uma utilização crescente de memória e que resultou numa degradação horrível de performance.

 

image

O primeiro conceito importante é falar sobre memória física ou memória RAM. Como vocês sabem, o Windows utiliza memória virtual, que permite colocar as informações em RAM e movê-las para o Paging File quando necessário. Entretanto, no mundo SQL Server estamos interessados somente na memória RAM porque a paginação em disco é muito lenta (não faz sentido fazer cache de dados usando o Page File).

Na linguagem técnica, dizemos que working set (veja o artigo Memory- Working Set) corresponde a quantidade de memória RAM usada por um processo.

Atenção: Apesar do Windows mostrar o working set dos processos, essa medida não contabiliza a memória alocada via AWE. Em outras palavras, evite utilizar o Task Manager para monitorar a quantidade de memória utilizada. Verifique os contadores do Performance Monitor chamados “Total Server Memory”.

No próximo artigo, pretendo introduzir o conceito de Buffer Pool – principal componente que interage com o Sistema Operacional.

Comments (19)

  1. DBA SQL disse:

    Poderias citar também sobre ring buffer, e alternativas para encontar contenção de memória no SQL Server?

    Abraço

  2. Alexandre disse:

    Olá Fabrício tudo bem ?

    Você comentou sobre a não utilizar o task manager para verificar a memória devido  AWE não ser mostrado.

    E em um servidor 64 bits posso garantir a utilização de memória pelo task manager ou ainda assim é mais garantido analisar pelos contadores.

    Abs

    Alexandre

  3. Anotado! Vou comentar sobre o ring buffer.

    Aproveito para adiantar que a sys.dm_os_ring_buffer não é documentada e pode sofrer alterações após mudança de versão ou service packs. Essa DMV é uma forma interessante de acompanhar os últimos eventos que ocorreram nos Memory Clerks, Memory Broker e Buffer Pool. Vale a pena comentarmos sim..

    Obrigado pela dica

    Fabricio

  4. Oi Alexandre!

    Usar ou não usar o Task Manager para monitorar a memória? (ótima pergunta!)

    A resposta é NÃO.

    Já vi muitos servidores de 64-bits que o Task Manager reportam que o SQL está usando apenas 300MB.

    O motivo é que o SQL Server ainda pode utilizar alocação de memória via AWE na plataforma 64-bits. Nesse caso, o Task Manager reportaria uma quantidade incorreta de memória. O ideal é sempre utilizar o Performance Monitor – Memory Manager:Total memory.

    Abraços

    Fabricio

  5. Leivio disse:

    Fabricio,

    Mas no caso o SQL Server poderia utilizaria AWE se o processo SQL Server estiver sobre WOW. Não é isso?

  6. Olá Leivio,

    Correto, o mecanismo AWE pode ser usado debaixo do WOW.  Mais do que isso: AWE pode ser usado no próprio modo nativo 64-bits (fora do WOW). Como o toda a memória AWE não contabiliza do ponto de vista do Task manager, então não é recomendado usa-lo para acompanhar o consumo de memória do servidor SQL.

    Abraços

    Fabricio

  7. Leivio disse:

    Fabricio,

    Só para esclarecer o que seria AWE em 64 bits;

    O AWE em modo nativo 64-bits(fora do WOW) não seria o privilegio do "lock pages in memory" para a conta do serviço MSSQLSERVER X64? Ou seja, teriamos uma modificação na alocaçãolocalização das paginas utilizadas pelo processo "working set e PFN database" com isso o SO ira ignorar essas "paginas" no processo de paginação e teriamos o acesso mais rapido as paginas ja que o mapeamento da paginas virtuais seria direto com as paginas fisicas.

  8. Oi Leivio!

    Exatamente!!! Perfeito!!! Ao habilitar o Lock Pages in Memory, o SQL Server passa a usar AWE para evitar a paginação em disco. Isso afeta diretamente na alocaçãolocalização das páginas e consequentemente no working set do processo. A estrutura de diretorio de PFN se altera principalmente ao usar Large Pages. Em ambos os casos, os PTE passam a usar referência a memória física e não podem ser paginados. Aproveitei para escrever um pouco mais no post:

    Lock Page: blogs.msdn.com/…/lock-pages-in-memory.aspx

    Obrigado pelos comentários. Fabricio

  9. DBA SQL disse:

    Senhores,

    Até onde sei em WOW o SQL Server 32 bits não ultrapassa a barreira dos 4 GB. Mesmo com AWE. Então, não vejo muita razão em usar AWE em Win 64 + SQL 32

    Abraço

  10. Olá!

    SQL Server 32-bits rodando em um Windows 64-bits (x64) é possível usando o WOW mode. Nesse caso é possível usar toda a memória RAM através do AWE limitado a 64GB. Segue uma referência:

    blogs.msdn.com/…/575152.aspx

    Por outro lado, não encontrei nada que falasse sobre o AWE não poder ser usado com WOW. Se tiver mais informações, coloque seu comentário e farei uma pesquisa mais cuidadosa. Obrigado pelo comentário!

    Abraços, Fabricio

  11. DBA SQL disse:

    Acredito que me enganei no link:

    msdn.microsoft.com/…/aa384209%28v=VS.85%29.aspx

    Abraço

  12. Agora está tudo claro: a memória não pode ser alocada através do AWE na plataforma Itanium (64-bits) quando rodando em WOW mode. Isso ocorre porque as páginas de memória são de 8kb, enquanto que no 32-bits, são 4kb.

    Esse é uma grande explicação porque a Microsoft nunca permitiu misturar SQL 32 e 64 bits dentro do Itanium. Felizmente, a plataforma x64 permite usar AWE sempre. 🙂

    Excelente link e comentário! 🙂

  13. DBA SQL disse:

    Foi isso. É que me enganei , não li corretamente que era apenas Itanium. Mas valeu. Fico no aguardo do RING BUFFER.

    Abraço

  14. Estou procurando algum bom exemplo para falar sobre RING BUFFER. Alguma sugestão? Algo parecido seria falar com XEvents, que tem muitas aplicações práticas.

  15. Olá Frabricio,

    Antes de tudo gostaria de parabeniza-lo pelo excelente conteúdo em seu blog. Ando acompanhando os posts e estão me servindo de muito aprendizado.

    Fiquei com uma dúvida, você deixou claro que o Working Set do processo não contabiliza a memória alocada pelo mecanismo AW e por isso é indicado monitorar a memória do SQL Server utilizando o contador Total Server Memory. Mas olhando em outras fontes li que o contador Total Server Memory contabiliza apenas a memória utilizada pelo Buffer Pool e alocações de memória como MPA,Linked Servers, etc não são contablizadas. Por isso dizem que o contador SQL Server Private Bytes mostra uma quantidade superior ao Total Server Memory.

    Apenas li essas informações mas nao testei ainda, gostaria de saber se é isso mesmo, e qual definitivamente é o contador que irá mostrar a memória como um todo alocada pelo SQL Server.

    Muito obrigado!

  16. Oi Felipe!

    Você tem total razão. Total Server Memory não contabiliza a memória alocada através dos mecanismos tradicionais do Windows. Isso significa que são os providers OLEDB (Linked Server) e Extended Procedures ficam de fora da conta. MPA é contabilizado sim! Então, a conclusão é:

    – Se o servidor NÃO usa AWE, então Private Bytes é superior a Total Server Memory SEMPRE.

    – Se o servidor utiliza AWE, então Private Bytes contém somente a memória alocada pelo Windows (dificilmente ultrapassa 2GB) e pode contabilizar no Working Set do processo. O restante de memória será alocado pelo AWE e não é visível como Private Bytes e/ou Working Set.

    Em geral, o Buffer Pool é responsável por mais de 90% da memória alocada no servidor.. por isso, costumo olhar apenas o contador Total Server Memory e não considero o Private Bytes. Mas seu comentário foi muito pertinente, não deixe de enviar outros.

    Obrigado, Fabricio

  17. Fabricio,

    Muito obrigado, agora sim deu uma clareada, nos conceitos.

    Por favor, queria pedir que continue com a serie sobre gerenciamento de memória do Windows e SQL Server, é um assunto bem complexo e pouco explorado, acredito que muitos profissionais nao entendem perfeitamente esse conceito.

    Obrigado!

  18. Oi Felipe!

    Acabei de postar um artigo sobre o DBCC MEMORYSTATUS, que revela muita coisa sobre a visão do SQL em relação ao Sistema Operacional. Espero que goste.

    blogs.msdn.com/…/dbcc_5F00_memorystatus_5F00_parte_5F00_1.aspx

    Abraços, Fabricio

  19. Respondendo a pergunta do DBA SQL sobre Ring Buffers: ainda não consegui pensar em um caso prático e útil sobre o Ring Buffers, mas estou pensando aqui em um exemplo que possamos todos usar no dia a dia. Se houver interesse, posso colocar algo teórico – apenas para ilustrar seu funcionamento.

    Abraços, Fabricio

Skip to main content