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.

 

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

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

• Anonymous
  June 24, 2010
  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

• Anonymous
  June 25, 2010
  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

• Anonymous
  June 25, 2010
  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

• Anonymous
  June 28, 2010
  Fabricio, Mas no caso o SQL Server poderia utilizaria AWE se o processo SQL Server estiver sobre WOW. Não é isso?

• Anonymous
  June 28, 2010
  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

• Anonymous
  June 30, 2010
  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.

• Anonymous
  July 01, 2010
  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

• Anonymous
  July 05, 2010
  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

• Anonymous
  July 05, 2010
  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

• Anonymous
  July 06, 2010
  Acredito que me enganei no link: msdn.microsoft.com/.../aa384209%28v=VS.85%29.aspx Abraço

• Anonymous
  July 06, 2010
  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! :)

• Anonymous
  July 06, 2010
  Foi isso. É que me enganei , não li corretamente que era apenas Itanium. Mas valeu. Fico no aguardo do RING BUFFER. Abraço

• Anonymous
  July 12, 2010
  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.

• Anonymous
  July 19, 2010
  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!

• Anonymous
  July 19, 2010
  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

• Anonymous
  July 19, 2010
  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!

• Anonymous
  July 25, 2010
  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

• Anonymous
  August 11, 2010
  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