Configurando o Min e Max Server Memory – Parte 1

  • Article

Configurando o Min e Max Server Memory – Parte 1

Hoje o acesso as páginas de dados em disco estão na casa dos milissegundos, enquanto o acesso em memória está na casa dos nanossegundos. Partindo desse pressuposto, podemos dizer que o acesso a páginas em memória é mais bem mais rápido do que o acesso a disco, o que não é nenhum mistério para nós. O SQL Server também sabe disso e tentará o máximo possível efetuar cache e colocar suas páginas em memória. O BufferPool normalmente é o componente do SQL Server que mais utiliza memória, pois nele ficam armazenadas as páginas de dados.

Quando efetuamos uma Query(consulta) o Storage Engine utiliza o Access Methods para retornar as páginas de dados necessárias para atender essa requisição,essas páginas podem estar no cache de dados, ou seja, no BufferPool, caso as páginas da tabela ou índice da operação (leitura/alteração) não estiverem no BufferPool, o SQL Server acessará o disco (Storage) e irá trazer essas páginas para o BufferPool.

O BufferPool é formado por buffers de 8KB para armazenar páginas de dados e ao contrário do que muitos pensam NÃO é lugar se armazena o Procedure Cache.
Conforme os usuários acessam os dados em suas tabelas e índices, as páginas de dados vão sendo armazenadas no BufferPool e com isso o consumo de memória aumenta , até chegar bem próximo do limite físico da máquina . Cada página no SQL Server tem o tamanho de 8KB, se você acessar 1.000.000 de páginas (diferentes), você terá algo em torno de 7812 MB de cache de dados, sem um limitador, essas páginas consumirão memória do cache até que ocorra uma pressão interna ou externa. Tal limitador é chamado de Max Server Memory. Além do Max Server Memory, que limita o máximo de memória RAM que poderá ser consumida pelo BufferPool, temos o  Min Server Memory" , que limita o mínimo de memória, cujo qual irei explicar a seguir.

Min Server Memory e Max Server Memory.

Em uma instância SQL Server podemos configurar por meio do sp_configure o Min e Max Server Memory. Quando os configuramos estamos atribuindo o “mínimo” e o "máximo" de memória que umainstância SQL Server poderá utilizar, porém existem algumas diferenças entre versões do SQL Server que irei explicar com mais detalhes a diante.

É comum configurarem o "Max Server Memory" próximo do total de memória física do servidor, deixando um pouco de espaço para que o sistema operacional opere de forma satisfatória, sem que ocorra pressão de memória externa por parte do OS. Para atribuir o "Min Server Memory" e o "Max Server Memory", devemos levar em conta as seguintes considerações de acordo com as seguintes versões do SQL Server.

SQL Server 2008 R2 - Max Server Memory.

Das versões anteriores até a versão SQL Server 2008 R2, quando configuramos o "Max Server Memory" estamos atribuindo o total de memória que o SQL Server utilizará como cache de dados, ou seja, estamos atribuindo o total de memória que apenas o BufferPool utiliza e também memória que ele fornece para outras alocações (até 8KB) que são para outro propósito ou para componentes na Engine que não são "Database Pages" . Essas páginas são chamadas de Stolen Pages, que traduzindo ao pé da letra são páginas roubadas , exatamente porque foram páginas roubadas do BufferPool para outras "funcionalidades" no SQL Server. Esse valor pode ser visualizado na stolen_pages_kb da DMV sys.dm_os_memory_clerks e esse consumo faz parte do Max Server Memory. Porém há uma outra série de alocações de memória, além das descritas acima que não faz parte da memória que foi atribuída no Max Server Memory e essas alocações são as seguintes:

  • Alocações Multi-Page, que são alocações que utilizam mais que 8K. Essas duas alocações podem ser visualizadas na coluna multi_pages_kb da DMV sys.dm_os_memory_clerks.
  • Alocações de CLR, que inclui as Heaps.
  • Alocações feitas para threads dentro do processo do SQL Server.
  • Alocações de Extended Store Procedures, objetos criados usando OLE Automation procedures(sp_OA calls) e Alocações de Linked Server.

SQL Server 2012 - Max Server Memory.

No SQL Server 2012 o "Max Server Memory" não limita somente o total de memória que o BufferPool utiliza, as alocações de memória mudaram, este funcionamento tomaria uma outra série de posts para explicar, mas basicamente o BufferPool é apenas um consumidor de memória como todos os outros “Memory Clerks”. Desde do SQL Server 2012 todas essas alocações podem ser visualizadas através da coluna pages_kb da sys.dm_os_memory_clerks, que inclui as alocações de “Stolen pages” de “Multipage” das versões anteriores.

Hoje o “Max Server Memory” limita "quase" toda a memória do SQL Server e as exceções são as seguintes:

  • Alocações feitas para threads dentro do processo do SQL Server.
  • Alocações de Extended Stored Procedures, objetos criados usando OLE Automation procedures(sp_OA calls) e Alocações de Linked Server.

Cada Thread tem 2MB de Stack Size, para calcular o máximo de Worker Threads que uma instância SQL Server poderá utilizar há a seguinte formula:

Maximum worker threads = (512+ (NumerodeProcessadoresdaMaquina - 4) * 16)

SQL Server 2014 – Max Server Memory

Os valores referentes a utilização de memória SQL Server 2012 também são válidos para o SQL Server 2014. Quando utilizamos a feature In-Memory OLTP adicionando uma tabela em memória “Memory Optimized Tables” . Observe como ficaria uma divisão de memória na Imagem 1 do diagrama abaixo.
 
 

Conforme adicionamos outras tabelas em memória, essas tabelas vão ocupando espaço, porém não devemos esquecer que mais tabelas virão do disco para memória. Por isso, quando se for criar uma tabela In-Memory não se pode esquecer de provisionar seu tamanho inicial, dimensionando também seu crescimento para não causar um cenário como a imagem 2, na qual se possui algumas tabelas In-Memory que ocupam um grande espaço, porque nestes casos as tabelas ficam inteiras na memória e pode faltar espaço para outras páginas que virão do disco.

Veja um exemplo de como configurar “Max Server Memory” de uma instância com 40GB via linha de comando abaixo:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'max server memory', 40960;

GO

RECONFIGURE;

GO

Para maiores informações de como configurar o Max Server Memory acesse o link:

https://msdn.microsoft.com/en-us/library/ms178067.aspx

No próximo post continuarei com Min Server Memory e alguns exemplos de como isso acontece em algumas instâncias.

Configurando o Min e Max Server Memory – Parte 2

Alberto Lima