Por que a memoria utilizada pelo SQL Server não diminui? Por que a memoria do SQL Server só diminui quando eu reinicio o servidor?

Uma das perguntas mais comuns do forum da microsoft na sessão de SQL Server, é o por que que o SQL Server não liberar memoria, portanto, pretendo com este posts, reduzir o numero de perguntas no forum, de forma que todas as duvidas sejam sanadas sem essa necessidade da pergunta.

Alguns conceitos e explicação….

Memorias

O SQL Server, assim como a maioria dos SGBD´s trabalham com um conceito de memoria primaria e secundaria, a memoria primaria é aquela de maior velocidade de acesso porem volatil, ou seja, ha perda de dados com a falta de energia, ja a memoria secundaria, é aquela mais lenta, porem não volatil, ou seja, caso ocorra uma queda de energia não havera perda de dados.
Como exemplo para as memorias temos para a memoria principal, componentes de um servidor como Memoria RAM ou Memoria CACHE, ja para a memoria secundaria, o HD (Disco fisico) em si.

As alterações

O SQL Server trabalha com o conceito de pagina de dados, que por padrão possui um tamanho fixo de 8KB, portanto, quando voce realizar uma opção, seja ela DML ou DDL, voce esta realizando alterações em paginas, seja uma mudança em uma pagina ja existente, a adição de uma nova pagina, ou a deleção de uma pagina atravez de um shrink por exemplo.
Não é possivel realizar nenhuma alteração em pagina de dados da memoria secundaria, portanto, é necessario que o DbEngine busque esta pagina em disco, realize a alteração, e devolva para disco.

Claro que isso não é feito, pois iria gerar um gargalo enorme de disco, portanto até o momento, temos 2 opções:
– Após alteração, gravar pagina no disco
Vantagem: Garante persistencia dos dados
Desvantagem: Muito lento, gargalo de disco
– Após a alteração, manter a pagina em memoria e dar transação como finalizada
Vantagem: Rapido
Desvantagem: Em caso de perda de energia, ou desligamento inesperado do servidor, havera perda dessa alteração

Então, o que foi pensado para se solucionar este caso, trabalhar com um arquivo de log.
Então, como funcionaria, ao invez de ao final de uma transação gravar a pagina em disco, seria melhor mante-la na memoria, mas como é necessario garantir

sua alteração, todas as ações tomadas seriam gravadas em log, que por ser sequencial, é muito mais rapido.

Para maiores explicações sobre Recovery models e arquivos de log, visite este artigo.
Para maiores explicações sobre o Processo de CheckPoint, visite este artigo.

Tendo isso em mente, podemos pensar o seguinte….

Uma vez que todas as transações estão registradas em log, podemos manter a pagina em memoria, mas…e a memoria?
Sim, a memoria ira crescer conforme sua utilização, isso é fato, caso o SO ou o DbEngine não precise de mais memoria, o proprio DbEngine não ira retirar paginas sem motivo da mamoria, portanto, ao se iniciar um servidor que possua uma instancia SQL Server, ele ocupara inicialmente de memoria, o minimo de sua instancia adicionado com mais um pouco para alguns processos de backupgroud.
Com o passar do tempo, essa memoria só tende a crescer e ocupar toda a memoria disponivel fisicamente, contanto que esse limite não ultrapasse o limite definido na instancia SQL Server.

Caso o servidor seja exclusivo para banco de dados, o proprio DbEngine sera responsavel pelo input e output de paginas de memoria, o que ira gerar um processo denominado de processo de swap, o algoritmo utilizar é o LRU, este algoritmo possui a seguinte ideia:
– Cada vez que eu altero algo em uma pagina, eu marco 1 no bit de alteração
– Cada vez que eu acesso uma pagina, sem alteração (Um select por exemplo), eu marco 1 no bit de leitura
E caso o SQL Server precise uma pagina em memoria foi solicitada mas ainda esta em disco, ele ira tentar retirar da memoria as paginas seguinte esta linha de raciocinio:
00 -> Primeiro
01 -> Segundo
10 -> Terceiro
11 -> Quarto

Claro que existem outros valores envolvidos, principalmente bits, mas creio que a ideia esteja clara…..

Alem das paginas em memoria, para ganho de performance, o SQL Server mantem em memoria planos de execução, ou seja, caso voce execute a mesma query repetitivamente, não sera necessario uma nova analise para a melhor arvore de execução, pois teoricamente isso ja esta feito, o que é ótimo para ambientes OLTP.

Porem, caso seu servidor não seja dedicado para banco de dados, como faremos? Vamos supor que neste mesmo servidor temos alem do SQL Server, um SharePoint instalado, vamos supor tambem que nosso servidor possua 32Gb de memoria.

Podemos sim limitar a memoria de ambas aplicações para 15Gb cada uma, mais 2Gb para o sistema operacional, porem, e se o SQL Server esta precisando de muita memoria e possui seu limite em 16Gb, no mesmo instante o SharePoint nem esta ativo? Teriamos uma possivel lentidão sem necessidade para os usuarios/aplicação do SQL Server, alem de 15Gb parados no servidor. O mesmo caso pode ocorrer caso o SharePoint necessite de memoria o SQL Server esteja tranquilo….

Não recomendo nesta colocar as 2 aplicações para utilizar 30Gb de memoria, mas ao menos uns 23~25Gb de memoria, é uma boa saida.

Porem, como ficaria o controle de concorrencia e swap em memoria pela parte do SQL? Bom, vamos supor a segunda situação acima, aonde o SQL Server esteja bem tranquilo e o SharePoint no Gargalo, precisando de mais memoria, neste caso, um processo em backgroud do SQL Server denominado Lazzy Writer, é executado a cada segundo, ele é responsavel por verificar se o SO precisa de mais memoria, no então, o SharePoint solicita mais memoria ao SO, o Lazzy Writer do SQL Server verifica que o SO precisa de mais memoria, portanto, utilizando o algoritmo descrito previamente, começara a liberar paginas da memoria.

Lembrando que ele não removera da memoria paginas que estão sendo utilizadas, pois isso poderia causar inconsistencia e um grande problema para o SQL

Server, portanto, ele libera o quanto ele conseguir até “desafogar” o SO.

Anúncios
Post a comment or leave a trackback: Trackback URL.

Comentários

  • Clayton Santos  On 04/04/2012 at 01:57

    Olá Fabrizzio, como vai?
    Parabéns pela iniciativa de explicação sobre memória do MS SQL Server.
    Um forte abraço

  • Angelo Máximo Moreira Silva  On 13/06/2012 at 21:15

    Fabrizzio, ótima explicação sobre o consumo de memória do SQL Server, já que trata-se de um assunto mais que corriqueiro na vida dos DBA’s.
    Parabéns.

Trackbacks

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: