Os principais objetivos de um banco de dados são o armazenamento e a recuperação da informação. Em 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, nos quais 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. Logo, 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 em que uma página pode ser descartada da memória, como quando é realizado um CHECKPOINT. Dessa forma, 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, 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 bancos 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, limitam o Buffer Pool com quantidade máxima e mínima. Por padrão, o SQL Server deixa como mínimo 0 e máximo, 2147483647 (MB).

Quando iniciado, o SQL Server não aloca imediatamente a quantidade mínima configurada, ou seja, se você configurar 2GB de mínimo, ao iniciar, o serviço não subirá com essa quantidade de memória alocada. Então para que server o mínimo? Vamos lá, o gerenciador de Buffer do SQL Server vai alocando memória conforme a carga e o processamento dos clients e, quando a configuração mínima 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 mínimo.

Já o Maximum Server Memory limita o gerenciador de Buffer a não alocar mais memória que o configurado. Como dito anteriormente, o gerenciador de Buffer vai alocando memória conforme a carga e processamento no SQL Server. Caso o limite máximo já tenha sido atingido e o gerenciador necessitar de espaço em memória para alocar mais páginas, é o momento em que 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 essa questão, vou falar como costumo configurá-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 identifica 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 coisas.

Por exemplo, em uma máquina com 16GB de RAM, como boas práticas, configuro o mínimo 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 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 mínimo 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. Porém, essa configuração é considerada avançada e, antes de utilizá-la, é aconselhável realizar toda uma análise de ambiente.

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

No próximo, 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.