Buffer Pool, Maximum e Minimum Server Memory no SQL Server

Buffer Pool, Maximum e Minimum Server Memory no SQL Server

13:00 12 October in Gestão de Infraestrutura, Implantação de Soluções
0 Comments

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.

1 WITH Consumo_Pool_Buffer
2
3 AS
4
5 (
6
7                 SELECT
8
9                                 Database_id,
10
11                                 BuffersPorPagina = COUNT_BIG(*)
12
13                 FROM sys.dm_os_buffer_descriptors
14
15                 GROUP BY database_id
16
17 )
18
19 SELECT
20
21                 Database_id as DatabaseID,
22
23                 CASE Database_id WHEN 32767
24
25                                 THEN 'Recurso interno do SQL SERVER'
26
27                                 ELSE DB_NAME(Database_id) END AS DatabaseName,
28
29                 BuffersPorPagina,
30
31                 (CONVERT(NUMERIC(10,2),BuffersPorPagina*8)/1024) AS BuffersPorMB
32
33 FROM Consumo_Pool_Buffer
34
35 ORDER BY BuffersPorPagina DESC, BuffersPorMB DESC
36
37 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.

1 SELECT
2
3                 DB_NAME(db_id()) DatabaseName,
4
5                 Result.ObjectName,
6
7                 COUNT(*) AS cached_pages_count,
8
9                 index_id
10
11  FROM sys.dm_os_buffer_descriptors A
12
13 INNER JOIN
14
15 (              SELECT
16
17                                 OBJECT_NAME(object_id) as ObjectName,
18
19                                 A.allocation_unit_id,
20
21                                 type_desc,
22
23                                 index_id,
24
25                                 rows
26
27                 FROM sys.allocation_units A, sys.partitions B
28
29                 WHERE A.container_id = B.hobt_id
30
31                 AND (A.type = 1 or A.type = 3)
32
33                 UNION ALL
34
35                 SELECT
36
37                                 OBJECT_NAME(object_id) as ObjectName,
38
39                                 allocation_unit_id,
40
41                                 type_desc,
42
43                                 index_id,
44
45                                 rows
46
47                 FROM sys.allocation_units AS au
48
49                                 INNER JOIN sys.partitions AS p
50
51                                                 ON au.container_id = p.partition_id
52
53                                                 AND au.type = 2
54
55                                                 ) as Result
56
57 On A.allocation_unit_id = Result.allocation_unit_id
58
59 WHERE database_id = db_id()
60
61 GROUP BY
62
63                 Result.ObjectName,
64
65                 index_id
66
67 ORDER BY cached_pages_count DESC
68
69 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 2008 R2 para o SQL Server 2012.

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

 

**Este texto é uma produção independente e, portanto, de inteira responsabilidade do autor, não refletindo a opinião da Infobase.

Luiz Henrique Garetti Rosário

luizh.rosario@gmail.com

Graduado em Ciência da Computação pela Universidade Paulista e Pós-graduando em Business Intelligence pelo instituto Brasileiro de Tecnologia Avançada (IBTA). Especialista SQL Server atuando como DBA/Consultor em projetos de diversos portes, focando em arquiteturas de Alta Disponibilidade utilizando ambientes em Failover Cluster, Database Mirror, AlwaysOn e Administração de Banco de Dados em geral. Contribui para a comunidade com artigos técnicos publicados em grandes eventos na área de Banco de Dados com foco em Alta Disponibilidade e distribuição geográfica dos dados.