Buffer Pool, Maximum e Minimum Server Memory no SQL Server

Os principais objetivos de um banco de dados é o armazenamento e a recuperação da informação, por decorrência disso o consumo de I/O é sempre considerável. Toda vez que um dado é solicitado o SQL Server precisa buscar essa informação e retornar para o aplicativo/usuário. Como já sabemos, o SQL Server assim como todos os SGBDs utilizam cache, esse cache também chamado de Buffer Pool é utilizado para reduzir o processamento de I/O causado pelas operações de manipulação de dados (DML). Como assim? Bom, simplificando o cache é uma área reservada na memória RAM para o SQL Server e seu tamanho é parametrizado de acordo com os parâmetros de Maximum Server Memory e Minimum Server Memory onde o mecanismo do SQL Server aloca páginas de dados lidas do disco em memória, assim quando o dado é solicitado o SQL Server retorna a página em memória, não sendo necessário o custo de processamento para ir buscar essa informação em disco.

Uma página de dados permanece em cache até que o gerenciador do Buffer precise de espaço para ler novas páginas, assim com base em alguns fatores, algumas páginas são descartadas da memória dando lugar a essas novas páginas. Existem outras ocasiões onde uma página pode ser descartada da memória, como por exemplo, quando é realizado um CHECKPOINT, assim as páginas “sujas” que são os dados que sofreram alteração são persistidas em disco. O comando DBCC DROPCLEANBUFFERS limpa o cache, assim matando tudo que está em memória.

Maximum e Minimum Server Memory

O Buffer Pool (cache) é único por instância, ou seja, uma vez configurado todos os banco de dados compartilharam da mesma área em memória para alocar suas páginas de dados. Os parâmetros Maximum e Minimum Server Memory localizados nas configurações do servidor (Propriedades -> Memória) e/ou através do comando sp_configure limita o Buffer Pool com quantidade máxima e minima. Por padrão o SQL Server deixa como minimo 0 e máximo 2147483647 (MB).

Quando iniciado o SQL Server não aloca imediatamente a quantidade minima configurada, ou seja, se você configurar 2GB de minimo, ao iniciar o serviço não subirá com essa quantidade de memória alocada. Então pra que server o minimo? Vamos lá, o gerenciador de Buffer do SQL Server vai alocando memória conforme a carga e processamento dos clients, e quando a configuração minima for atingida o SQL Server garante que mesmo se acontecer uma pressão do Sistema Operacional não irá liberar memória inferior ao configurado no minimo.

Já o Maximum Server Memory limita o gerenciador de Buffer a não alocar mais memória que o configurado. Conforme dito anteriormente, o gerenciador de Buffer vai alocando memória conforme a carga e processamento no SQL Server. Caso o limite máximo já foi atingido e o gerenciador necessita de espaço em memória para alocar mais páginas é o momento onde algumas páginas são retiradas da memória para a entrada de novas páginas.

Qual a configuração ideal para o Maximum e Minimum Server Memory?

Como tudo no mundo de banco de dados, a resposta é DEPENDE. Depende de quantas instâncias existem no servidor, depende de quantos aplicativos compartilham a mesma memória, depende da carga de processamento sobre o banco de dados, depende da quantidade de memória física disponível, enfim, existem vários outros fatores que contribuem para a análise e parametrização disso.

Mas para não deixar muito em aberto está questão, vou falar como costumo configura-las. Inicialmente quando instalo uma nova instância, costumo configurar um valor padrão e monitorar durante um período, com isso é possível analisar se a configuração está adequada para o ambiente. Esse monitoramento se abrange em identificar se o SO está tentando “roubar” memória do SQL Server, se o máximo já foi atingido e com qual frequência isso aconteceu, entre outras.

Por exemplo, uma máquina com 16GB de RAM, como boas práticas , configuro o minimo com 2GB e o máximo com 12GB, deixando assim um espaço razoável para o sistema operacional trabalhar. Lembrando, isso depende de vários fatores já citados, não podemos levar isso como regra para todos os ambientes.

Monitorando quanto de memória cada Banco de Dados está consumindo

A query a seguir retorna a quantidade de páginas e em MB que cada banco de dados está consumindo do Buffer Pool.

WITH Consumo_Pool_Buffer

AS

(

                SELECT

                                Database_id,

                                BuffersPorPagina = COUNT_BIG(*)

                FROM sys.dm_os_buffer_descriptors

                GROUP BY database_id

)

SELECT

                Database_id as DatabaseID,

                CASE Database_id WHEN 32767

                                THEN ‘Recurso interno do SQL SERVER’

                                ELSE DB_NAME(Database_id) END AS DatabaseName,

                BuffersPorPagina,

                (CONVERT(NUMERIC(10,2),BuffersPorPagina*8)/1024) AS BuffersPorMB

FROM Consumo_Pool_Buffer

ORDER BY BuffersPorPagina DESC, BuffersPorMB DESC

GO

Monitorando quanto de memória cada objeto está consumindo

A query a seguir retorna os objetos do banco que estão com páginas alocadas no buffer e quanto isso está consumindo de memória. Essa análise é bem interessante, pois conseguimos chegar ao nível de descobrir o quanto uma determinada tabela está consumindo de memória do espaço alocado para o banco de dados proprietário.

SELECT

                DB_NAME(db_id()) DatabaseName,

                Result.ObjectName,

                COUNT(*) AS cached_pages_count,

                index_id

 FROM sys.dm_os_buffer_descriptors A

INNER JOIN

(              SELECT

                                OBJECT_NAME(object_id) as ObjectName,

                                A.allocation_unit_id,

                                type_desc,

                                index_id,

                                rows

                FROM sys.allocation_units A, sys.partitions B

                WHERE A.container_id = B.hobt_id

                AND (A.type = 1 or A.type = 3)

                UNION ALL

                SELECT

                                OBJECT_NAME(object_id) as ObjectName,

                                allocation_unit_id,

                                type_desc,

                                index_id,

                                rows

                FROM sys.allocation_units AS au

                                INNER JOIN sys.partitions AS p

                                                ON au.container_id = p.partition_id

                                                AND au.type = 2

                                                ) as Result

On A.allocation_unit_id = Result.allocation_unit_id

WHERE database_id = db_id()

GROUP BY

                Result.ObjectName,

                index_id

ORDER BY cached_pages_count DESC

GO

Configurar o limite máximo e minimo do Buffer Pool é interessante, pois evitamos que o sistema operacional “roube” memória do SQL Server e também que o SQL Server utilize toda a memória do servidor, causando gargalo no próprio sistema operacional e em outros aplicativos instalados no servidor. Porem, essa configuração é considera avançada e antes de utiliza-la é aconselhável realizar toda uma análise de ambiente.

Espero que artigo tenha agregado conhecimento e auxiliando na configuração de memória do seu ambiente.

No próximo artigo vamos mais a fundo no conceito de Buffer Pool, analisando com mais detalhes como o SQL Server controla a memória e também a diferença no gerenciamento de memória do SQL Server 2008R2 para o SQL Server 2012.

Grande Abraço  e até o próximo artigo.

Comente sobre isso: