Configurando o Min e Max Server Memory – Parte 2
Configurando o Min e Max Server Memory – Parte 2
A configuração de "Min Server Memory" é usada para atribuir o mínimo de memória que você imagina que seja necessário para uma instância SQL Server. Ao contrário do que muita gente pensa, logo após a configuração do “Min Server Memory” no sp_configure o SQL Server não irá utilizar essa memória imediatamente. Isso ocorrerá conforme os usuários utilizando os bancos de dados da instância e lendo as páginas do disco, até que o consumo de memória atinja o valor do "Min Server Memory".
Após o consumo de memoria superar o “Min Server Memory” e se estabeleça entre Min e Max , quando ocorrer uma pressão de memória externa, o Memory Manager do Sistema Operacional irá solicitar ao SQL Server que reduza seu consumo de memória em uma situação de pressão de memória externa o SQL Server não reduzirá sua utilização abaixo do no estabelecido no "Min Server Memory" ou seja, não cederá esse espaço para o sistema operacional ou qualquer outro processo que esteja rodando no Windows.
Há alguma situação que SQL Server pode reduzir a utilização de memória RAM para abaixo do que o estabelecido em Min Server Memory?
Caso o sistema operacional precise que o SQL Server ceda mais memória além do mínimo e isso não ocorrer, o SQL Server poderá ter uma parte da memória alocada em PagingFile, que funciona como uma extensão da memória RAM, porém no disco. Essa extensão em disco que por sua vez, possui o acesso muito mais lento.
Nos contadores do SQL Server, mais especificamente no objeto "Memory Manager" do Performance Monitor, podemos visualizar esse consumo. Nele encontraremos os contadores:
- "Total Server Memory": demonstra o total que a instância utiliza como memória Committed pelo Memory Manager (representado na cor verde).
- "Target Server Memory": demonstra o total de memória que o SQL Server pretende atingir e normalmente ele está alinhado com o "Max Server Memory" que foi atribuido à instância
- "Stolen Server Memory" : é a quantidade de páginas "roubadas" do BufferPool.
Observe o seguinte raciocínio:
Imagine que você possui uma instância SQL Server 2012, rodando em Windows Server 2012, com 100 GB de Memória RAM e você teve que reiniciar a instância após efetuar atividade de manutenção à meia noite. Os bancos de dados dessa instância possuem uma grande atividade durante o horário regular do expediente de trabalho entre as 09:00 e 18:00 horas. Imagine também que você configurou o "Min Server Memory" da instância para 30GB e o "Max Server Memory" para 80GB, consequentimente deixando 20GB para outros componentes, threads stack e outros processos no sistema operacional.
Observe a imagem abaixo que ilustra o cenário descrito acima:
No gráfico acima, o “Total Server Memory” é o total de memória que o SQL está utilizando no momento. Observe que devido ao restart da instância que ocorreu a meia-noite, a utilização da memória começou em zero e foi aumentando gradualmente de acordo com atividades que foram sendo executadas nos bancos. Podemos observar também que a quantidade de memória sendo utilizada, atinge o estabelecido em “Min Server Memory” apenas quando o horário de expediente começa, por volta das 9:00. Nesse nosso ambiente hipotético, após o início dos trabalhos, a quantidade de páginas lidas aumentou e atingiu o “Target Server Memory” ao meio dia - vale relembrar que comumente encontramos o “Target Server Memory” com o valor igual ao de “Max Server Memory”.
Voltando a análise, imagine agora que um processo externo ao SQL começou a ser executado ao meio dia, este processo consome muita memória, o “Memory Manager” do Sistema operacional irá solicitar que o SQL reduza seu consumo para dar espaço para esse processo. Uma vez que essa ação seja tomada pelo SO, o SQL irá devolver memória até que o limite de “Min Server Memory” seja atingido. Quando isso ocorrer, você verá que o “Target Server Memory” será reduzido e terá um novo valor (no caso, 60GB). O SQL então fará um flush de suas páginas em memória e fará com que a memória utilizada por ele chegue ao valor de “Target Server Memory” – o que ocorre as 16:00.
Por fim, o processo externo que vinha consumindo muita memória, termina sua execução. Ao término, o SQL poderá utilizar novamente o espaço solicitado anteriormente pelo SO e preencher o cache de dados com outras páginas. Essa ação fará com que o “Target Server Memory” volte a crescer, até que atinja o valor de “Max Server Memory” novamente.
Como estimar a quantidade de memória física para uma instância SQL Server?
Essa é não uma pergunta simples de responder. Em um mundo perfeito, a quantidade ideal de memória para uma instância SQL Server seria fazer com que a instância tenha espaço suficiente para colocar em cache de dados todas as páginas so seu banco de dados. Desta forma a instância não precisará em momento algum trazer as páginas de dados do disco, pois as mesmas já estariam no BufferPool. No ambiente corporativo e maior parte dos casos, obviamente não temos memória suficiente para tal, porém quanto mais memória eu tiver para cache, melhor.
O contador do Perfmon "Page Life Expectancy" é um 'indicador para visualizarmos quando há uma pressão de memória. Esse contador mostra uma média de segundos em que suas páginas de dados permanecem em memória, ou seja, por quanto tempo essas páginas estão sendo reaproveitadas por outras consultas sem precisar ir ao sistema de discos. Há vários documentos que citam o número mágico de 300 segundos para esse contador. Na realidade não existe um número exato, o que eu não se pode esperar deste contador é que haja quedas muito bruscas. O fato deste tempo diminuir é um indício de que houve uma pressão interna ou externa de memória, fazendo com que novas páginas tenham que vir de disco e varrendo as antigas do BufferPool.
Quando habilito Lock Pages In-Memory (LPIM) o processo SQL Server pode ir para o PagingFile?
Quando é concedida de permissão de "Lock Pages In Memory" para conta de serviço, o SQL Server utilizará memória AWE (Address Windowing Extention) para alocação de páginas de até 8KB e neste caso a memória adquirida via AWE NÃO poderá ir para PagingFile pois não faz parte do WorkingSet do processo do SQL Server.
Para maiores informações sobre Lock Pages In-Memory em 64 bits, veja o link abaixo:
https://blogs.msdn.com/b/slavao/archive/2005/04/29/413425.aspx
Devo sempre atribuir o Min e Max Server Memory da minha instância?
A resposta é depende. Quando em um servidor a instância SQL Server não é compartilhada com mais nada, é muito mais difícil que os cenários de pressão de memória mencionados aqui ocorram. No entanto, em uma instância SQL Server em cluster, é bastante importante que sejam atribuídos o Max e principalmente o Min Server Memory. Suponha que voce possua dois servidores cada um com a instancia SQL Server, uma chamada de InstA e a outra de InstB e que possam fazer Failover entre eles. Em um dado momento occorre uma falha no Servidor da InstA e foi feito o Failvover para InstB. Após o failover, as duas instâncias terão que compartilhar a mesma memória. Caso o "Min Server Memory" tenha sido atribuido com um valor muito alto na InstB, poderá faltar uma memória mÍnima para que a InstA consiga ser operacional. (Ex: Uma maquina de 100GB de Memória RAM que possui uma instância com o "Min Server Memory" de 70GB e o "Max Server Memory" também com 70GB, o que chamamos de Memória Estática), a instância InstA terá apenas 30GB para dividir com as threads da outra instância e entre os processos do sistema operacional, porque a InstB já utilizou 70GB e sobrará pouca memória para a instância e o servidor operarem.
Espero essa série de atigos tenha auxiliado a um melhor entendimento do que levar ou não em consideração ao se configurar o Min e Max Server Memory de suas instâncias SQL Server.
Até a próxima pessoal.
Para quem não viu a primeira parte:
Configurando o Min e Max Server Memory – Parte 1
Alberto Lima
Comments
Anonymous
December 04, 2014
Alberto, Excelente post. Eu penso que entender como configurar adequadamente cada instância SQL em um servidor faz toda diferença para obter uma boa performance, mesmo que seu hardware seja antigo ou com recursos limitados. Gostei do "cenário" que você apresentou. Definir o que o SQL Server pode utilizar e como ele consome os seus recursos torna todo servidor mais estável, diminuindo a possibilidade de "surpresas" por uma manipulação de dados elevada.Anonymous
December 09, 2014
Muito bom esse post, me ajudou bastante a entender como funciona a distribuição da memoria para as instancias.Anonymous
December 09, 2014
Olá Durval e André, Que bom que gostaram do Post, espero ajude na tomada de decisão do ambiente de vocês, tentamos deixar claro os pontos principais, Porque é uma configuração que tem bastante efeito no ambiente e há alguns pontos que pode causa confusão. Muito Obrigado pelo Feedback! Um abraço, Alberto Lima - SQL Server PFEAnonymous
August 24, 2015
The comment has been removed